MS-Access Tips for Serious Users

Provided by Allen Browne, allenbrowne.com

Error Handling in Access Basic

(Note: for Access 95 or later, use the VBA error handler

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 & " " & Error$           ' Place error handling here.
8     Resume Exit_SomeName                ' Pick up again and quit.
9 End Sub|Function

The labels (lines 4 and 6) must be in the current procedure, and must be unique.

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

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

The Case Else in this example calls a custom procedure 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	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.

Below is the procedure for writing to this table. If you wish to go further, you could extend it to count errors recorded recently and suppress the display of the same message repeatedly, or give up retrying locking errors.

Sub LogError (ByVal iErrNumber As Integer, ByVal strErrDescription As String, strCallingProc As String)
On Error GoTo Err_LogError
    ' Purpose:   Generic error handler.
    '            Logs errors to table "tLogError".
    ' Arguments: iErrNumber        - value of Err
    '            strErrDescription - value of Error$
    '            strCallingProc    - name of sub|function that generated the error.
    ' Author:    Allen Browne, June 1997.

    Dim NL As String * 2                ' New Line
    Dim sMsg As String                  ' String for display in MsgBox
    Dim db As Database                  ' Current database
    Dim rst As Recordset                ' The tLogError table

    sMsg = "Error " & iErrNumber & ": " & strErrDescription
    MsgBox sMsg, 32, strCallingProc

    Set db = CurrentDB()
    Set rst = db.OpenRecordset("tLogError")
    rst.AddNew
        rst![ErrNumber] = iErrNumber
        rst![ErrDescription] = Left$(strErrDescription, 255)
        rst![ErrDate] = Now
        rst![CallingProc] = strCallingProc
        rst![UserName] = CurrentUser()
    rst.Update
    rst.Close

Exit_LogError:
    Exit Sub

Err_LogError:
    sMsg = "An unexpected situation arose in your program." & NL
    sMsg = sMsg & "Please write down the following details:" & NL & NL
    sMsg = sMsg & "Calling Proc: " & strCallingProc & NL
    sMsg = sMsg & "Error Number " & iErrNumber & NL & strErrDescription & NL & NL
    sMsg = sMsg & "Unable to record because Error " & Err & NL & Error$
    MsgBox sMsg, 16, "LogError()"
    Resume Exit_LogError
End Sub