Access Cookbook

Suppress Printing a Report if There Are No Records to Print

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?

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