Which event to use?

[Sess]

I am using Access2003. I want to make a little pop up message to inform the end user if the current data is missing an element they should be updating. So I want to place this

If Nz(Me.BoMDate, " ") = " " Then
MsgBox "This item needs a Bill of Material", vbOKOnyl, "Company Name LTD"
End If

so that when the form opens it pops up if the field is null, they can click on OK and update the field - or not - so anyhow where can I put it so it tests the data. I tried OnLoad but it seems to be ahead of seeing the form. I tried OnCurrent but the message pops up 4 times.
It is a Single Form and so there can be more than one record so I would like it to test each time they go to a new record.

Thank you.

 

[Banana]

Just to be sure, you want this pop up to fire even when they're reviewing a pre-existing record, yes?

In this case, the OnCurrent event is the appropriate place. The fact that you get pop up 4 times tell me that it's being redundantly fired. A common cause for this is unneeded requery being done on the form that would cause several other events to fire and thus muck up the works. Do you have requery somewhere on the form or maybe other operations that would change form's recordsource, do filtering or something like that?

 

[gemma-the-husky]

try this from another direction

the normal procedure for this sort of thing is

a) make them enter the data correctly at input stage and/or
b) actually have a separate form/query that picks out all the records with deficient data, for someone to fix.

 

[Sess]

Yes. There is other events firing in the OnCurrent so I have to think of somehow to isolate that or something.
I have this in OnLoad
DoCmd.GoToRecord , , acLast
DoCmd.GoToRecord , , acFirst
so that I can display Record X of Y in a text box with this in OnCurrent
Me.RecordCountField = "Record " & CurrentRecord & " of " & RecordsetClone.RecordCount
So I guess that is making this fire 3 times.
If Nz(Me.BoMDate, " ") = " " Then
MsgBox "This item needs a Bill of Material", vbOKOnyl, "Company Name LTD"
End If

I would love to just make a form of YOU DID NOT DO YOUR WORK but the customer wants just the warning to pop up if it has not been done. It is not good enough that I have the background of the text field RED if it is null value.

 

[missinglinq]

For this:

If Nz(Me.BoMDate, " ") = " " Then
MsgBox "This item needs a Bill of Material", vbOKOnyl, "Company Name LTD"
End If

to check every record, it needs to be in the Form_Current event, along with whatever other code you have there. Having it in the Form_Load event means that it will only check the first record when the form opens.

Having said that, I have to agree with Dave in that you should be doing validation when the record is originally created ensuring that a new record is not saved until the required field is populated.

 

[Sess]

I have dropped the idea for now for the message box and stuck with the idea to have the Record x of y display. The background of the BomDate field is red if it is null so my mentor says that is good enough and he will discuss this with the customer.
Thank you for your help.