display user friendly message for date validation

[arnold]

Dear friends,

I am using the following code in the afterupdate event of my textbox:

 If Not IsDate([MyTextBox]) Then
     MsgBox "Invalid date!"
Else
     'do nothing, date is OK
End If

My problem is that if the user entered false date such as 15-15-2009
The Ms Access error message is displayed "The value you entered is not valid for this field" instead of my own message which is "invalid date!"
How this problem can be solved?
Any help will be highly appreciated

[ajalwaysus]

If this is a bound field to a date field in your table, then the reason you are getting the error is because you are allowing the update to the table to take place and then checking it afterupdate, so you are getting access's error because it supersedes your check. If you want to catch this before it tries to save to your table you need to do this on beforeupdate.

Like this:

Private Sub MyTextBox_BeforeUpdate(Cancel As Integer)
  If Not IsDate([MyTextBox]) Then 
    MsgBox "Invalid date!"
    Cancel = True
  Else
    'do nothing, date is OK
   End If
End Sub

Let me know if this works for you, or if you need me to clarify the issue further.

-AJ

[NeoPa]

AJ is Spot On (of course). I would simply add that the Else part of the If is not necessary in this instance, and should probably be omitted.

Welcome to Bytes!

[arnold]

Thanks ajalwaysus for your hard effort.
But I'm still having the same problem

Remark : regarding your point whether my text box is bound field to a date field in my table the answer is yes

[ajalwaysus]

I need to see the code, as this should work without issue. Please copy and paste your code as it appears in your DB to this thread and let me take a look, also make sure you take out the afterupdate sub as it is no longer needed.

-AJ

[ajalwaysus]

Disregard my last post. I tested this myself and found myself surprised and a little annoyed at Microsoft, it appears that a form validation happens before the field validation which means that you need to catch this on the Form_Error, sorry for the confusion, but my logic is still correct about before and after updates, the difference is that Form_Error happens before either.

Here is the code you will need to properly catch this:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
    Select Case DataErr
        Case 2113
            If Me.ActiveControl.Name = "MyTextBox" Then
                MsgBox "Invalid date!"
                Response = acDataErrContinue
            End If
    End Select
End Sub

This code triggers on Form_Error, it then checks the error number, which in this case is 2113 which means an invalid entry in a field, then it will check that we are on "MyTextBox" , if it is it will give your error message and then continue on.

Also, please make sure the before and after update subs are gone so that you aren't replicating effort.

If you have any questions don't hesitate to ask.

-AJ

[NeoPa]

Well, I've learned something tonight. Good one AJ.

[arnold]

Dear ajalwaysus ,
Thanks and God bless you
I am much obliged to you