MS Access VBA Error Handling: Guide, Patterns, and Tips

In Microsoft Access VBA, handle runtime errors with an On Error statement: use On Error GoTo HandlerLabel for procedures that need to report or recover from failures, and reserve On Error Resume Next for short, controlled checks. A good handler records the error details, performs any necessary cleanup, and exits safely.

What Access VBA error handling does

Error handling is the code that decides what a procedure should do when it encounters a runtime problem. Without a suitable handler, Access may show a default error dialog and stop the procedure. With a handler, you can show a clearer message, log the failure, release resources, or attempt a carefully chosen recovery.

It helps to distinguish three kinds of problems. A syntax or compile error is detected before the affected code can run; an error handler cannot repair code that does not compile. A runtime error occurs while a procedure runs, such as trying to use an unavailable object or executing an invalid database operation. A data or business-rule problem, such as a missing required value, may be better handled with validation before an operation rather than treated as an unexpected runtime failure.

Access also has errors raised by its forms and controls, and errors from database operations. These can require different handling approaches. A procedure-level handler is a useful foundation, but it is not a substitute for input validation, transaction design, or a clear user-facing message.

How On Error statements work

VBA provides three main forms of the On Error statement. Choose one according to what the procedure should do when a runtime error occurs:

On Error GoTo HandlerLabel activates an error-handling section in the current procedure. Execution transfers there when a runtime error occurs.
On Error Resume Next tells VBA to continue with the statement after the one that failed. It is useful for narrow, expected cases, but can conceal failures if left active across unrelated code.
On Error GoTo 0 disables the current procedure’s enabled error handler. It does not mean “go to line zero,” and it is not a way to clear the Err object.

Error handling is procedure-scoped: an error in one procedure does not automatically jump to a label in its caller. If a called procedure does not handle an error, the error can propagate back through the call stack to a caller that has an enabled handler. A handler that is already handling an error should not simply be allowed to fall through into normal code; use a clear exit path.

A standard procedure-level handler

This example shows a typical pattern for a DAO action query. The table and field names are illustrative, so change them to match the database. The dbFailOnError option asks DAO to raise an error if the action query fails, rather than silently ignoring certain update failures.

Public Sub ActivateCustomer()
    On Error GoTo ErrHandler

    Dim db As DAO.Database
    Dim rowsChanged As Long

    Set db = CurrentDb
    db.Execute "UPDATE Customers SET Active = True WHERE CustomerID = 42", dbFailOnError
    rowsChanged = db.RecordsAffected

CleanExit:
    Set db = Nothing
    Exit Sub

ErrHandler:
    MsgBox "Could not update the customer." & vbCrLf & _
           "Error " & Err.Number & ": " & Err.Description, vbExclamation
    Resume CleanExit
End Sub

Before running a macro or procedure that changes data, save a copy of the database or use a suitable test copy; VBA changes cannot be undone with Ctrl+Z. In this example, normal execution reaches CleanExit and exits. If the query raises an error, the handler displays its number and description, then uses Resume CleanExit to release the database variable and leave the procedure. Capturing the error details before cleanup is a good habit because later code could change the contents of Err.

CurrentDb returns a database object for the current Access database, and RecordsAffected reports the number of records affected by the most recent action query on that database object. The example stores that value but does not display it; in a real procedure, use it only if the application needs to confirm or record the update count.

Understanding Err and recording failures

Inside an error handler, the Err object provides useful information. Err.Number is the error number, Err.Description is a text description, and Err.Source may identify the source that raised the error. Save any details you need before calling other code that might raise an error or reset the error information.

For a small database, a clear message may be enough. For an application used by several people, logging can help a developer diagnose issues that are difficult to reproduce. A log might capture the date and time, procedure name, error number, description, and relevant non-sensitive context, such as a record key. Avoid logging passwords, private customer details, or other unnecessary sensitive data.

Logging itself can fail—for example, if a table is unavailable or a file cannot be written. Keep the logging path simple, and do not let a secondary logging error obscure the original failure. A useful design records the original error details in local variables before attempting cleanup or logging. Where appropriate, show users a concise message and keep the technical details in a restricted log rather than exposing implementation details in a dialog.

When Resume Next is appropriate

On Error Resume Next can be useful when a particular failure is expected and the next line checks whether it happened. Its safety comes from keeping its scope small, checking Err immediately, and restoring normal handling as soon as possible. It is not a general replacement for a handler label.

For example, a routine may try to access an optional object that is not always present. If you use this pattern, adapt it to a real object and explicitly check the result. Do not allow a missing object to pass unnoticed into later operations.

Public Sub CheckOptionalObject()
    On Error Resume Next

    Dim obj As Object
    Dim errorNumber As Long

    Set obj = Application.Forms("OptionalForm")
    errorNumber = Err.Number
    Err.Clear
    On Error GoTo 0

    If errorNumber <> 0 Then
        MsgBox "The optional form is not open.", vbInformation
        Exit Sub
    End If

    ' Continue only after confirming the object is available.
End Sub

This is a pattern to adapt, not a universal way to test whether a form exists; use the Access object and state checks suitable for the situation. The important point is that the code saves the error number, clears the error state, and turns off Resume Next before continuing. If you leave Resume Next active, later mistakes may appear to succeed, leaving variables unset or operations incomplete.

Errors in forms and controls

A form’s Error event is different from an ordinary procedure error handler. Access calls the form event when an Access error occurs in the form context, and passes the error number in DataErr. The Response argument tells Access whether to display its standard message or let the event code handle the response. For example, setting Response = acDataErrContinue suppresses the standard Access message; do so only when the event provides an appropriate explanation or action.

This event is often useful when a user enters a duplicate value into a field with a unique index. You can respond with a plain-language message rather than relying only on the default dialog. However, it is not a catch-all for every error in every VBA procedure. Keep normal error handlers in procedures that perform database work, file access, or other operations that can fail independently.

Validation is usually better before saving than using an error message as the main validation strategy. For example, a form can check required fields and explain what is missing before attempting an update. Database constraints still matter: they protect data when records are entered through another form, a query, or code, not only through the particular screen where validation was written.

DAO errors, transactions, and cleanup

Access databases commonly use DAO for table and query operations. A DAO operation can provide more detail than a single VBA error number: after certain DAO failures, inspect the DAO workspace’s Errors collection promptly if the underlying database engine reports multiple related errors. Capture the relevant details before another database operation replaces that collection’s contents. The exact diagnostic information depends on the operation and the error.

For multiple related updates that must succeed or fail together, consider a DAO transaction where the database engine and operation support it. The general idea is to begin the transaction, perform the updates, commit if all succeed, and roll back in the handler if something fails. Transaction support and behavior depend on the data source; linked tables and external systems may have different limits. Do not assume that wrapping arbitrary Access operations in a transaction makes every change reversible.

Cleanup is part of error handling. Close recordsets and other objects when appropriate, release object variables when their lifetime ends, and roll back a transaction that is still active after a failure. Put cleanup in a predictable path, and make it safe when an object was never successfully created. Avoid performing complex operations in the cleanup section unless those operations also have a deliberate failure strategy.

Common mistakes and troubleshooting

Using the wrong label or omitting an exit: A handler section placed after normal code can run during successful execution unless the procedure exits before it. Use a normal exit path before the handler label.
Leaving Resume Next enabled: Turn it off with On Error GoTo 0 once the narrowly expected operation is checked.
Overwriting the useful error: Record Err.Number and Err.Description early, before cleanup or logging code can raise another error.
Expecting handlers to catch everything: Compile errors must be corrected, and some failures may not be safely recoverable. Test normal and failure paths.
Showing only “an error occurred”: Users need a safe next step, while developers need enough diagnostic context to find the cause. Separate user-facing guidance from technical logging.

When a handler does not run as expected, confirm that the error occurs after the On Error statement, that the label is in the same procedure, and that the code is actually executing the procedure you think it is. In the VBA editor, use Debug > Compile to find compile problems, then step through the procedure to see where execution goes. A breakpoint and the Locals or Immediate window can help inspect values during debugging.

Frequently Asked Questions

What is the best error-handling pattern in Access VBA?
For most procedures that can fail, use On Error GoTo HandlerLabel, capture the error details, perform necessary cleanup, and exit through a clear path. Use Resume Next only around a specific, expected operation and check its result immediately.

What is the difference between On Error GoTo 0 and Err.Clear?
On Error GoTo 0 disables the active error handler in the procedure. Err.Clear clears the current error information. They do different jobs; clearing Err does not by itself disable Resume Next or a handler.

Can Access VBA error handling catch syntax errors?
No. Syntax and compile errors prevent the affected code from running correctly, so a runtime handler cannot catch and repair them. Fix them in the VBA editor, using compile and debugging tools as appropriate.

How do I show a custom message for a form error?
Use the form’s Error event, examine DataErr, display a suitable message, and set Response deliberately. Set it to acDataErrContinue when your code should suppress Access’s standard message; otherwise, allow the standard response.

Can I undo VBA changes with Ctrl+Z?
No. Ctrl+Z does not roll back database changes made by a running VBA procedure. Test on a copy, and use transactions or an intentional backup and recovery plan where suitable.

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.