Trapping Errors in Macros

Access 2007 is the first Access version to support trapping and handling errors within macros. During the normal process of running your application, Access can (and most likely will) encounter errors. Access might encounter errors in your code that it cannot resolve-such as a syntax error in a predicate used to filter a form. In those cases, Access cannot proceed further. Other errors might occur that are not quite so catastrophic but happen in the normal processing of your application. For example, you might use the OnNoData event of a report to display a message box saying no records were found. If your code then cancels the report from opening, Access returns an error if a subsequent action attempts to reference the report that didn’t open. If there’s no error trap in the macro, Access displays an ugly and confusing dialog box to the user.

To see how error trapping works in Access 2007, open the ErrorTrapExample macro in Design view. We created this simple macro specifically to show you two things-how Access handles an unexpected error with no error trapping and how you can trap and respond to an error. In Figure 18–20, you can see the macro names, conditions, actions, and arguments for this example macro.

Figure 18–20: The ErrorTrapExample macro demonstrates error handling in Access 2007.

In the first line of the macro, we call the MsgBox function in the Condition column to ask whether you want to use error trapping. (You can learn more about the settings in the MsgBox function in Table 18–4 on page 932.) If you click the Yes button in the dialog box displayed by MsgBox, the function returns the value 6. So when you click Yes, the condition is True, and the RunMacro action calls the TrapYes macro. If you click No, the condition is False, so the macro executes the second RunMacro that calls the TrapNo macro.

Table 18–4: Option Settings for the MsgBox Function
Open table as spreadsheet

Value	Meaning
Button Settings (Choose One)
0	OK button only
1	OK and Cancel buttons
2	Abort, Retry, and Ignore buttons
3	Yes, No, and Cancel buttons
4	Yes and No buttons
5	Retry and Cancel buttons
Icon Settings (Choose One)
0	No icon
16	Critical (red X) icon
32	Warning query (question mark) icon
48	Warning message (exclamation point) icon
64	Information message (letter i) icon
Default Button Settings (Choose One)
0	First button is the default
256	Second button is the default
512	Third button is the default

The first action in the TrapYes macro uses the new OnError macro action to tell Access how it should proceed if any error occurs. The OnError action has two arguments-Go To and Macro Name. The options in the Go To argument are Next, Macro Name, and Fail. If you select Next in the Go To argument, Access does not halt the macro when an error occurs-it simply goes on to the next action. If you select Macro Name in the Go To argument, Access runs the macro you specify in the Macro Name argument. If you select Fail, you’re basically turning error trapping off.

In all cases, Access records the error number and error description information in the MacroError object. If you have trapped the error by specifying Macro Name or Next, you can examine the error in a Condition statement to determine what action, if any, to take. For simple errors (such as an OpenReport that might be canceled), you can choose Next and check to see whether an error has occurred in the Condition line on the next action. For more complex errors, you should go to another macro that can test for several potential errors that you plan to handle. In this example, we tell Access to run the Trapped macro if any errors occur.

Note 

If you specify a macro name in the Go To argument of the OnError action, the macro must exist in the same macro object. You cannot reference a macro in a different macro object when using the OnError action.

The next line in the TrapYes macro uses the SetTempVar action to create a temporary variable named Gorp and set it to an invalid mathematical expression of 1/0-dividing by zero will cause an error. Because we asked Access to trap any error, Access runs the Trapped macro when the error occurs. Although we could have examined the error and perhaps taken some other action, for this simple example we used another MsgBox action to tell Access to display a message containing the error number and description of the error. Click this MsgBox action, and notice the following text in the Message argument:

 ="Error Trapped: " & [MacroError].[Number] & ", " & [MacroError].[Description]

All errors in Access have both a unique error number and a description. When an error occurs in a macro, the Number property of the MacroError object contains the error number, and the Description property of the MacroError object contains text describing the error associated with the number. The Message argument of the MsgBox action asks Access to fetch the Number and Description properties and display them in the message.

Finally, the TrapNo macro executes the assignment of an invalid value to a temporary variable without first setting an error trap. To see how this process works, click the Run button in the Tools group on the Design tab. Because the first action contains a call to the MsgBox function in the Condition column, Access displays the message box shown in Figure 18–21 asking whether you want to trap the error as part of evaluating the condition.

Figure 18–21: When you run the ErrorTrapExample macro, it first asks you whether you want to trap the error.

Click No to see what happens when the error isn’t trapped. First, Access displays a message box telling you the nature of the error, as shown in Figure 18–22.

Figure 18–22: Access cannot divide a number by zero, so it displays an application error message.

Click OK in this message box, and then Access displays the Action Failed dialog box, as shown in Figure 18–23. Not very user friendly, is it? Access displays the Action Failed dialog box whenever it encounters an unhandled error while running a macro. Access displays the specifics of where the error occurred in the Macro Name, Condition, Action Name, and Arguments boxes. Access displays the error number currently stored in the MacroError object in the Error Number box. The 2950 error number indicates that Access encountered an application error and had to display an application error message. As you’ll see when we trap the error, this isn’t the error number associated with the Divide By Zero error!

Figure 18–23: Access displays an Action Failed dialog box if it encounters an unhandled error.

The only button you can click in this dialog box is the Stop All Macros button. When you click this button, Access stops running the macro so that you can continue working in your application. You can imagine the support calls you’re going to get from your users if this dialog box appears often in your applications. Click the Stop All Macros button to close the Action Failed dialog box.

Now let’s see what happens when the macro traps the error. Run the macro again, and click Yes when the code asks you whether you want to trap the error. This runs the TrapYes macro (shown earlier in Figure 18–20), which executes OnError followed by the SetTempVar that generates an error. Access traps the error and executes the Trapped macro as requested. That macro asks Access to display another message box with the error number and description, as shown in Figure 18–24. Notice that Access displays the correct error number (11) and error description (Division By Zero) in the message text.

Figure 18–24: By trapping an error in a macro, you can display a helpful message to the user.

In a completed application, you probably would not need to display the error details to the user, but for debugging your application, the information in the MacroError object can be very useful. For an end user, it could be more informative to display a message such as “While attempting to calculate a value, the application divided a number by zero. Please recheck the numbers you entered before proceeding.”

Click OK in the message box, and notice what happens when you do trap the error- nothing! Because we trapped the error, Access does not display the confusing Action Failed dialog box.

Earlier in this chapter you saw the DoReport macro that is used with the PrintOptions form. This macro also uses error trapping to handle the possibility that a report might not contain any records. Close the ErrorTrapExample macro, and then open the DoReport macro in Design view. In the Groups, Alpha, Accepted, and PrintF macros, you can see that we used the OnError action just before each OpenReport action. Scroll down until you can see the PrintF macro, as shown in Figure 18–25.

Figure 18–25: The DoReport macro uses the OnError action to handle the possibility that no records are returned in the report.

The first line of the macro turns the mouse pointer into an hourglass. The second line calls the Cancel macro that closes the PrintOptions form and puts the focus back on the WeddingList form. The third line sets the error trap. We selected Macro Name in the Go To argument and ErrReport in the Macro Name argument to tell Access to go to the ErrReport macro if any errors occur. The fourth line attempts to open the report.

In each of the four reports in this sample database, the On No Data event property specifies the NoRecords macro. When the report has no records, this macro executes the CancelEvent action to prevent the report from opening. If the report opening is canceled, Access encounters an error on the next line of our macro-SelectObject. Access cannot put the focus on a report that isn’t opened, so we need to plan for this possibility. Because we’re trapping all errors, the user won’t see the ugly Action Failed dialog box. Instead, the ErrReport macro runs, and this macro restores the mouse pointer and displays an informative message telling the user that the report requested has no records.

To test how this works, close the DoReport macro, and then open the WeddingList form in Form view. Click the Print button on this form to open the PrintOptions form.

On the PrintOptions form, select List Invitees Who Have Accepted. Unless you have changed the sample data, this report should return no records. Click the Print button to run the PrintIt macro in the DoReport macro group. This macro looks at the option you chose on the PrintOptions form and runs the Accepted macro. That macro attempts to open the WeddingAccepted report with a filter to return only the records where the value in the Accepted field is greater than zero.

Because no records qualify, the NoData event in the WeddingAccepted report runs the NoRecords macro and cancels the opening of the report. Next, the macro attempts to set the focus on the WeddingAccepted report. Because the report is now closed, this causes an error-2489, if you’re curious-that Access returns to the macro that attempted to set the focus. Because we turned on error trapping, the ErrReport macro displays a message to inform you that no records were found in the report, as shown in Figure 18–26.

Figure 18–26: The error handling in the DoReport macro presents an informative message if the report contains no records.

If you want to see what happens when the error isn’t trapped, open the DoReport macro object, scroll down to the Accepted macro, type False in the Condition column next to the OnError action so that the error trap isn’t set, and save the macro. Try to run the report again from the WeddingList form, and you’ll see the ugly error messages that result when you don’t trap the error. Be sure to remove the False condition from the Accepted macro and save it again so that the application works properly.

If you want to use macros in your application, you should add appropriate error handling using the OnError action. A well-designed Access application should always display helpful messages to users when errors occur.

Inside Out-Clearing the MacroError Object 

The MacroError object contains only the information from the last reported error. Access retains this information in the MacroError object until either the macro stops running, another error occurs, or you run the ClearMacroError action. If you need to continue running your macro after an error is handled and expect to possibly test the MacroError object again (perhaps after setting OnError to Next), you can use the ClearMacroError action to clear the contents of the MacroError object. The ClearMacroError action requires no arguments.