Prevent entry of a future date on form

[Indigo]

Using a form bound to a table in Access 2003, I want to prevent users
from entering a future date in a text box. In the after update event
for the text box I have:

If [ClosedDate] > Now() Then
MsgBox "The Closed Date must not be greater than today's date.", vbOKOnly
Cancel = True
End If

However, when I compile the database I get an error:

"Variable not defined"

What am I missing?

 

[pbaldy]

You want the before update event. I would test against Date() as well.

 

[Indigo]

Thank you....actually, I just played around with the validation rule

<=Now()

and that works too

 

[ShaneMan]

Using a form bound to a table in Access 2003, I want to prevent users
from entering a future date in a text box. In the after update event
for the text box I have:

If [ClosedDate] > Now() Then
MsgBox "The Closed Date must not be greater than today's date.", vbOKOnly
Cancel = True
End If

However, when I compile the database I get an error:

"Variable not defined"

What am I missing?

Hey Indigo,

I think your problem is there is not a "Cancel" event associated with AfterUpdate. If you changed the cancel part and made your control go to empty or null, I'll bet you'll get the results your after.

HTH,
Shane

Edit: I forgot to mention the "better to use BeforeUpdate" part but I see Paul has already recommended that.

 

[missinglinq]

Cancel = True was the source of the error, as Shane said, and Paul is correct in saying this type of code belongs in the textbox BeforeUpdate event; placed there your code would work as expected, returning focus back to the proper field. Using the Validation rule obviously works in this case as well! Which just goes to prove my signature line!