databasedev.co.uk - database solutions and downloads for microsoft access

Microsoft Access VBA

Error Handling In Microsoft Access

Error handling routines are very powerful tools in Microsoft Access if they are used correctly. Surprisingly though, errors and their properties are relatively unknown to a large part of the development community. To effectively implement error handling in your applications, it is necessary to know the resources that are available when your program encounters an error.

Assuming we are working with non-executable programs (.mdb's or .accdb's), the most important thing to understand is the global settings for error trapping in our VBA procedure. Under the "Tools » Options" menu, then on the "General" Tab there are three options for error trapping:

  • Break on all errors » Stops code execution when an error is encountered.
  • Break in class module » Stops code execution in class modules only.
  • Break on unhandled errors » Stops code execution only if there is no error handling routine present in the procedure.

This is the global setting for error handling. So, if error traps are present in our procedures, they will be ignored if we have specified the "Break on all errors" option. In the following image, we've done just that:

Error Trapping Options

There are three important statements in an error handling routine: On Error, Resume and GoTo. Typically, they will appear together at the beginning of a procedure:

On Error Resume Next
On Error GoTo 0

The "Resume" and "GoTo" statements simply tell VBA what to do when an error is encountered. First, Resume has two primary actions associated with it:

  • Resume Next » Ignores the encountered error and continues execution with the next line of code.
  • Resume (label) » After an error occurs, code execution resumes on the first line of code after the labeled line.

Similarly, GoTo is usually found in two forms:

  • GoTo 0 » Terminates the procedure's error handler. In other words, this statement disables it.
  • GoTo (label) » Identical to the Resume statement. Execution jumps to the first line after the labeled line.

So which is best to use?

It all depends on what the error possibilities are. But there a few other things to know before deciding what tools to use. For traps to be effective, you should also understand the purpose of the following: active error handler, the err object and its properties. Let's look at each individually:

Active Error Handlers

One handler is initialized and enabled as soon as the "On Error" statement is encountered. Error handling routines only work if the current handler is enabled. Two situations disable a handler - the end of a procedure or a "GoTo 0" statement.

The Err Object

This is actually quite complex, but for the purposes of this article, we will assume that the Err object only deals with the current error in a procedure. "Current Error" simply means the last one that was encountered in the current procedure. The most commonly used properties of this object are:

  • Err.Number
  • Err.Description » A description of the error that occurred.
  • Err.Clear » Clears all traces of previous errors. Also resets the "number" property to 0.

This is very useful when you need to check the type of error that occurs, or if you anticipate specific errors based on user activity. You can use the Err object inside conditional statements as well, which are generally used to force users to make appropriate changes. Here is a small example that evaluates user input and forces the user to enter correct information:

Sub InputInfo()

On Error GoTo ErrorHandler

Dim strTemp As String

Repeat:
   strTemp = InputBox("Enter a table name.", "Input")
      CurrentDb.TableDefs.Delete (strTemp)

ExitHandler:
   Exit Sub

ErrorHandler:
   MsgBox "Oops!  An error occurred:" & vbCrLf & _
          "Error " & Err.Number & ": " & Err.Description
      GoTo Repeat

End Sub

If we enter an incorrect table name, we will see this dialog popup:

Error trapping message

Notice that there is a small exit routine before the actual error handler. This is very common, as most handlers are included at the end of a sub or function. More sophisticate handling will include conditional statements that evaluate user activity. In the above example, we can expand a little bit to include this effect:

ErrorHandler:
   If Err.Number = 3265 Then
      MsgBox "Table does not exist!  Please re-enter."
         GoTo Repeat
   Else
      MsgBox "An error occurred:" & vbCrLf & _
             "Error " & Err.Number & ": " & Err.Description
         GoTo Repeat
   End If

There are many other resources available for handling routines, which can be easily found by exploring the VBA intellisense dropdown menu for the Err object.

The Author

Adam Evanovich lives in Iowa in the United States and frequently works on contract in various industries. He started using Access in 1997 to record notes in a small database for a marketing program. Since then he has continued to explore the models that are available in Access and often uses them to streamline various small business operations.

Adam's database skills also include MySQL, Oracle, and SQL Server implementations. He also enjoys spending some of his free time understanding the history of technology. In his off hours he enjoys exercising, spending time friends and family and exploring the mountains and forestry.

If you'd like to contact Adam, you can reach him through his email address: the.net.2.0.0.0@gmail.com