Replies

Linda,

It shouldn’t have anything to do with the wizard. I first thought, using a macro and not an event procedure for the BeforeUpdate event could be the culprit (I never use macros for events). Now I just tested such a macro and it works. It doesn’t let me save the record even if the table field is not required and the NotInList procedure is not bothering. So it must be something else. Are you sure you can save records with that field empty after editing? Existing records with SpecID empty will remain so if there is no change in the record, because neither form’s BeforeUpdate nor ctlSpecID’s NotInList fires then.

Maybe you can post your macro commands here in some form, although an event procedure would be much better.

BTW, LimitToList=True itself does allow an empty field in A97 and A2K. But you should set it anyway, otherwise the users could enter initials not contained in your table.

Charlotte,

I’m experiencing exactly the same. Used before only the Find tab I really can go nuts with A2K help. I tried the answer wizard also, it seems to work better, but one has to type more and figure out questions for 2/more items. And I don’t like the VBE/UI splitting either, it’s not entirely logical. When I search some VBA thing, I’ll expect to find the items from the Access catalogue also (DoCmd etc). Now I just search sometimes when I forget about the splitting and search and search… In a moment of mental derangement caused by a fruitless search with “subform referencing” I even thought to decompile the whole bunch and try to recompile with the new help compiler. I don’t know of course if this can be made, but I’m dreaming of those (newer?) help files from other software having more search options.

And I didn’t say anything about the tasty mix of english and german code in the samples… (I had to download the whole original solutions+orders package, the german solutions.mdb is completely unusable. It doesn’t even open due to localized modules incl. table, field etc. names in the code but not localized database object names such as table, field etc. Which means, the thing was not even once clicked upon to test it after localization!)

I have read somewhere the question, if someone in Redmond is reading this…

But let’s think positive 🙂 I’m reading this forum since couple of weeks now, and it’s just great ! This is the highest level forum I found until now, one can really learn a lot. Thanks!! Some day I even may be grateful to Compuserve for having closed the german forums.

Kevin,

Another idea:
Check those fields in the form’s BeforeUpdate event proc. If they are empty, set Cancel to false. This prevents the record to be saved with empty fields (and left), no matter what the user is trying to do.

If IsNull(Me![First Name]) Then
   MsgBox "You have deleted the first name during this session. The record cannot be saved without this.", _
   vbOKOnly, "Empty Field Notification"
   Cancel=True
End If

I almost always do so. I have very few mandatory fields in the tables. I tried once to trap the error in the form’s error proc, but the original engine error message kept popping around, next to my own error. But I admit it’s some time since and I may have made something wrong.

The incomplete records, if it’s not very much, can be filtered with Filter by Form and then completed or deleted. Or you can delete them with an SQL delete statement in the database window.

Charlotte,

This sounds logical, and I used the command as a quick-and-dirty method to print various simple continuos forms. The point was, Access kept printing the same record in the main form with the same record set in the subform, so that was indeed not very obvious :-). The main form is opened per VB with WhereCondition set, so it (apparently?) has one record.

However, I’ll keep to that command button printing the data per VB 🙂

Sorry for answering so late – I had problems with my left hand sinew (don’t know if it’s the right translation).

Doris,

if you want to set the OrderBy property, then you have to set OrderByOn to True, so the OrderBy gets activated.

However, if the natural sort order at opening (laufende Nr. ;-))doesn’t change, you could put an ORDER BY clause in the RecordSource query and leave the Load proc. I just tried this out – I have a subform with ascending order in the query, I sorted it per menu command descending, closed the whole thing, reopened – and I have on the screen the original ascending order now.

Jason,

your question about forms & printing reminds me of something similar.
I have a form with course data. It has a subform with the students and exam notes. We wanted to have a list printed, so I said, just print out the form. Well, the form got printed – but the printing wouldn’t stop. The whole form including subform was printed again and again – I don’t know how many times, I just pulled out the paper tray and switched off the printer at some time. This I could reproduce on another PC with another printer, so it must be Access.

If you are experiencing something similar, I’ll have to take some time to experiment a bit. I cannot remember using the Access print command for a form before.

O yes, this happened in Access 95 – it seems, the bug, if it is one, has a looong life

Larry,

You can add the new course name in the combo’s NotInList event procedure. When Limit to List property is set to Yes, it fires each time the user enters a new item. Basically, you then open a recordset based on the CourseName table and add a new record with the new name.

Setting the Limit to List property to No would not help you, because the new name would only appear in the combo. There would be no new record in the CourseName table automatically added. Anyway, you could not set the property to No because of the ID. I suppose there is a relation between CourseName ID and ClassName ID.

For Access 95/97 there is a KB article:
INF: Use NotInList Event to Add a Record to Combo Box (7.0/97)
Article ID: Q161007

There must be something similar for Access 2000, or you can take the same code.

And maybe you’ll consider to provide some way to update/add the course names through a simple endless form – there are always typing errors.

Slav,

I suppose, it’s these 3 lines of your code:

DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
Reports![invoice]![invoice].Visible = True
DoCmd.printout acPages, 1, 1, acLow, 1, True

You make the control visible only _after_ showing the report on screen in preview mode. At this time the control is still invisible. When printing (=printout command), the report is opened again and the control set visible by Access. At least I imagine things so…

You should set the Visible property in the report’s Open event – sort of other way around. You should have a control in your form you can reference in the report’s event procedure to know if it is an invoice or an order. Then you could write in the Report_Open procedure:

If Forms![FOrderInformation]![ctlInvoiceOrOrder]= "Invoice" Then
Me![invoice].Visible=True
Me![order].Visible=False
Else
Me![invoice].Visible=False
Me![order].Visible=True
End If

This way, you don’t have to worry about preview or print, the report should show the right fields.
Hope this helps 🙂

Judy,

First, put your function in the OnCurrent event of the subform. It will fire each time the recordset changes, no matter if new record, deleting record or moving in the main form. There is no need for code in the main form.

Then, here is some code again. There is an If in case there are no recs in the subform and there is a gimmick to show the right number if you’re in a new rec on the subform.

Dim rst As DAO.Recordset
Dim lngCount As Long
Dim fAtNew As Boolean

‘Just in case there are no recs in subform
If Not IsNull(Me.RecordSource) Then
Set rst = Me.RecordsetClone

‘ Check to see if you’re on the new record or not.
fAtNew = Me.NewRecord

With rst
If Not .EOF And Not .BOF Then
.MoveLast
lngCount = .RecordCount
.MoveFirst
End If
End With
rst.Close

‘If you’re on a new rec, you should count it also,
‘the recordset doesn’t contain it yet
[txtRecCount] = CStr([CurrentRecord]) & ” of ” _
& lngCount + IIf(fAtNew, 1, 0)
Else
[txtRecCount]=”0 of 0″
End If

Hope this helps 🙂

Oh, I forgot about the error message. To make sure the recordset has some records, you always should write:

If Not rst.EOF And Not rst.BOF Then
…
End If

I guess, the error came because the recordset was on BOF.

Your assumtion is indeed bad, Access always stores the complete path – otherwise cannot find its tables. The solution is to write a function which checks the links, asks the user for the new table location if the link is broken, then refreshes the link (=updates the path).
You can call this function in the AutoExec macro with the ExecuteCode command (or something like this, I have german version), so the links will be checked each time you start the application.

If you have a Getz/Litwin/etc book around (Access Dev handbook), you can find the code there. Or maybe someone else around here has it, mine is full of german comments.

If you have the usual back-end/front-end construction with data on server and front-end on each workstation, you should create temp tables always local, in the front-end application, so each user has his/hers own temp data.

That’s because you never reset the Visible property for the case neither of the conditions is true. Put a line before the IF-Statement:

with me
!Text36.Visible = false ‘!!!!!!!!!!
If !powerday > Forms!mask1!Text25 * 1000 Then
!Text36.Visible = True
!Text36 = “more than ” & Forms!mask1!Text25 & “MW”
Else
If !powerday < Forms!mask1!Text23 * 1000 Then
…
end with
Cheers
Emilia