Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources]

Implementing Error Handling

Without error handling, the user of your application is forced to exit abruptly from your application code. Consider the example in Listing 16.1.

The click event behind the command button calls the routine TestError1, passing it the values from two text boxes. TestError1 accepts those parameters and attempts to divide the first parameter by the second parameter. If the second parameter is equal to 0, a runtime error occurs. Because no error handling is in effect, the program terminates.

Listing 16.1 An Example of Code Without Error Handling

Private Sub cmdNoErrorHandler_Click() 'Call TestError1, passing the values in the txtValue1 'and txtValue2 text boxes Call TestError1(Me.txtValue1.Value, Me.txtValue2.Value) End Sub Sub TestError1(Numerator As Integer, Denominator As Integer) 'Divide the value received as the first parameter 'by the value received as the second parameter Debug.Print Numerator / Denominator 'If successful, display a message to the user MsgBox "I am in Test Error" End Sub

Figure 16.1 shows the error message the user receives. As you can see, the choices are Continue, End, Debug, and Help. If users click Debug, the module window appears, and they are placed in Debug mode on the line of code causing the error. Clicking Continue (this is not always available) tells Access to ignore the error and continue with the execution of the program. End terminates execution of the programming code. If the application is running with the runtime version of Access, it shuts down, and users are returned to Windows. If users click Help, VBA Help attempts to give them some information about the error that occurred. With error handling in effect, you can attempt to handle the error in a more appropriate way whenever possible.

Figure 16.1. The default error handling message.

You can add error-handling code to the error event procedure of a form or report. You can also add it to any VBA subroutine, function, or event routine. You can easily modify the code in Listing 16.1 to handle the error gracefully. The code in Listing 16.2 shows a simple error-handling routine.

Listing 16.2 A Simple Error-Handling Routine

Sub TestError2(Numerator As Integer, Denominator As Integer) On Error GoTo TestError2_Err 'Divide the value received as the first parameter 'by the value received as the second parameter Debug.Print Numerator / Denominator 'If successful, display a message to the user MsgBox "I am in Test Error" Exit Sub TestError2_Err: 'If a divide by zero (error 11) occurs, display an 'appropriate message to the user If Err = 11 Then MsgBox "Variable 2 Cannot Be a Zero", , "Custom Error Handler" End If Exit Sub End Sub

The routine now invokes error handling. If a divide-by-zero error occurs, a message box alerts the user to the problem, as Figure 16.2 shows.

Figure 16.2. A custom error handler message.

NOTE

This code is located in the basError module, which is in the CHAP16EX.MDB database on the accompanying CD-ROM.