Problem with form displaying message for date field with code for checking ISNull in Exit event

[DWL]

I am working with a form that has tab controls. I code for the
required fields that if null will give the user a message. I also have
code when leaving the form to run a delete query that deletes the
record if the required field is null. The problem I am having is with
the two required fields that are dates. When creating a new record,
when the form is loaded, even before the user can begin entry, the
message box pops up. It doesn't do this for the fields that are not
dates. Below is my code. The date field is on the third tab and the
form sets focus to the project number on the first tab when the form
opens.

Private Sub Plans_Approved_Exit(Cancel As Integer)
If IsNull(Plans_Approved) Then
MsgBox ("The plans approved date is a required field.")
'This message comes up at beginning of form even if not on this
sheet tab
Cancel = True

End If
End Sub

This is the same code I am using for text fields that are null. Should
dates be handled differently? I don't want the message to appear until
the user has left the field.

 

[Lynn Trapp]

Try moving all your validation to the BeforeUpdate event of the Form.

 

[missinglinq via AccessMonster.com]

Doing validation in the Form's BeforeUpdate event, as Lynn suggested, is a
standard method of carrying out this function, as is doing it in the
control's BeforeUpdate event. Having said that, your code should still work!
I plugged it into a date field on a test db I keep, and it works as expected,
which begs the question of why the validation code is running when you
haven't even entered the Plans_Approved control, much less exited it! When
totally erratic behaviour raises its ugly head one of the first things to
think about is corruption. I'd create a blank db and import everything from
your original db into it and see what happens then. The danger, of course, is
that if corruption exits, it may mess up other parts of your db as well.

 

[Lynn Trapp]

Well, since subform controls load before the main form, it's possible that
the Plans_approved control IS, in fact, being exited when the main form
opens.

 

[DWL]

I didn't know subform controls load first and cannot find anything that
indicates this in "help" or Microsoft. I have many subforms on the
main form and each tab control (form). Is there an order for subform
controls to load?

I would agree that the loading of the subform controls first might be
the problem except the IsNull checks for the other controls on the
subforms don't display the message on form load. It seems the problem
only occurs with the date controls. They are formatted as short date.
So, I'm still thinking it has something to do with the date. This
might explain why missinglinq's test db works if it doesn't have a
subform. If it is, in fact, loading first, again, why would it trigger
the message/check if the setfocus is to the first control on the main
form - which is the project number? ?? If it is doing that, do you
know a way to divert it?

Thanks for your suggestion to move the controls to the form's "before
update" event but this only checks when the data is changed. If the
user tabs through the control, it will not activate (except of course
for the problem I am reporting where it activates regardless......)
What I need is for the event (wherever it is placed) to check for the
ISNull whether or not the control was accessed. Do you know of a way
to do that? I do have a check on the main form unload for all the
required fields but my users want the check also to be done at the
control level so they can know right away that it is required. (even
though it has a green background to indicate required.