Clearing data/value from a date textbox access form

Question

I am using a normal textbox to input a date, if the user types an invalid date I get an information msg box from access telling me the date is invalid and I get returned to that textbox. I need to use a date picker and if the date input is wrong the textbox.value = "", I dont want to be returned to that textbox with the same value. I am using a class to validate every unbound control, but all date controls work with the automatic access validation.

After I get this error I want my textbox value to be set to "". What event or input validation triggers this input box and how can I code in VBA or Access to make the textbox.value = ""?? Can I eliminate the access msg box so that I can code in vba my own validation rule? I can do this with normal textboxes but I need to have the datepicker. I tried using textbox.value = "" on the got focus event and almost all other events but I need something that works.

Answer 1

You could bypass access default error, test with:

Private Sub Form_Error(DataErr As Integer, Response As Integer)

  If DataErr = 2113 Then
    ' Ignore the error and continue without displaying the default Microsoft Access error message     
    Response = acDataErrContinue
    ' Your custom error Message
    MsgBox "Only date are acceptable in this box", vbCritical, "Error"
    ' Undo 
    ActiveControl.Undo
  End If

End Sub

See: http://msdn.microsoft.com/en-us/library/office/ff836345.aspx