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
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.
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:
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]".