Dear BK I have now tried your solu

[Nvijayk]

Dear BK
I have now tried your solution and it works as follows.
When an invalid date is entered, it throws a message and if I clicked okay, it then just carries on and allows me to complete the form and save it with the invalid date in the table.I need it to stop me from accepting an invalid date by taking the cursor back to Date field in the form and not allow update at all.
Also, when the date is left blank, it allows the transaction to be updated but does not update the table with the startdate as required.If this can be problem, I would rather have the form reject null value in the date field.
Please advise

regards

Vijay

 

[jebry]

Hi Vijay!

In the BeforeUpdate event procedure for the text box you can use this code:

If IsDate(Me!YourTextBox) = False Then
If MsgBox("You entered an invalid date. Your record will not be saved if a correct date is not entered. Do you want to enter the correct date now?", vbYesNo) = vbYes Then
Cancel = -1
End If
End If

Then in the BeforeUpdate event procedure for the form use this code:

If IsDate(Me!YourTextBox) = False Then
If MsgBox("You entered an invalid date into the start date field. If this is not corrected your data will not be saved. Do you want to correct this now?", vbYesNo) = vbYes Then
Cancel = -1
Call YourTextBox.SetFocus
Else
Me.Undo
DoCmd.Close acForm, Me.Name
End If
End If

hth
Jeff Bridgham
bridgham@purdue.edu