Automating Microsoft Access with VBA

Like forms, you can handle report-specific errors using a special event the report's Error event when an error occurs at the Jet database engine level. The event has two arguments for passing error values to the event:

• DataErr Stores error code returned by the Err object.

• Response Determines whether an error message is displayed using one of the following intrinsic constants: acDataErrContinue ignores the error and acDataErrDisplay displays the default error message.

The benefit of the Error event is that you can glean an error's actual number and then add that error to your list of errors that you need to handle if a generic handler isn't appropriate. Let's look at a quick example using BillingReport:

1. Open BillingReport in Design view and change the Record Source property from BillingReportSource to test.

2. Save the report and close it.

3. Open the BillingReportSetup form, choose a client, and then click the Open Report button. Viewing the report in Print Preview generates the error shown in Figure 14.3.

   Figure 14.3. Removing a report's data source produces a Jet error.

4. Click OK to clear the message.

5. Open the report's module and enter the following code in the report's Error event:

   Private Sub Report_Error(DataErr As Integer, Response As Integer) Debug.Print DataErr End Sub

6. View the report in Print Preview to generate the error again.

7. View the Immediate window in the VBE. It contains the Jet error code, as shown in Figure 14.4.

   Figure 14.4. You can print the Jet error code to the Immediate window.

8. Now that you know the error code, replace the Debug.Print statement in the error handler with the following code:

   Private Sub Report_Error(DataErr As Integer, Response As Integer) If DataErr = 2580 Then MsgBox "The report's data source has been changed or is " & _ "missing", vbOKOnly, "Error" Response = acDataErrContinue End If End Sub

9. View the report in Print Preview one more time. The same error occurs, but this time, instead of displaying the default message, the Error event displays your custom message shown in Figure 14.5. Click OK to clear the message.

   Figure 14.5. Use the Error event to handle Jet errors your way.

10. Return the report to Design view and reset the Record Source property to BillingReportSource.

In this particular case, the custom message you added to the Error event isn't superior to the internal message, but that isn't really the point of the exercise. Now you know how to determine an error's code so you can include it in your own error-handling routine.

What to Do When There's No Data

Reports have a unique problem what to return when there are no records in the report's underlying record source. That really isn't an error, and Access will still display the report, but most of the time, users won't appreciate or understand what they see. For instance, Figure 14.6 shows the BillingReport report opened from the Database window instead of going through the setup form.

As you can see, the report is full of error values. To avoid this problem, use the report's NoData event to cancel the report or display a custom message. This section uses the billing report to illustrate a simple solution for this problem. First, open BillingReport's module and enter the following code:

Private Sub Report_NoData(Cancel As Integer) MsgBox "There are no records to print", _ vbOKOnly, "No report" Cancel = True End Sub

Preview the report and click OK in response to the parameter prompts. These occur because Access is trying to resolve references to the form values that are missing. After dismissing three parameter prompts, Access displays the message box shown in Figure 14.7. Click OK to clear the message box.