Error Handling in VBA

        11 votes: *****     35,355 views      No comments
by Allen Browne, 20 April 2005    (for Access 95+)

Microsoft Access Tips for Serious Users

Provided by Allen Browne,, June 1997. Updated October 2004.

Error Handling in VBA

Every function or sub should contain error handling. Without it, a user may be left viewing the faulty code in a full version of Access, while a run-time version just crashes.

The simplest approach is to display the Access error message and quit the procedure. Each procedure, then, will have this format (without the line numbers):

1 Sub|Function SomeName()
2     On Error GoTo Err_SomeName          ' Initialize error handling.
3     ' Code to do something here.
4 Exit_SomeName:                          ' Label to resume after error.
5     Exit Sub|Function                   ' Exit before error handler.
6 Err_SomeName:                           ' Label to jump to on error.
7     MsgBox Err.Number & Err.Description ' Place error handling here.
8     Resume Exit_SomeName                ' Pick up again and quit.
9 End Sub|Function

For a task where several things could go wrong, lines 7~8 will be replaced with more detail:

      Select Case Err.Number
      Case 9999                        ' Whatever number you anticipate.
          Resume Next                  ' Use this to just ignore the line.
      Case 999
          Resume Exit_SomeName         ' Use this to give up on the proc.
      Case Else                        ' Any unexpected error.
          Call LogError(Err.Number, Err.Description, "SomeName()")
          Resume Exit_SomeName
      End Select

The Case Else in this example calls a custom function to write the error details to a table. This allows you to review the details after the error has been cleared. The table might be named "tLogError" and consist of:

Field Name Data Type Description
ErrorLogID AutoNumber Primary Key.
ErrNumber Number Long Integer. The Access-generated error number.
ErrDescription Text Size=255. The Access-generated error message.
ErrDate Date/Time System Date and Time of error. Default: =Now()
CallingProc Text Name of procedure that called LogError()
UserName Text Name of User.
ShowUser Yes/No Whether error data was displayed in MsgBox
Parameters Text 255. Optional. Any parameters you wish to record.


Below is a procedure for writing to this table. It optionally allows recording the value of any variables/parameters at the time the error occurred. You can also opt to suppress the display of information about the error.

Function LogError(ByVal lngErrNumber As Long, ByVal strErrDescription As String, _
    strCallingProc As String, Optional vParameters, Optional bShowUser As Boolean = True) As Boolean
On Error GoTo Err_LogError
    ' Purpose: Generic error handler.
    ' Logs errors to table "tLogError".
    ' Arguments: lngErrNumber - value of Err.Number
    ' strErrDescription - value of Err.Description
    ' strCallingProc - name of sub|function that generated the error.
    ' vParameters - optional string: List of parameters to record.
    ' bShowUser - optional boolean: If False, suppresses display.
    ' Author: Allen Browne

    Dim strMsg As String      ' String for display in MsgBox
    Dim rst As DAO.Recordset  ' The tLogError table

    Select Case lngErrNumber
    Case 0
        Debug.Print strCallingProc & " called error 0."
    Case 2501                ' Cancelled
        'Do nothing.
    Case 3314, 2101, 2115    ' Can't save.
        If bShowUser Then
            strMsg = "Record cannot be saved at this time." & vbCrLf & _
                "Complete the entry, or press <Esc> to undo."
            MsgBox strMsg, vbExclamation, strCallingProc
        End If
    Case Else
        If bShowUser Then
            strMsg = "Error " & lngErrNumber & ": " & strErrDescription
            MsgBox strMsg, vbExclamation, strCallingProc
        End If
        Set rst = CurrentDb.OpenRecordset("tLogError", , dbAppendOnly)
            rst![ErrNumber] = lngErrNumber
            rst![ErrDescription] = Left$(strErrDescription, 255)
            rst![ErrDate] = Now()
            rst![CallingProc] = strCallingProc
            rst![UserName] = CurrentUser()
            rst![ShowUser] = bShowUser
            If Not IsMissing(vParameters) Then
                rst![Parameters] = Left(vParameters, 255)
            End If
        LogError = True
    End Select

    Set rst = Nothing
    Exit Function

    strMsg = "An unexpected situation arose in your program." & vbCrLf & _
        "Please write down the following details:" & vbCrLf & vbCrLf & _
        "Calling Proc: " & strCallingProc & vbCrLf & _
        "Error Number " & lngErrNumber & vbCrLf & strErrDescription & vbCrLf & vbCrLf & _
        "Unable to record because Error " & Err.Number & vbCrLf & Err.Description
    MsgBox strMsg, vbCritical, "LogError()"
    Resume Exit_LogError
End Function

Notes on this function:

  1. For Access 1 or 2, use the Access Basic error handler.
  2. The Return Value serves only to indicate if the function succeeded in logging the error.
  3. Possible Extensions: Since you have tErrorLog open, you could count errors recorded recently and suppress the display of the same message repeatedly, or give up retrying locking errors.

Home Index of tips Top

Rate this article:  Your rating: PoorYour rating: Not so goodYour rating: AverageYour rating: GoodYour rating: Excellent

This is a cached tutorial, reproduced with permission.

Have your say - comment on this article.

What did you think of 'Error Handling in VBA'?

No comments yet.

Why not be the first to comment on this article?!

Have your say...

E-mail (e-mail address will be kept private)

Comments require approval before being displayed on this page (allow 24 hours).