The Form Error Event: How to Handle "Untrappable" Access Errors

There is a category of errors that get raised in Microsoft Access that are similar to runtime errors, but are not triggered by VBA code.  

Rather, these errors are triggered by built-in form and report functionality.  Here's an example of what these errors look like in the Access user interface:

Unlike a standard VBA runtime error, pressing Ctrl+Break dismisses the message box but does not take you to the source of the error in code.  

You can't hide these errors with On Error Resume Next.

You can't hide these errors with DoCmd.SetWarnings False.

Is the situation hopeless?  Is there no way to hide or customize these built-in error messages?  For a long time, I thought the answer was yes.  And then I learned about the Form Error event.

About the Form Error Event

From the Access docs:

The Error event occurs when a run-time error is produced in Microsoft Access when a form or report has the focus. This includes Microsoft Jet database engine errors, but not run-time errors in Visual Basic.

This event is raised when there is an error in a form or report that does not originate with VBA code.

There are a few ways to recognize these types of errors:

• They appear in a formatted message box where the top line is bold, followed by a blank line, and then a non-bold additional message.
• The error is not related to any custom code you wrote.
• Pressing Ctrl+Break does not enter break mode in the VBA editor; it just dismisses the message.

For a long time, I thought these sorts of errors simply couldn't be trapped.  It turns out they can sort of be "pre-trapped" within the form (or report) Error event.  

Before the error message appears on screen, the Form Error event is raised.  The event includes two parameters:

• DataErr: The error code returned by the Err object when an error occurs.
• Response: The setting determines whether an error message is displayed.

DataErr

This will be a number that corresponds with an Access error.  

The best way to determine this number is to set a breakpoint within the Form Error event and see what the error number is when the event gets triggered.  To confirm you have the correct number, you can use the AccessError function to return the associated error message:

Response

This is sort of a "return value" for the Error event.  It's similar to how you "return" values in ribbon callbacks via ByRef parameters.

You set this value to tell Access how to behave.  There are only two values you can assign to this parameter:

• acDataErrDisplay (default): Displays the built-in error message.
• acDataErrContinue: Hides the built-in error message.

In practice, you rarely use acDataErrDisplay since the Response is set to that value by default.  Instead, you would use acDataErrContinue if you want to override the default behavior and suppress the built-in error message box:

'Hide the error message:
Response = acDataErrContinue

The one time that acDataErrDisplay can be useful is if you have custom error handling for several error codes.  You can suppress the error message, execute a Select Case statement to handle the custom errors, then restore the error message for all other unhandled errors.  

For example, let's re-write the Select Case Form Error sample code from the Microsoft documentation:

Original Microsoft Sample Code

Private Sub Form_Error(DataErr As Integer, Response As Integer)
    Select Case DataErr
        Case 2113
            MsgBox "Only numbers are acceptable in this box", vbCritical, "Call 1-800-123-4567"
            Response = acDataErrContinue
        Case 2237
            MsgBox "You can only choose from the dropdown box"
            Response = acDataErrContinue
        Case 3022
            MsgBox "You entered a value that exists already in another record"
            Response = acDataErrContinue
            SSN.Value = SSN.OldValue
        Case 3314
            MsgBox "The DOH is required, so you cannot leave this field empty"
            Response = acDataErrContinue
        Case Else
            Response = acDataErrDisplay
    End Select
    ActiveControl.Undo
End Sub

Refactored Sample Code

Private Sub Form_Error(DataErr As Integer, Response As Integer)
    'Hide the default error message for custom-handled error codes
    Response = acDataErrContinue
    Select Case DataErr
        Case 2113
            MsgBox "Only numbers are acceptable in this box", vbCritical, "Call 1-800-123-4567"
        Case 2237
            MsgBox "You can only choose from the dropdown box"
        Case 3022
            MsgBox "You entered a value that exists already in another record"
            SSN.Value = SSN.OldValue
        Case 3314
            MsgBox "The DOH is required, so you cannot leave this field empty"
        Case Else
            'Restore the default error message for all other errors
            Response = acDataErrDisplay
    End Select
    ActiveControl.Undo
End Sub

Handling the Form Error Event

The Form Error event is like any other form, report, or control event.

To handle this event, you need to set the form's On Error property to "[Event Procedure]" in the form's Property Sheet:

While you can also set property sheet event handlers to macros or custom VBA functions, it doesn't make sense to do that with the Error event, as you can only set the Response parameter if you handle the On Error property with an "[Event Procedure]".