Access VBA Error Handling: Trapping and Fixing Errors

Access VBA error handling lets a procedure respond when a runtime error occurs instead of stopping unexpectedly. Use On Error GoTo HandlerLabel for important procedures, capture the error details from the Err object, and then report the problem or clean up safely; reserve On Error Resume Next for narrowly controlled operations.

What Access VBA error handling does

VBA does not use a try/catch block. Instead, an error-handling statement tells the current procedure what to do when execution encounters a runtime error, such as a failed database update, a missing file, or an invalid object reference. Without a suitable handler, Access may display its own error dialog and stop the procedure.

Error handling applies to runtime errors, not every kind of problem. A syntax mistake or undeclared variable is generally a compile-time problem: VBA cannot run the procedure until you correct it. A handler also cannot make an incorrect result correct. It gives you a controlled opportunity to identify the failure, explain it, release resources, or decide whether to continue.

Access applications commonly use handlers in form and report event procedures, button-click code, import routines, and other database operations. Short code that cannot fail may not need a complicated handler, but procedures that change data or interact with files and external services should normally account for likely failures.

How On Error statements work

There are three common forms of the On Error statement. Their effects apply to the procedure in which they appear, not automatically to every procedure it calls.

On Error GoTo HandlerLabel activates an error handler at a label in the same procedure. This is the usual pattern for a procedure that needs to report errors or perform cleanup.
On Error Resume Next tells VBA to continue with the statement after the one that failed. It can be useful for a specific operation where failure is expected, but broad use can conceal important errors.
On Error GoTo 0 disables the current procedure’s enabled handler. Use it when you deliberately want normal error behavior again, such as after a limited section using Resume Next.

Inside a handler, the Resume statement controls what happens next. Resume retries the failed line, Resume Next continues at the following line, and Resume SomeLabel transfers control to a label. These are not interchangeable: retrying a line that will fail for the same reason can create a loop, while skipping it may leave later code without a required value.

A practical procedure pattern

For a database action, use a handler label, keep the successful path separate from the handler, and capture the error details before running cleanup. This example updates a table and uses DAO’s dbFailOnError option so a failed update raises an error rather than silently leaving records unchanged.

Private Sub UpdateOrderStatus()
    On Error GoTo Err_Handler

    Dim db As DAO.Database
    Dim savedNumber As Long
    Dim savedDescription As String
    Dim savedSource As String

    Set db = CurrentDb
    db.Execute "UPDATE Orders SET Status = 'Ready' WHERE Status Is Null;", dbFailOnError
    MsgBox db.RecordsAffected & " order(s) updated.", vbInformation

Exit_Handler:
    Set db = Nothing
    Exit Sub

Err_Handler:
    savedNumber = Err.Number
    savedDescription = Err.Description
    savedSource = Err.Source
    MsgBox "The update could not be completed." & vbCrLf & _
           "Error " & savedNumber & ": " & savedDescription & vbCrLf & _
           "Source: " & savedSource, vbExclamation
    Resume Exit_Handler
End Sub

Before running any macro or VBA code that changes data, save a copy of the database or workbook. Macro actions cannot be undone with Ctrl+Z, and an error handler does not reverse changes that have already been committed. The example assumes an Orders table with a Status field and a working DAO reference, as is typical in Access.

The handler saves Err.Number, Err.Description, and Err.Source before cleanup because later operations can replace the current error information. In a production application, replace or supplement the message box with a logging routine or a user-friendly message. Avoid showing technical details to users if those details expose sensitive information.

Understanding the Err object

The built-in Err object describes the most recent runtime error. Its most useful properties are Number, a numeric error code; Description, a text explanation; and Source, which can identify the object or application that raised it. Save these values immediately in the handler if you need them later.

Err.Clear clears the current error information. This matters when you intentionally use On Error Resume Next: check whether the operation failed, record or handle that result, and clear the error before checking another operation. Do not assume that an empty message means success; test the error number or the specific result you expect.

Erl can report the line number associated with an error when the procedure contains numbered lines. Without line numbers, it normally returns zero, so it is not a substitute for useful logging or debugging. If you add line numbers to important routines, capture Erl in the handler along with the other error details.

When Resume Next is appropriate

On Error Resume Next is sometimes appropriate when the code expects a particular operation might fail and can make a deliberate decision immediately afterward. For example, code may try to access an optional object, inspect whether it was obtained, and then either use it or take an alternate path. The key is to keep the region small and test the outcome directly.

Do not put On Error Resume Next at the top of a long procedure merely to stop error messages. VBA will continue past failures, which can make later statements operate on missing objects, incomplete data, or default values. That often turns one clear error into a harder-to-diagnose result. Once the narrow operation is checked, use On Error GoTo 0 or restore the procedure’s intended handler.

Remember that On Error GoTo 0 disables the active handler; it is not the same as Err.Clear. One changes how future errors are handled, while the other clears stored error information. Use each for its specific purpose.

Errors in forms, reports, and called procedures

Event procedures need the same care as ordinary procedures. A button’s Click event can call a routine that updates data or opens a form; if that routine does not handle an error, the error can return to the caller. A handler in the event procedure can catch an error that propagates back to it, but it cannot catch an error that the called procedure has already handled and suppressed.

For maintainable code, decide which procedure owns the response. A low-level routine can report or re-raise an error, while the form event can translate it into a message appropriate for the user. Avoid displaying duplicate messages from both levels. If the called routine performs cleanup, make sure it does so before returning or propagating the error.

Some Access actions can be canceled as part of their normal behavior. For instance, an action can produce error 2501 when the action is canceled. If cancellation is an expected outcome in a specific workflow, handle that code separately from unexpected failures; do not treat every error as harmless cancellation. Error numbers and circumstances matter.

Database errors and reliable cleanup

Database operations deserve special attention because a failure can affect several records or occur after earlier work has succeeded. With DAO, use Database.Execute with dbFailOnError for action queries when you want update failures to raise an error. Check transaction requirements separately: a handler alone does not make a sequence of multiple operations atomic.

If a procedure starts a transaction, its error path should roll it back when appropriate, while its success path commits it. Make sure the transaction is active before attempting rollback, and preserve the original error details before cleanup operations. For complex multi-step updates, design and test the transaction boundaries rather than assuming that a later error automatically undoes earlier statements.

Cleanup can include closing a recordset, releasing an object variable, or removing a temporary file. Put cleanup on a path reached after both success and failure, but account for the possibility that cleanup itself can fail. Keep cleanup simple and avoid allowing a secondary cleanup error to hide the original failure.

Finding and diagnosing errors in the VBA editor

Error handling should not replace debugging. In the Visual Basic Editor, use Debug > Compile to find compile errors, then run the procedure in a test copy of the database. Set breakpoints by clicking the margin beside a line, or use Debug > Step Into to execute code one statement at a time. Inspect variables and the call stack when execution pauses.

The editor’s error-trapping setting affects where execution stops. In the Visual Basic Editor, open Tools > Options > General and review Error Trapping. “Break on Unhandled Errors” is generally useful for normal development, while “Break on All Errors” can interrupt code even when a handler is present. “Break in Class Module” changes behavior for class-module errors. During diagnosis, choose a setting that exposes the failure without confusing an expected, handled error with an unhandled one.

During development, do not hide errors just to make a form appear to work. Reproduce the problem with representative data, identify the failing line, and determine whether the code, the data, or an outside dependency is responsible. Once fixed, test both the success path and the failure path.

Common mistakes to avoid

Forgetting Exit Sub or Exit Function before the handler label. Successful execution can otherwise fall through into the handler code.
Using a misspelled handler label. A target label must exist in the same procedure.
Reading Err.Description only after other code has run. Save the original values first.
Using Resume Next without checking whether the failed operation produced a usable result.
Assuming a handler in one procedure automatically protects every procedure it calls. Each procedure needs appropriate handling or a deliberate way to propagate errors.
Showing a generic “success” message after an error was suppressed. Confirm the actual result before reporting success.

Frequently Asked Questions

What is the basic Access VBA error-handling statement?
For a procedure that needs a handler, start with On Error GoTo HandlerLabel and place the matching label in that procedure. Add an exit before the label so normal execution does not fall into the error-handling code.

How do I display an Access VBA error message?
In the handler, read Err.Number and Err.Description, then use MsgBox or a logging routine. Save the values before cleanup or other calls that could change the current error information.

What is the difference between On Error GoTo 0 and Err.Clear?
On Error GoTo 0 disables the procedure’s enabled handler. Err.Clear clears the current error details. They affect different things and are not substitutes for one another.

Why does Access stop even though I added an error handler?
Check that the failing line is in the same procedure as the handler or that the called procedure propagates the error. Also check for compile errors, an invalid label, and the editor’s Error Trapping setting. Some errors cannot be solved by adding a runtime handler.

Can VBA error handling undo an Access update?
No. A handler controls what the code does after an error; it does not automatically reverse completed changes. Use appropriate transaction handling for multi-step database work and keep a backup before running code that changes data.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.