12.5 Add an Item to the Startup Group

3.9 Suppress Printing a Report if There Are No Records to Print

3.9.1 Problem

You have a report that prints records you select from a criteria form. Sometimes there aren't any records that match the criteria and the report opens with #Error in the detail section, which is unattractive and confusing. Is there any way you can prevent the report from printing when it has no records to print?

3.9.2 Solution

Access includes an event, OnNoData, that fires when no records are present in the report's underlying recordset. This solution shows you how to use this new event to suppress printing of the report when no records match the specified criteria.

To create a report that suppresses printing when there are no records, follow these steps:

1. Create a new report or open an existing report in design view.

2. Create an event procedure attached to the report's OnNoData property. (If you're unsure of how to do this, see Section P.5.5 in the the preface of this book.) Enter the following VBA code in the event procedure:

   Private Sub Report_NoData(Cancel As Integer)       MsgBox "Sorry, no records match these criteria!", _        vbExclamation, "No Records to Print"       Cancel = True End Sub

3. Save and run the report. If you enter criteria that do not match any records, you will get a message box telling you that no records meet the criteria (like the one shown in Figure 3-21).

The following example demonstrates this solution. Load the 03-09.MDB database. Open the frmCriteria1 pop-up criteria form. This form allows you to enter criteria for the rptSelect1 report (see Figure 3-19).

Figure 3-19. The frmCriteria1 pop-up criteria form with default values

When you press the traffic-light button, a simple event procedure will execute that opens the report in print preview mode. The rptSelect1 report is based on the qryCriteria1 parameter query, which derives its parameter values from the controls on the frmCriteria1 form. If you accept the default values, the parameter query will return a recordset with no records. This will produce the report shown in Figure 3-20.

Figure 3-20. rptCriteria1 prints a page of errors when no records are selected

Now open the frmCriteria2 pop-up criteria form. This form is identical to the first, except that the event procedure attached to its command button runs the rptSelect2 report instead. If you accept the default values, the rptSelect2 report will attempt to run, again with no records. But this version of the report has an event procedure attached to its OnNoData event that suppresses printing and instead displays the message box shown in Figure 3-21.

Figure 3-21. rptCriteria2 displays this message and cancels printing when there are no records

3.9.3 Discussion

The OnNoData event is triggered whenever a report attempts to print with no records. If you attach an event procedure to the OnNoData event, your code will run whenever the report prints without any records. While the MsgBox statement informs the user what has happened, the key line of code is:

Cancel = True

This line tells Access to cancel printing of the report (by setting the passed Cancel argument to True).

If you use VBA code to open a report that has no data and allow the report's OnNoData event to cancel the report, you will get an error in the code that attempted to open the report. So, in this solution, you'll find error-handling code in the button-click event that opens the report in frmCriteria2. When an error occurs, the code checks whether it's the expected error, which has a number of 2501. If so, it ignores the error. Here's the code behind the cmdPrint button:

Private Sub cmdPrint_Click(  )          On Error GoTo HandleErr     Me.Visible = False     DoCmd.OpenReport "rptSelect2", acPreview ExitHere:     DoCmd.Close acForm, Me.Name     Exit Sub HandleErr:     Select Case Err.Number         Case 2501             ' The OpenReport action was canceled.             ' There were no rows. So do nothing.         Case Else             MsgBox Err.Number & ": " & Err.Description     End Select     Resume ExitHere      End Sub

The report header contains controls to display the selection criteria, which are picked up from the criteria form, using expressions like this one:

=[Forms]![frmCriteria1]![txtLastOrderAfter]

For more information on printing query criteria on reports, see Section 3.2.2.

The form disappears from view when the report opens in print preview mode because the event procedure attached to the traffic-light button sets the form's Visible property to False before opening the report. Making the form invisible (rather than closing it) ensures that the selection criteria are still available for the report's data source.

 

Get a Performance Boost by Converting Old Report Code

Prior to Access 95, you had to use a DCount function in an event procedure attached to the report's OnOpen event to solve the "no data" problem. This workaround, however, had the negative side effect of forcing Access to execute the report's query an extra time when there were records in the report's recordset. If you have used this workaround (which still works in Access), you will realize a significant performance boost in your report by instead using the technique presented in this solution.