On Error 2113 - Reset field to ""

JohnTall
JohnTall used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Add after your message box :

Me.YourTextBoxName = ""
Or
Me.YourTextBoxName = Null

Author

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.

Commented:
Do you have any code in the before_update or any validations in the validationrule property of the text box?
Ensure you’re charging the right price for your IT

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

Author

Commented:
no code in before_update and no code in the validationrule property
Commented:
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

Author

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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial