Create Better MS Access Reports
Report Builder for Microsoft Access helps you create dynamic, professional-looking reports fast! The easy-to-understand wizard helps you with complex tasks like calculated fields, adding subreports, customizing styles, as well as grouping and sorting. Download a free trial today!
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.
To run a Microsoft Access macro that cancels a report that contains no records:
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:
|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|
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:
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