Microsoft Access Tips for Serious Users

Provided by Allen Browne, June 1997. Updated September 2009.

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. For a more detailed approach to error handling, see FMS' article on Error Handling and Debugging.

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, allen@allenbrowne.com

    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.AddNew
            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
        rst.Update
        rst.Close
        LogError = True
    End Select

Exit_LogError:
    Set rst = Nothing
    Exit Function

Err_LogError:
    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.