Cancel printing a report if it does not contain any records

By default, you can print reports that contain no records. To solve this problem, you can use either a macro or Microsoft Visual Basic for Applications (VBA) code to detect the absence of records in a report and then cancel the print operation. The steps in this article explain how to use both techniques.

What do you want to do?

Use a macro to cancel printing

Use VBA code to cancel printing

You probably want to cancel printing a report when it doesn't contain any records. For example, if you're starting to sell a new product, there is likely to be a period of time where you have yet to register any sales. You should, therefore, consider the possibility that some of your reports might not contain any detail records and that aggregate functions, such as the Count function, might have nothing to count. To handle such an occurrence gracefully, you can create a macro that cancels the print job. You can also add a few lines of carefully placed VBA code to do the same thing. VBA is the programming language that Microsoft Office Access 2007 uses.

You add either the macro or the VBA code to the report's On No Data event procedure. Office Access 2007 triggers the On No Data event whenever you run a report that has no records. The macro and the VBA code described in this article display an appropriate message and cancel the printing of the report when that report contains no data. When you add a macro or VBA code to the On No Data event procedure, the macro or the VBA code runs whenever you open a report that contains no records. When you click OK to close either alert message, the macro also closes the blank report. When you add either the macro or the VBA code to the report, a blank report will not open when you try to display it in Report view or Layout view — but you can open the report in Design view.

Use a macro to cancel printing

The macro described in this section displays an alert message when you try to print a blank report. When you click OK to close the message box, the macro automatically cancels the print operation. If you do not include an alert message, it will appear as if nothing happens when you try to print the report — something that will probably confuse the users of the report.

Create the macro

1. In the Navigation Pane, right-click the report that you want to change and click Design View.

2. On the Design tab, in the Show/Hide group, click Property Sheet.

   -or-

   Double-click the box in the upper left or right corner of the report, depending on your regional and language settings.

3. Click the Event tab, and in the On No Data property box, click .

   The Choose Builder dialog box appears.

4. Click Macro Builder, and then click OK.

   The macro designer starts and displays a blank macro.

5. In the first row of the macro, click the field in the Action column and select MsgBox from the list.

6. Under Action Arguments in the lower section of the macro designer, in the Message box, type the text for your alert message.

   For example, you could enter the following: There are no records to report.

7. Optionally, change the argument value in the Beep box from Yes to No and, in the Type list, select the type of icon that you want to appear in your alert message.

8. In the Title box, type the title for your alert message.

   For example, you could enter No Records.

   Your changes appear in the upper portion of the macro designer — in the Argument column, next to the MsgBox action.

9. In the upper part of the macro designer, click the next cell in the Action column (the cell directly below MsgBox), and then select CancelEvent

   Your macro should look like the following, although you can use different text in the arguments:

   

10. On the Design tab, in the Close group, click Save As, and then use the Save As dialog box to give the macro a name.

11. Close the macro. If an alert message appears and asks if you want to save changes to the macro and the report property, click Yes, and continue to the next steps to test it.

Test your macro

• In the Navigation Pane, right-click the report that contains the macro and click Print. Depending on the options you chose, an alert message similar to the following should appear:

  

  When you click OK to close the message, the CancelEvent action stops the print operation. Because you specified no other event (such as opening the report for viewing), the report closes.

Top of Page

Use VBA code to cancel printing

The VBA code described here works much like the macro described in the previous section — it displays an alert message when you open a blank report, and then cancels the print operation when you close the alert message.

Add VBA code

1. In the Navigation Pane, right-click the report that you want to change and click Design View.

   Note: To complete this procedure use a report that contains no records.

2. On the Design tab, in the Show/Hide group, click Property Sheet.

   -or-

   Double-click the box in the upper left or right corner of the report, depending on your regional and language settings.

3. Click the Event tab, and in the On No Data property box, click .

   The Choose Builder dialog box appears.

4. Click Code Builder, and then click OK.

   The Visual Basic Editor starts and displays a blank event procedure.

5. Type the following code in the Visual Basic Editor so that the Report_NoData procedure appears exactly like this when you're done:

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

6. When you are finished, click File, and then click Save.

7. Click File, and then click Close and Return to Microsoft Office Access.

8. Close the open report, and then click Yes to confirm the save.

9. In the Navigation Pane, right-click the report that you just changed and click Print. You will see a message like the following:

The Report_NoData procedure uses the MsgBox function to display the There are no records to report message and an OK button. When you click OK, the "Cancel=True" line in the procedure instructs Access to cancel the report. Cancel is an argument that is automatically passed to the event procedure and is always checked by Access when the event procedure completes.

Top of Page