We help IT Professionals succeed at work.

Validation using the BeforeUpdate Event

I have an Access Project linked to SQL 2008 R2. My problem is with a form I have that is using a view.

The majority of the data I can validate using the BeforeUpdate Event, but I have 2 fields that cannot contain nulls. I have this specified in the base table, but unfortunately the form gives me a system error message before my BeforeUpdate event triggers. I want to give the end user a more friendly message than the confusing system message.

I know I could allow nulls in the base table, and check data entry at runtime, but I would prefer to keep the base table the way I have it.

Is there another event that I am missing?
Comment
Watch Question

Author

Commented:
The message I am trying to trap is: You tried to assign the Null value to a variable that is not a Variant data type.
Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007
Commented:
You must trap that error in the Form Error event ...

mx
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
This is the idea:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
    Response = acDataErrContinue
    Select Case DataErr
       
        Case 3314   'Required Field
            MsgBox "One or more required fields are missing. Please enter ALL required fields <Your Field List Here>"

            MsgBox "An unexpected error occurred " & DataErr & "  " & AccessError(DataErr)
    End Select
End Sub


mx

Author

Commented:
THANK YOU SO MUCH. I didn't realize that event existed (is one of the newer ones!). Solution worked quickly and easily. This will have many future applications. THANKS AGAIN!!!!!
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
Well, it's not a new event, but there are a small subset of errors that are *only* rendered in the Form Error event, including the Required Field error.  I've never been able to get a complete list.  Write Conflict errors are another example.  Also, if you have an Input Mask ... and user enters something that does not conform to mask,  a trappable error will occur in that event.  Stuff like that.

mx

Explore More ContentExplore courses, solutions, and other research materials related to this topic.