On Error 2113 - Reset field to ""

I have a form in Access 2003, it has a unbound date field where the user enters a date and then presses either 'Continue' that opens a report based on the criteria in the date filed, or 'Cancel' that closes the form.

The problem I have is that if the user enters '2' in the unbound date field or any other value that is not a valid date, he gets the following massage 'The value you entered isn't valid for this field', but I wanted to be able to (1) replace it with my own massage and (2) reset the value of the filed to "" so that the user can either enter a new date or cancel.

I was able to replace the massage with my own using the following code:

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

   Const INPUTMASK_VIOLATION = 2113
   
    If DataErr = INPUTMASK_VIOLATION Then
   
        MsgBox "The Date you have enterd is not valid, please enter a valid Date, or Cancel.", vbInformation
       
                       
       Response = acDataErrContinue
       
    End If
   
End Sub

But I wasn't able to reset the value lin the date filed to "" so that the user can either enter a correct date or cancel, so if the user wants to cancel, he / she must first delete the '2' or whAtever wrOng value thaye had in it and then cancel; how would I do that ?

Tnx
JohnTallAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike77Commented:
Add after your message box :

Me.YourTextBoxName = ""
Or
Me.YourTextBoxName = Null
JohnTallAuthor Commented:
For both options I got the following error on that line: Error Number: -2147352567; The macro or function set to the BeforeUpdate or ValidationRule property for this field is preventing Access Reports from saving the data in the field.
Mike77Commented:
Do you have any code in the before_update or any validations in the validationrule property of the text box?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

JohnTallAuthor Commented:
no code in before_update and no code in the validationrule property
Mike77Commented:
Try this instead :

Private Sub Form_Error(DataErr As Integer, Response As Integer)
    
    If DataErr = 2279 Or DataErr = 2107 Then

        MsgBox "The Date you have enterd is not valid, please enter a valid Date, or Cancel.", vbInformation
        Response = acDataErrContinue
        Me.YourTextBoxName.Undo
        
    End If

End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JohnTallAuthor Commented:
Got it, it is working, I just added the following line:

Or DataErr = 2113

to make sure that it traps this error as well.

Thanks.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.