Sorry, I didn't understand your explanation about what was happening with the BeforeUpdate and Undo.
Not that this matters to you and I'm sure this has something to do with my system's date format settings. But if I type 29/2/5 into a date field, I get:
DateField.OldValue = 2/5/2029
DateField.Value = 2/5/2029
DateField.Text = 29/2/5
I assume that means the when you use the BeforeUpdate event to validate the format, you should look at .Text to see and edit what the user typed.
The Undo does give crazy results. Why does Access set the OldValue to garbage? Must have something to do with the fact that Access is aware that the field is a date field? Whatever!!!
Sorry, but it looks like you'll have to do things the hard way. You could try this code in the module of the form:
Code:
Dim varSampleFromDate As Variant
Dim blnSampleFromDateBad As Boolean
Public Function MyDateValidation(pvarDate As Variant) As Boolean
' NOTE: Undo won't work if validation occurs in the BeforeUpdate event
' because the OldValue of the field gets set according to the bad
' date that was entered.
If IsNull(pvarDate) Then
MyDateValidation = True
Else
If pvarDate = "29/2/5" Then ' validate date format
MyDateValidation = False
Else
MyDateValidation = True
End If
End If
End Function
Private Sub SampleFromDate_BeforeUpdate(Cancel As Integer)
blnSampleFromDateBad = MyDateValidation(Me.SampleFromDate)
End Sub
Private Sub SampleFromDate_Enter()
blnSampleFromDateBad = False
varSampleFromDate = Me.SampleFromDate
End Sub
Private Sub SampleFromDate_Exit(Cancel As Integer)
If blnSampleFromDateBad Then
blnSampleFromDateBad = False
Me.SampleFromDate = varSampleFromDate
Cancel = True
End If
End Sub
Of course, depending on how important that date is, you could just let the bad date be recorded. Hopefully, users will get used entering dates properly.