Microsoft Access VBA Error Handling: A Practical Guide

Microsoft Access VBA error handling uses the On Error statement to decide what a procedure does when a runtime error occurs. For most procedures, use On Error GoTo to send unexpected errors to a handler that records useful details, informs the user appropriately, and exits safely; reserve On Error Resume Next for narrowly controlled operations.

What Access VBA error handling does

VBA does not automatically recover from every runtime error. Without an active handler, an error can stop a procedure and show a dialog containing an error number and description. A handler lets the code respond deliberately instead: it can cancel a partially completed operation, show a clear message, log diagnostic information, or pass control to a safe exit point.

Error handling is separate from checking whether a user entered valid information. If a form requires a date or a selection, validate that before running a query. A missing value is usually an expected condition to explain or prevent, not an exceptional failure to conceal with a broad error handler. Use handlers for failures that can still occur despite validation, such as a locked record, an unavailable linked table, or an unexpected problem executing a query.

Handlers are procedure-level. An error handler in one subroutine does not automatically catch an error in another subroutine it calls. Each procedure that can fail should either handle its own errors or let the calling procedure handle them through a deliberate design. This is why reusable routines often report or re-raise errors rather than showing a message box themselves.

How On Error and the Err object work

The main choices are On Error GoTo label, On Error Resume Next, and On Error GoTo 0. The first enables a handler at a label in the current procedure. The second tells VBA to continue at the statement immediately after the one that failed. The third disables the current procedure’s enabled error handler. These settings apply to the procedure, so reset or change them intentionally when using more than one approach.

When an error occurs, the Err object exposes details about it. Capture its values before running other code that might replace them:

Err.Number is the numeric error identifier.
Err.Description is the explanatory text.
Err.Source identifies the source reported for the error, when available.

A common structure is to put normal cleanup and exit code before the handler label. In the handler, capture the error details, respond, then use Resume to go to the cleanup or exit section. Resume also leaves the active handler state, which matters if execution continues in the procedure. Do not let execution simply fall from the handler into code intended only for the success path.

A practical error-handler example

This form button example updates one order and uses DAO’s dbFailOnError option so an unsuccessful action raises an error rather than silently leaving an update incomplete. The table and field names are illustrative; replace them with names in your own database. Save a copy of the database before running a macro or VBA procedure that changes data: macro actions cannot be undone with Ctrl+Z.

Private Sub cmdUpdate_Click()
    On Error GoTo ErrHandler

    Dim db As DAO.Database
    Dim affected As Long
    Dim errNumber As Long
    Dim errDescription As String
    Dim errSource As String

    Set db = CurrentDb
    db.Execute "UPDATE Orders SET Status = 'Processed' WHERE OrderID = 1001;", dbFailOnError
    affected = db.RecordsAffected

    MsgBox CStr(affected) & " order record(s) updated.", vbInformation

ExitHere:
    Set db = Nothing
    Exit Sub

ErrHandler:
    errNumber = Err.Number
    errDescription = Err.Description
    errSource = Err.Source

    MsgBox "The order could not be updated." & vbCrLf & _
           "Error " & CStr(errNumber) & ": " & errDescription & vbCrLf & _
           "Source: " & errSource, vbExclamation
    Resume ExitHere
End Sub

The handler saves the error details before displaying a message, then resumes at ExitHere so the database object is released. On success, execution reaches the same cleanup section and exits without entering the handler. CurrentDb returns a DAO database object in Access; if your project does not recognize DAO.Database, check the project’s DAO reference in the Visual Basic Editor’s Tools > References dialog.

When Resume Next is appropriate

On Error Resume Next can be useful when a specific failure is expected and the next line checks whether it happened. For example, it is sometimes used while attempting to access an optional object or clean up a resource that may already be absent. Its safety depends on keeping the scope short and checking Err.Number immediately after the operation.

A dangerous pattern is enabling Resume Next for an entire long procedure. A failed assignment, query, or object call may then be followed by code that assumes it succeeded. That can produce misleading results or a later error far from the original cause. A safer pattern is to enable it for one operation, inspect the result, clear or record the error as appropriate, and restore the normal handler. For instance:

On Error Resume Next
someObject.Refresh
If Err.Number <> 0 Then
    Debug.Print Err.Number, Err.Description
    Err.Clear
End If
On Error GoTo ErrHandler

This is only a pattern: use an object and operation that make sense in your procedure. Err.Clear resets the current error information; it does not repair the failed operation. If the failure is not genuinely optional, let the normal handler deal with it instead.

Handling database and form errors

For DAO action queries, Database.Execute with dbFailOnError is generally preferable to relying on a query to appear successful. It makes a failed action query raise an error that a VBA handler can catch. After a successful call, RecordsAffected can tell you how many records the action changed. Do not report success merely because the code reached the next line if the operation itself can fail silently under the options you used.

Forms also need a distinction between validation and error recovery. Use form events and validation rules to prevent invalid entries, and show a specific prompt for a known condition. Use a VBA handler for runtime problems that remain possible. For example, if a user tries to save a record that conflicts with a unique index, the database may reject it; the interface can explain that the value must be unique without displaying a cryptic diagnostic as the only guidance.

Some failures involve multiple database operations. If several changes must either all succeed or all be reversed, consider a DAO workspace transaction, using the same workspace for the participating operations and committing only after they succeed. On failure, roll back before leaving. Transaction support and behavior depend on the data source, so verify that the tables and operations involved support the transaction you need. A rollback can itself fail, so production code should treat rollback as a separate operation and preserve the original error details first.

Designing handlers that help you diagnose problems

A useful handler does more than suppress a dialog. It preserves enough context for someone to reproduce and fix the issue, while avoiding unnecessary technical detail in a message shown to an ordinary user. Capture the error number, description, source, procedure name, and relevant record or task identifier when appropriate. Avoid logging passwords, sensitive personal data, or complete connection strings.

Use a clear user message that says what failed and what the user can do next.
Keep technical diagnostics in a development log or a controlled error table when the application needs a persistent record.
Keep logging code simple: a logger can fail too, so it should not hide the original error or cause an endless chain of errors.
For reusable procedures, consider raising an error to the caller rather than showing a message box inside the routine.

To raise a meaningful application-specific error, VBA provides Err.Raise. A custom error number is commonly based on vbObjectError, with a number chosen for the application. Include a helpful source and description. The caller can then handle it in the same way as other runtime errors. This approach separates business logic from interface decisions: a shared data routine can report a failure, while a form decides what message to show.

Common problems and how to troubleshoot them

If VBA stops in the editor despite an error handler, check whether the Visual Basic Editor’s error-trapping setting is configured to break on all errors. That setting can affect debugging behavior. Also verify that the error occurs in the procedure where you expect a handler; an error in a called procedure may be handled there or passed back, depending on its own code.

A handler that produces a second error can obscure the first. For example, a cleanup statement or logging routine may fail while the handler is already active. Capture the original Err values first, keep the handler’s work minimal, and make cleanup resilient where necessary. Avoid using On Error Resume Next as a blanket fix for repeated errors.

For development, place a breakpoint or use Debug.Print to inspect values, then use the Visual Basic Editor’s Debug > Compile command to catch compile-time issues. Compilation catches problems such as undeclared variables when Option Explicit is used, but it does not prove that a query will succeed with real data or that every runtime condition is handled. Test error paths as well as successful ones, using a backup or test copy of the database.

If line-level diagnostics matter, VBA’s Erl function can report the most recently executed line number, but only when the procedure contains numbered lines. It returns zero when no applicable line number is available. Line numbering is optional and adds maintenance work, so many Access applications instead log the procedure name and relevant operation alongside the error details.

Frequently Asked Questions

What is the best error handling method in Access VBA?
For unexpected errors, use On Error GoTo with a handler that records the original error details, performs safe cleanup, and exits or passes the error on deliberately. Use Resume Next only for a specific operation whose failure you check immediately.

What is the difference between On Error GoTo and On Error Resume Next?
On Error GoTo label transfers control to a handler when an error occurs. On Error Resume Next skips the failing statement and continues with the next one, so the procedure must inspect Err to determine whether the operation worked.

How do I clear an error in VBA?
Use Err.Clear after capturing or handling the current error information. It clears the Err object’s details; it does not undo changes, fix the cause, or replace a proper error-handling decision.

Why does my Access VBA error handler not run?
Check that the procedure has an active On Error GoTo statement, that its label exists in the same procedure, and that the error is not occurring while the handler is already active. Also check the editor’s error-trapping setting and whether a called procedure has its own handler.

Should every Access VBA procedure have an error handler?
Not necessarily. Add handlers where an operation can fail and the procedure can respond usefully, especially around database updates and resource cleanup. Small routines may let errors propagate to a caller that can handle them with better context.

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.