databasedev.co.uk - database solutions and downloads for microsoft access

Cancel Printing of a Blank Report in Microsoft Access:

As the Microsoft Access database developer, you will probably create many reports for your database users to view and print. Many of these may be fairly complex and have parameters to be entered by the user to generate the report based upon input criteria. However, if one of your database users enters criteria for the report that does not exist, Microsoft Access will still create an empty report. In this article, we will deal with canceling the printing of a blank Access report.

When a report contains no records, the 'detail' area of the report is blank. Some users may be accustomed to just sending the report directly to the printer, rather than previewing the report first, therefore he or she won't know that the report was blank until it has been picked up from the printer. Aside from wasting paper printing the blank report, the user will also have wasted time. We can prevent this from happening when designing the report.

How to Cancel the Printing of a Blank Report

To run a Microsoft Access macro that cancels a report that contains no records:

1. Open the report in design view
2. From the On No Data event property, click on the Build button (...)
3. Run the Macro Builder
4. Name the macro and click OK
5. In the first macro Action row, select MsgBox from the drop-down list and set the arguments
6. In the second Action row, choose the CancelEvent action
7. Save and close the macro
8. Save the report

Microsoft Access Report Events

There are various events available in the report, as there are in a form, with which you can run a macro from. The events available for the report, and examples of how they could be used are shown below:

Event	Explanation	Possible Use
Open	Occurs before the report is previewed or printed and before, if applicable, an underlying query is ran.	Open a custom dialog box to collect report criteria
Close	Occurs when the report window is closed.	Display a menu or switchboard form
Activate	Occurs when the report window becomes the active window.	Show a custom toolbar or maximize the report window
Deactivate	Occurs when the window loses the focus and before the Close event.	Hide a custom toolbar
No Data	Occurs when the underlying query has been run with no records returned.	Cancel the previewing or printing of the report
Page	Occurs after a report page has been formatted for printing but before it's printed.	Draw a border around the page
Error	Occurs when there is a run-time error generated by the database engine .	Display a custom error message

Canceling the Printing of a Blank Report

Scenario: You create a report for your users, detailing Customer Orders. It enables the user to print off a report for their chosen Customer, for a specific time period that they enter. Sometime, when they create the report, there may be no Orders for the time period that they specify, and the report detail will be blank. If this is the case, you would like to prevent the report from printing, and cancel the print.

To create the macro, we perform the following steps:

1. In design view of your report, open the Properties sheet.
2. Select the Event page.
3. Select the On No Data event and click on the Build Button (...) to start the Macro Builder.
   
   
   The report properties, showing the On No Data event
4. Name the macro, mcrNoData, and click OK.
5. In the first row of the Action column, select MsgBox from the drop-down list.
   
   In the Action Arguments pane, enter the following details:
   • Message: There were No Orders placed by this Customer during the specified period
   • Type: Information
   • Title: Customer Order Information
   
   In the first row of the Comment column, enter "User Information"
6. In the second row of the Action column, select the CancelEvent action.
   
   In the second row of the Comment column, enter "Cancel printing of the report if there is no data"
7. Save and Close the macro.
   
   The completed macro will look like the image shown below:
   
   
   The completed macro, which will cancel the printing of a report containing no data
   
   The macro is attached to the On No Data event of the report:
   
   

If we try to preview the report now, and enter parameter values for the report, and no records match this data, the message box will be displayed:

The MsgBox displayed when trying to preview or print a report containing No Data.

When the message box is displayed, clicking on OK cancels the printing/previewing of the report.

Images added to article courtesy of databasedev.co.uk