Error Handling and Debugging Tips and Techniques for Microsoft Access, VBA, and Visual Basic (VB6)

Professional applications need to include error handling to trap unexpected errors. By using a consistent error handler, you can make
sure that when crashes occur, the user is properly informed and your program exits gracefully. Basic error handling just hides the default
behavior and exits the program. Advanced error handling can include all sorts of features such as saving information about the cause of the
error and the environment at the time, attempts to address the problem, and information for the user on what they need to do next.

Microsoft Access RuntimeMicrosoft Access Runtime

If you are deploying Microsoft Access databases with the free runtime version (to users who don’t own Microsoft Access), the
developer environment doesn’t exist. In such situations, you need to have an error handling system in place to capture errors and
gracefully exit your program should it crash. Visit our Microsoft Access Runtime Distribution and Free Downloads page for more info.

Verify Error Handling Setting

Before you can use error handling, you need to understand the Error Trapping setting. VB6/VBA lets you to determine how it should behave
when errors are encountered. From the IDE, look under the Tools Options setting.

Setting Error trapping/handling options for VBA/VB6

Make sure error trapping is not set to “Break On All Errors”. That setting will cause your code to stop on every error, even errors you are
properly handling with “On Error Resume Next”.

“Break on Unhandled Errors” works in most cases but is problematic while debugging class modules. During development, if Error Trapping is
set to “Break on Unhandled Errors” and an error occurs in a class module, the debugger stops on the line calling the class rather than the
offending line in the class. This makes finding and fixing the problem a real pain.

I recommend using “Break in Class Modules” which stops on the actual crashing line. However, be aware that this does not work if you use
raise errors in your classes via the Err.Raise command. This command actually causes an “error” and makes your program stop if Error Trapping
is set to “Break in Class Modules”.

Unfortunately, users can modify this setting before launching your application so you should make sure this is properly set when your application starts.

Programmatically, the option settings can be viewed and modified using the Application.GetOption and Application.SetOption methods.

Function GetErrorTrappingOption() As String
Dim strSetting As String

Select Case Application.GetOption(“Error Trapping”)
Case 0
strSetting = “Break on All Errors”
Case 1
strSetting = “Break in Class Modules”
Case 2
strSetting = “Break on Unhandled Errors”
End Select
GetErrorTrappingOption = strSetting
End Function

Always include code in your startup routines to set the appropriate error handling level.

Sub SafeStart()
Application.SetOption “Error Trapping”, 1
End Sub

Make Sure Every Procedure has Error Handling

Once the Error Trapping issue is resolved, you need to add error
handling to your application. Unfortunately, VB6/VBA does not support a
global error handler to manage any errors that arise. You actually have
to set error handling in every procedure.

Without explicitly adding error handling, VB6/VBA shows its default
error message and then allows the user to debug your code or just crashes.

At the most basic level, error handling involves two parts:

Error Enabler

This section invokes the error handler:

On Error GoTo PROC_ERR

If an error occurs in the procedure, the code jumps to the line where the label “PROC_ERR” is defined.
For consistency, use the same label name in every procedure.

Error Handler

This section is where the code goes if an error occurs in the procedure:

MsgBox “Error: (” & Err.Number & “) ” & Err.Description, vbCritical

Here you can manage the error and determine what to do next. Examine
the error object (Err) to see what occurred. For instance, Err.Number is
the error number, Err.Description is the error description, etc.

Disabling Error Handling

In some situations, you need to turn off error handling. For instance,
you may want to see if a file exists. By looking for it and managing the
error if it can’t be found, you can determine whether it exists or not.

Disable error handling with:

On Error Resume Next

Turn off error handling during development and testing

Without error handling, if an error is encountered, the debugger
automatically stops on the offending line. This is great for debugging
and correcting mistakes. However, if error handling exists in the
procedure, when an error occurs, rather than stopping on the offending
line, the code in the Error Handling section is invoked. This makes debugging much more difficult.

An easy way to avoid this problem is to add a global constant or
variable that controls when error handling is active. So rather than this:

On Error GoTo PROC_ERR

use this:

If gcfHandleErrors Then On Error GoTo PROC_ERR

and define a global constant:

Public Const gcfHandleErrors As Boolean = False

Set this constant to False during development, and True when you
deliver your application. That way, your users get the benefit of the
error handling and you can get your work done without it.

Getting information from the error object

When an error occurs, information about the problem in the Error Object.
This object is named Err and contains several properties. These are the ones you should check:

  • Number
    The error number, which is useful for testing. Zero means no error.
  • Description
    The built-in description of the error. Sometimes this doesn’t exist
    and this text “Application-defined or object-defined error” is given.

The error object lets you easily inform the user of the problem. For
instance, rather than a simple message that an error occurred, you can
specify the exact error number and message:

MsgBox “Error: (” & Err.Number & “) ” & Err.Description, vbCritical

This still may not be understandable by the user, but can be very helpful in diagnosing the problem.

Click here for a list of all
Microsoft Access Error Numbers and Descriptions

Clearing the Error Object

There may be situations where you test for an error number but cannot be
sure the Err object doesn’t already contain an error. In such cases, use
the Clear method to clear the object:


Alternatively, you can set the error number to zero (Err.Number = 0),
but is not as effective as the Clear method since it does not clear the description property.

Using Error Handling for Testing

Error handling can also be used to test a condition. Here’s an example
of deleting a file and providing the user with error messages:

Sub DeleteFile(strFileName As String)
Dim lngSaveErr As Long
Dim strSaveErr As String
Const clngErrNoFile As Long = 53
Const clngErrFileInUse As Long = 75

On Error Resume Next

Kill strFileName
lngSaveErr = Err.Number
strSaveErr = Err.Description

On Error GoTo PROC_ERR

Select Case lngSaveErr
Case 0
‘ No error
Case clngErrNoFile
MsgBox “The file ” & strFileName & ” does not exist.”
Case clngErrFileInUse
MsgBox “The file ” & strFileName & ” is in use.”
Case Else
MsgBox “Unknown error: ” & strSaveErr
End Select

Exit Sub

MsgBox “Error ” & Err.Number & ” ” & Err.Description
End Sub

Notice how this syntax:

On Error Resume Next

allows the program to continue (go to the next line) even when a
command cannot be executed.

The Kill command triggers an error if the file being deleted doesn’t
exist or is locked. We don’t care whether the object exists or not. We
just want to delete it if it does. Therefore, the command to ignore the
error (Resume Next) is appropriate.

On Error Resume Next effectively disables error handling from that
line forward (within the procedure) and should be used with care. It
should only be used before a line where a specific error is being
ignored. To reset error handling, use this line:

On Error GoTo 0

Or, your standard error handler with an extra clause to handle
situations where error handling is not being used:

If gcfHandleErrors Then
On Error GoTo PROC_ERR
On Error GoTo 0
End If

Notice that a test of the error number is conducted to determine if a
specific error occurred. That is, we consider it okay if the object
could not be found. However, there are other reasons that may cause a
failure to delete an object that exists (for instance another user has
the object open, insufficient rights to delete it, etc.).

What Error Handling Cannot Trap

Error handling only handles well-behaved errors: errors that trigger an
error number in code. Technically, these are the only types of errors
you can have, but we all know that Access can crash with a GPF.
Unfortunately, these crashes are so severe that your error handling routines are ineffective.


Leave a Reply

Your email address will not be published. Required fields are marked *