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:
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:
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:
Similarly, GoTo is usually found in two forms:
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:
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.
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:
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:
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.