VBA Error Handling in Access: Write Bulletproof Code

Nothing destroys user confidence in a database application faster than an unhandled runtime error. The cryptic "Error 3075: Syntax error in query expression" dialog, followed by a crash, is the kind of experience that makes users distrust the entire system. Proper error handling prevents this — and it is not as complicated as it looks.

Why Error Handling Matters

Without error handling, any runtime error in your VBA code causes Access to display a generic error dialog and halt execution. This is bad for several reasons:

• Users see confusing technical messages
• Partially completed operations leave data in an inconsistent state
• You have no record of what went wrong or when
• The application appears unreliable

With error handling, you control what happens when something goes wrong: show a friendly message, log the error, clean up resources, and continue gracefully.

The Basic Pattern: On Error GoTo

The standard VBA error handling pattern uses On Error GoTo to redirect execution to an error handler when an error occurs:

Public Sub SaveCustomer()
    On Error GoTo ErrorHandler
    
    ' Your normal code here
    Dim db As Database
    Set db = CurrentDb
    db.Execute "UPDATE Customers SET LastModified = Now() WHERE CustomerID = " & Me.CustomerID
    
    MsgBox "Customer saved successfully."
    Exit Sub  ' Important: exit before the error handler

ErrorHandler:
    MsgBox "An error occurred while saving: " & Err.Description, vbCritical, "Save Error"
    ' Clean up resources if needed
    If Not db Is Nothing Then Set db = Nothing
End Sub

The key elements:

1. On Error GoTo ErrorHandler — at the top of every procedure
2. Exit Sub (or Exit Function) — before the error handler label, so normal execution does not fall into it
3. ErrorHandler: — the label that marks the start of your error handling code
4. Err.Description — the human-readable error message
5. Err.Number — the numeric error code

The Err Object

When an error occurs, VBA populates the Err object with information about the error:

• Err.Number — the error number (e.g., 3075, 2501)
• Err.Description — a text description of the error
• Err.Source — the object or application that generated the error

You can use Err.Number to handle specific errors differently:

ErrorHandler:
    Select Case Err.Number
        Case 3022  ' Duplicate key violation
            MsgBox "A customer with this ID already exists.", vbExclamation
        Case 3058  ' Index or primary key violation
            MsgBox "This record violates a uniqueness constraint.", vbExclamation
        Case 2501  ' Action was cancelled
            ' User cancelled - not really an error, do nothing
        Case Else
            MsgBox "Unexpected error " & Err.Number & ": " & Err.Description, vbCritical
    End Select

On Error Resume Next

On Error Resume Next tells VBA to ignore errors and continue with the next line. This sounds dangerous — and it can be — but it is appropriate in specific situations:

' Check if a file exists without crashing
On Error Resume Next
Dim fileNum As Integer
fileNum = FreeFile
Open "C:\Reports\output.pdf" For Input As #fileNum
If Err.Number <> 0 Then
    MsgBox "File not found."
    Err.Clear
End If
Close #fileNum
On Error GoTo 0  ' Restore normal error handling

Always restore normal error handling with On Error GoTo 0 after using Resume Next. And always check Err.Number immediately after the potentially failing line — do not let errors accumulate silently.

Error Logging

For production databases, logging errors to a table is invaluable for diagnosing problems after the fact:

Public Sub LogError(errNumber As Long, errDescription As String, procedureName As String)
    On Error Resume Next  ' Don't let the logging itself crash
    
    Dim db As Database
    Set db = CurrentDb
    
    Dim sql As String
    sql = "INSERT INTO ErrorLog (ErrorNumber, ErrorDescription, ProcedureName, ErrorDate, UserName) " & _
          "VALUES (" & errNumber & ", '" & Replace(errDescription, "'", "''") & "', " & _
          "'" & procedureName & "', #" & Now() & "#, '" & CurrentUser() & "')"
    
    db.Execute sql
    Set db = Nothing
End Sub

Create an ErrorLog table with fields: ErrorLogID (AutoNumber), ErrorNumber (Long), ErrorDescription (Text), ProcedureName (Text), ErrorDate (Date/Time), UserName (Text).

Then call it from your error handlers:

ErrorHandler:
    LogError Err.Number, Err.Description, "SaveCustomer"
    MsgBox "An error occurred. The error has been logged.", vbCritical

Transactions and Error Handling

When your code performs multiple database operations that must all succeed or all fail together, use transactions:

Public Sub TransferInventory(fromID As Long, toID As Long, qty As Long)
    On Error GoTo ErrorHandler
    
    Dim ws As Workspace
    Set ws = DBEngine.Workspaces(0)
    ws.BeginTrans
    
    CurrentDb.Execute "UPDATE Inventory SET Quantity = Quantity - " & qty & " WHERE ItemID = " & fromID
    CurrentDb.Execute "UPDATE Inventory SET Quantity = Quantity + " & qty & " WHERE ItemID = " & toID
    
    ws.CommitTrans
    Exit Sub

ErrorHandler:
    ws.Rollback  ' Undo all changes if anything failed
    MsgBox "Transfer failed: " & Err.Description, vbCritical
End Sub

The Rollback in the error handler ensures that if the second UPDATE fails, the first UPDATE is also undone — leaving the data in a consistent state.

The Resume Statement

After handling an error, you have three options for where execution continues:

• Resume — retry the line that caused the error (use carefully — can cause infinite loops)
• Resume Next — continue with the line after the one that caused the error
• Resume ExitPoint — jump to a specific label

ErrorHandler:
    If Err.Number = 3021 Then  ' No current record
        Resume Next  ' Skip this record and continue
    Else
        MsgBox Err.Description
        Resume ExitPoint
    End If

ExitPoint:
    ' Cleanup code here
End Sub

A Complete Error Handling Template

Here is a reusable template for any VBA procedure:

Public Sub ProcedureName()
    On Error GoTo ErrorHandler
    
    ' === Your code here ===
    
    Exit Sub

ErrorHandler:
    Dim errNum As Long
    Dim errDesc As String
    errNum = Err.Number
    errDesc = Err.Description
    
    LogError errNum, errDesc, "ProcedureName"
    MsgBox "Error " & errNum & ": " & errDesc, vbCritical, "Error"
    
    ' Resume Next or Exit Sub depending on context
End Sub

Conclusion

Error handling is not optional in production Access applications — it is a professional requirement. The On Error GoTo pattern is simple to implement and dramatically improves the reliability and user experience of your database. Add error logging and you gain visibility into problems you would otherwise never know about. Make error handling a habit from the start of every procedure you write.