Data Validation

[Lynn_AccessUser]

I am trying to set validation on a date field as follows:

If DateField > Now() Then
Msgbox "You can not enter in a date greater than today's date."
End If

I would like the code to then delete whatever value the user entered and set the focus back to that field.

I have tried the above code as is on the before update, after update, on change, on enter, on got focus, and on lost focus events. Problem is that the user get the msgbox but then after they hit OK it tabs to the next field leaving the data as is in the date field so the invalid date gets saved once the record is closed.

 

[Mile-O]

Private Sub YourControl_BeforeUpdate(Cancel As Integer)

   If txtDateField > Date() Then 
        Msgbox "You can not enter in a date greater than today's date." , vbExclamation, "Improper Date"
        Me.txtDateField.Undo
        Cancel = True
    End If 

End Sub

 

[Lynn_AccessUser]

Thanks

That worked . . . thanks!

 

[teiben]

This code works, sort of! The user can still save!

 

[PierreR]

Teiben. Could you please clarify what you mean when you say that the user can still save. It does not appear to be the case in the very similar example of that code in attached zip file.

 

[teiben]

I'm not using table validation but rather form validation:

Private Sub DueDate_BeforeUpdate(Cancel As Integer)
If DateDiff("d", Me.dateIn, Me.DueDate) <= 3 Then

MsgBox "You must allow 3 days to process any PPAP requests.", _
vbExclamation, "Improper Date"
Me.DueDate.Undo
Cancel = True
End If
End Sub

The message works fine, but with a due date of 9/22/04 on a datein of 9/22/04, the date is still being save to the table.