Automating a Client Application Using Macros in Microsoft Access 2010

Trapping Errors in Macros

Access 2010 supports 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 2010, close the ValidateCitySetStateAndZip macro and then 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 20-20, you can see some of the submacro names, conditional expressions, actions, and arguments for this example macro.

In the first action of the macro, we call the MsgBox function in the If conditional expression to ask whether you want to use error trapping. (You can learn more about the settings in the MsgBox function in Table 20-4 on page 1243.) 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 submacro. If you click No, the condition is False, so Access moves down to the Else block and executes the second RunMacro that calls the TrapNo submacro.

The first action in the TrapYes submacro uses the 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 submacro 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 an If conditional expression 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 an If block on the next action. For more complex errors, you should go to another submacro that can test for several potential errors that you plan to handle. In this example, we tell Access to run the Trapped submacro if any errors occur.

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 submacro when the error occurs. Although we could have examined the error and perhaps taken some other action, for this simple example, we used a MessageBox action to tell Access to display a message containing the error number and description of the error. Scroll down the macro design surface, select this MessageBox 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 MessageBox action asks Access to fetch the Number and Description properties of the MacroError object and display them in the message.

Finally, the TrapNo submacro 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 If conditional expression, Access displays the message box shown in Figure 20-21, asking whether you want to trap the error as part of evaluating the condition.

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 20-22.

Click OK in this error message, and then Access displays the Macro Single Step dialog box, as shown in Figure 20-23. Not very user friendly, is it? Access displays the Macro Single Step 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 text boxes. Access displays the error number currently stored in the MacroError object in the Error Number text box—in this case, error number 11.

The only button you can click in this dialog box is Stop All Macros. 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 displays often in your applications. Click Stop All Macros to close the Macro Single Step 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 submacro (shown earlier in Figure 20-20), which executes OnError, followed by the SetTempVar action that generates an error. Access traps the error and executes the Trapped submacro as requested. That submacro asks Access to display another message box with the error number and description, as shown in Figure 20-24. Notice that Access displays the error number (11) and error description (Division By Zero) in the message text.

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 a 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 Macro Single Step 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 submacros, you can see that we used the OnError action just before each OpenReport action. Scroll down until you can see the PrintF submacro, as shown in Figure 20-25.

The first action of the submacro turns the mouse pointer into an hourglass. The second action calls the Cancel submacro that closes the PrintOptions form and puts the focus back on the WeddingList form. The third action 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 submacro if any errors occur. The fourth action attempts to open the report.

In each of the four reports in this sample database, the On No Data event property specifies the NoRecords submacro. 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 action of our submacro—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 Macro Single Step dialog box. Instead, the ErrReport submacro runs, and this submacro 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 Print-Options form, select List Invitees Who Have Accepted. Unless you have changed the sample data, this report should return no records. Click Print to run the PrintIt submacro in the DoReport macro group. This submacro looks at the option you chose on the PrintOptions form and runs the Accepted submacro. That submacro 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 submacro and cancels the opening of the report. Next, the submacro 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 submacro that attempted to set the focus. Because we turned on error trapping, the ErrReport submacro displays a message to inform you that no records were found in the report, as shown in Figure 20-26.

If you want to see what happens when the error isn’t trapped, open the DoReport macro object, scroll down to the Accepted submacro, and change the Go To argument to Fail for 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 change the Go To argument back to Macro Name in the OnError action of the Accepted submacro 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.