Date Validation

[Rubi]

I want to validate that the user has entered a valid date.
this should be simple with the validation rule and validation text in the controls properties...

but ACCESS gives me the standard message instead, how can I bypass this message and insert my own ?

 

[Mile-O]

Rather than set the Validation you can code it in the BeforeUpdate() event of your date field (textbox?)

i.e.

Private Sub txtMyDate_BeforeUpdate(Cancel As Integer)
    If Not IsDate(Me.txtMyDate) Then
        MsgBox "Invalid Date", vbExclamation, "Example"
        Cancel = True
    End If
End Sub

 

[Rubi]

Thanks for your help,

I gave it a try and it works only if the user didn't enter anything (being a required field it pops up the message "Invalid Date")

if the date entered is invalid, the message "Invalid Date" is not displayed and the standard access message is shown.

any suggestions

 

[Mile-O]

Private Sub txtMyDate_BeforeUpdate(Cancel As Integer)

    If IsNull(Me.txtMyDate) Then Exit Sub    
    
    If Not IsDate(Me.txtMyDate) Then
        MsgBox "Invalid Date", vbExclamation, "Example"
        Cancel = True
    End If
End Sub

 

[Mile-O]

If it's a required field, you'd be better to put this sort of code on your form's BeforeUpdate event rather than the control's.

 

[Rubi]

I don't understand it,

I entered the code and it's not working,
the Access message always apears before the message from the code.

 

[Mile-O]

Take the Validation Rule and Message off!

 

[Rubi]

They are off...

for some reason it only works with the null value

 

[Rich]

You have to use the Form Error event to trap it

 

[Rubi]

would you please show me how to do that ?

 

[Rich]

Private Sub Form_Error(DataErr As Integer, Response As Integer)
If DataErr = 2279 Then
Screen.ActiveControl.Undo
MsgBox "Please enter dates as yyyy/mm/dd"
Response = acDataErrContinue
Else
If DataErr = 2113 Then
Screen.ActiveControl.Undo
MsgBox "The value you have entered is not a valid date!"
Response = acDataErrContinue
Else
Response = acDataErrDisplay
End If
End If
End Sub

 

[Rubi]

Thanks a lot !!!

Thats exactly what I've been looking for...