Debugging and Error Trapping

If you write code, your code will contain errors. Aside from the normal variety of syntax errors and faulty logic in coding solutions, you can often get run-time errors for code that you think will work correctly. These errors can result from someone failing to enter a value in a text box or from the entry of an out-of-bound value in a text box. With trial deployments, you can solve many of these issues, but users can still enter an unanticipated value or click a button at the wrong time.

Error trapping is the process of catching run-time errors in your code before they cause abnormal termination of your application. Error trapping puts you in control. Your code can examine the error and sometimes even fix the problem without any action on the part of the user. If your code determines that user intervention is necessary, you can display an explanatory message. You can even gather new input in a set of custom dialog boxes and then proceed with the application. Even if you cannot gracefully recover from an error, you can terminate the program under prescribed conditions. Your solution can automatically save information about where the application failed as well as the error code, its description, and any other pertinent information. Then you can instruct the user to report the error to the proper person.

Because Access permits developers to respond to run-time errors, you should consider raising your own custom errors to trap special application logic errors, such as the entry of an out-of-bounds field value. In fact, Access lets you generate custom errors in a variety of ways. Your code can then recover from these custom errors just as it does from Access errors. You'll probably have a better idea of how to respond to your custom errors since you created them in the first place.

Essential Syntax for Error Trapping

You need to understand the On Error statement, the Err object, and the Resume statement to implement error trapping. They work together to enable error trapping and help you process errors after you trap them.

There are three versions of the On Error statement, but you are most likely to use this one, which enables error trapping:

 On Error GoTo linelabel 

When Access detects an error, this line allows your program to take control so that Access does not perform its default error handling. The linelabel argument is the name of the line to which you want to transfer control. Your code, starting with this line, is executed in place of the standard Access error processing code. Your code should determine and decipher the error number so that your program can take appropriate action. If you cannot fix the problem, you can at least get some information about the error before your code ends gracefully.

The Err object tells you about the error. It has two properties. The Number property precisely defines the error. The numbers stored in the Number property do not change from one version of Access to the next. This means that you can use the property as a robust error identifier. The other clue about the error is the Err object's Description property, which contains a textual description of the error. You should display these descriptions in the Immediate window or a message box.

When you initially design a program, the Err object descriptions can help you improve your design and develop appropriate responses to normal run-time errors, such as an out-of-bounds input value. An operational version of your program can also save descriptions of errors to a log file. This can help you design fixes expeditiously over the lifetime of the application. However, you typically will not want to detect errors by their descriptions, which are longer than their error numbers. (Microsoft might also reword descriptions even though the error numbers remain the same.)

The Resume statement is used to indicate where to resume running an application after the error. Use Resume without an argument if you want control to return to the same statement that caused the error. This approach can make sense when you fixed the problem that initially generated the error, such as a missing field value. If your error processing determines that it cannot fix the error, you might still want to continue at the line after the one that caused the error. Use the Resume Next statement for this. As long as the error does not affect the validity of these remaining code lines, this approach is at least possible. You will sometimes want to simply abort an application when your application uncovers an error that it cannot fix. In this case, transfer control after processing the error with the Resume linelabel statement. When you do error trapping, you will frequently include an Exit Function or Exit Sub statement with a label. It is convenient to use this label as the argument for the Resume linelabel statement when you want to exit the procedure after an error.

Error Trapping Samples

This section presents two error trapping samples. The first one is a basic application of error trapping logic. It shows error trapping for a function procedure in the Module folder. It also shows how to raise custom error codes. This sample shows how easy it is to process your application's internal errors with the same logic that you use for trapping Access errors. The second example uses error-trapping logic in the code behind a form. A distinguishing feature of this application is that it responds differently to each of two classes of errors.

The following function procedure builds error trapping into the more basic example shown earlier in Figure 1-16. Recall that this routine performs one of four arithmetic operations. You pass the procedure three arguments: two operands and a string that specifies what arithmetic operation to perform on the numbers you pass the function.

At least two errors can crop up in this code at run time. First, a user can attempt to divide a number by 0. This generates error number 11. Second, the operation can generate a result larger or smaller than the data type permits. Access calls this an overflow error; its error number is 6. This procedure's error trapping logic catches these two errors explicitly, and it implicitly sets a trap for every other kind of error. The error handler helps the user by suggesting a fix for division by 0, and it explains what an overflow is in friendlier language than Access uses. Since you will often write applications for an audience that you know, your application can address that audience more directly than a general program like Access can.

 Public Function Computer2(dblNumber1, dblNumber2, _     Operation As String) On Error GoTo Computer2Handler        Select Case Operation         Case "Addition"             Computer2 = dblNumber1 + dblNumber2         Case "Subtraction"             Computer2 = dblNumber1 - dblNumber2         Case "Multiplication"             Computer2 = dblNumber1 * dblNumber2         Case "Division"             Computer2 = dblNumber1 / dblNumber2     End Select    Computer2Exit:     Exit Function Computer2Handler:     If Err.Number = 11 Then         MsgBox "Can't divide by zero. Change second number from 0.", _         vbInformation, "Programming Microsoft Access 2000"     ElseIf Err.Number = 6 Then         MsgBox "Result exceeds data type value limits.", _             vbInformation, "Programming Microsoft Access 2000"     Else         MsgBox Err.Number & ": " & Err.Description, vbInformation, _             "Programming Microsoft Access 2000"     End If     Resume Computer2Exit End Function 

The On Error statement appears immediately after the start of the program. You want it there so that it can trap errors as soon as possible. If another program calls this one, it might make sense to use error trapping in that program so that it can use error trapping for its arguments to the Computer2 function.

If you type ?Computer2(2, 4, "multiplication") in the Immediate window and press Enter, the function returns 8. Change multiplication to division, and the return value is .5. Next, enter 0 for the second argument. Without error trapping, this causes your program to end abruptly with a system message. With error handling, control passes to Computer2Handler as soon as the function attempts to divide by 0. The routine checks for error 11, and because error 11 occurred, it prints a message encouraging the user to enter a number other than 0 for the divisor. Type ?Computer2(1.79E308, 4, "multiplication") and press Enter to generate an overflow error. The multiplication operation attempts to generate a result greater than Access can represent. Control again passes to Computer2Handler immediately after the attempted multiplication fails. After determining that the error code is not 11, the error trapping logic tests the error code against 6. This matches the error code and the function presents a message box explaining the source of the error.

The error handler section of the routine uses an If…Then…ElseIf…Else statement. The Else clause offers a way to catch errors that your code does not explicitly trap. When you initiate your application, you might not know what the likely errors are. In this case, just use an If…Then…Else statement without any ElseIf clauses. Your Else clause presents error numbers along with their descriptions as they occur during testing. You can then use this information to develop explicit traps for certain kinds of errors along with any remedies that are available. As you test your application, it might offer additional opportunities to enhance your collection of ElseIf clauses in the error handler.

The final line in the error handler section is a Resume statement. This Resume statement transfers control to a line that exits the procedure. You can also use separate Resume statements for each type of error trap. This approach makes sense when your application requires you to take different actions after each kind of error trap.

The error handler will not trap some types of errors. For example, it will not trap an error if a user misspells an operation—as in multiplcation instead of multiplication. An operational error of this sort passes through the Select Case statement without stopping to perform any arithmetic, and the program logic then encounters the Exit Function. You need a way to trap this error and tell the user what to do. The solution is to detect the problem with a Case Else clause in the Select Case statement. Then you raise a custom error that tells the user what to do. Because the error trapping logic is robust, you need only alter the Select Case statement. The following excerpt from the modified code shows the new version with just two additional lines of code:

 Select Case Operation     Case "Addition"         Computer2 = dblNumber1 + dblNumber2     Case "Subtraction"         Computer2 = dblNumber1 - dblNumber2     Case "Multiplication"         Computer2 = dblNumber1 * dblNumber2     Case "Division"         Computer2 = dblNumber1 / dblNumber2     Case Else         Err.Raise 1, , "Wrong operation." End Select 

The line after Case Else shows the syntax for raising a custom error code. It turns out that error number 1 does not receive assignment from either VBA, DAO, or ADO. Therefore, our application can use the number 1 for a custom error code. When the Case Else clause catches the misspelled operation, it raises the custom error, and this passes control to the Computer2Handler statement. The Else clause in that block of statements detects error code 1, and it issues a message box with the error message and our custom error description, "Wrong operation." After reviewing how to do error trapping with a form, we will return to the topic of which custom error codes to use.

The code behind the form in Figures 1-18 through 1-20 uses nested For Each…Next statements to process responses to a form. Recall that the procedure for the command button's click event looped through all the controls on the form to find the two text boxes. It did this to avoid an error that would occur if it applied the IsNull function to a control, such as a label or a command button, which did not support this function.

An alternative design is to let the error happen, trap it, and recover from the problem. Using the IsNull function with an inappropriate control generates error number 438. The new version of the cmdSubmit_Click event procedure appears below.

 Private Sub cmdSubmit_Click() On Error GoTo SubmitErrorTrap 'Check for valid entries.     For Each ctl In Screen.ActiveForm.Controls         If IsNull(ctl.Value) Then             MsgBox "Please enter information " _                 & "in both input boxes.", _                 vbInformation, _                 "Programming Microsoft Access 2000"             MarkFieldsToEdit             Exit For         End If SubmitNextCtl:     Next ctl    SubmitExit:     Exit Sub    SubmitErrorTrap:     If Err.Number = 438 Then         Resume SubmitNextCtl     Else         MsgBox Err.Number & ": " & Err.Description, vbInformation, _             " Programming Microsoft Access 2000"         Resume SubmitExit     End If End Sub 

Notice that the error trapping logic actually lengthens the code, although it does allow the removal of one of the two For Each…Next statements. The new error trapping logic is considerably more robust. The nested For loops avoid just one type of error—number 438. This new alternative can potentially process any kind of error that occurs. In addition, it has two different ways of responding to errors. Notice that error 438 results in control passing back to the Next statement in the remaining For loop. This enables the program to keep processing additional controls for which the IsNull function is appropriate. Any other type of error causes the event procedure to terminate after it displays the unexpected error number and description in a message box.

Raising Errors

The Err object's Raise method is robust, but it requires that error handling be enabled. Adding error-handling routines can lengthen your code. If you have a small procedure with little opportunity for errors and you are an inexperienced developer, you might want to use a simpler way of returning an error code.

The following procedure does this with the CVErr function. This method is not as robust as a full error handling approach because it traps just a single type of error. Because there is no error handler, your whole application can crash if a run-time error does occur. On the other hand, the CVErr function is quick and easy to use. It cannot result in conflicts with built-in error codes because it does not return error codes via the same route. You must weigh for yourself the merit of the CVErr function in cases that require an error code return.

 Public Function Computer1(dblNumber1, dblNumber2, _     Operation As String)     Select Case Operation         Case "Addition"             Computer1 = dblNumber1 + dblNumber2         Case "Subtraction"             Computer1 = dblNumber1 - dblNumber2         Case "Multiplication"             Computer1 = dblNumber1 * dblNumber2         Case "Division"             Computer1 = dblNumber1 / dblNumber2         Case Else             Computer1 = CVErr(2002)     End Select End Function 

This function traps the error of a misspelled or unsupported operation. The Select Case statement isolates these problems with its Case Else clause. When a user types in a faulty operation, the function returns a Variant data type of subtype Error containing error number 2002.

This version of the procedure is much leaner than the Computer2 procedure shown earlier in this chapter. However, Computer2 explicitly traps for division by 0 and overflow even while it returns other kinds of errors. The much shorter Computer1 catches the single problem of the misspelled or unsupported operation.

While the CVErr function does not have to worry about error number conflicts with VBA and Access, the following pair of procedures dramatically simplifies the task of finding error codes that are free for your custom use. The first function, VBADAOUsedErrorList, lists the error codes in a range of numbers that are used by VBA and DAO. You specify the starting and ending numbers when you call the procedure. The numbers missing from the output are available for use as custom error codes. The second procedure also lets you specify a starting number, and an ending number for the range over which to search for error codes. This procedure, however, prints to the Immediate window the error numbers not reserved by either VBA or DAO. Many of the unique ADO error numbers are large negative numbers.

 Sub VBADAOUsedErrorList(intStart, intEnd) Dim intErrorCode As Long, strAccessError As String     For intErrorCode = intStart To intEnd         strAccessError = AccessError(intErrorCode)         If strAccessError <> "" Then             If strAccessError <> _                 "Application-defined or object-defined error" Then                 Debug.Print intErrorCode, strAccessError             End If         End If     Next intErrorCode End Sub Sub VBADAOUnUsedErrorList(intStart, intEnd) Dim intErrorCode As Long, strAccessError As String     For intErrorCode = intStart To intEnd         strAccessError = AccessError(intErrorCode)         If strAccessError = "" Or strAccessError = _             "Application-defined or object-defined error" Then             Debug.Print intErrorCode, strAccessError         End If     Next intErrorCode End Sub