Show All

Hide All

You'll probably want to cancel a report when it doesn't contain any records. For instance, if you're starting to sell a new product, there's likely to be a period of time (hopefully short) where you have yet to register any sales. You should, therefore, consider the possibility that there may be no detail records to report — and that aggregate functions, such as the Count function, might have nothing to count. In order to gracefully handle such an occurrence, you can add a few lines of carefully placed Microsoft Visual Basic® for Applications (VBA) code to your report. VBA is the programming language that Access uses.

You add the VBA code to the report's NoData event procedure (event procedure: A procedure that is automatically executed in response to an event initiated by the user or program code, or that is triggered by the system.). Access triggers the NoData event (event: An action recognized by an object, such as a mouse click or key press, for which you can define a response. An event can be caused by a user action or a Visual Basic statement, or it can be triggered by the system.) whenever a report runs and is found to have no records. You can place code in the event procedure that will display an appropriate message and cancel the printing of the report. The code will run whenever the report runs and it contains no records.

Create the VBA code by following these steps:

To open the report in Design view   

1. In the Database window, under Objects, click Reports.
2. Click the report, and then click Design in the Database window.

To edit the NoData event procedure   

1. On the Edit menu, click Select Report.
2. On the View menu, click Properties, and then select the Event tab.
3. Click the On No Data property box. Click the ellipsis button (...) when it appears.
4. When the Choose Builder dialog box appears, click Code Builder and then click OK.
5. Type the following code in the Visual Basic Editor (Visual Basic Editor: An environment in which you write new and edit existing Visual Basic for Applications code and procedures. The Visual Basic Editor contains a complete debugging toolset for finding syntax, run-time, and logic problems in your code.) 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


When you're finished, on the File menu, click Save. Then, on the File menu, click Close and Return to Microsoft Office Access. Finally, close all windows except the Database window. Now, when you run the report and there's no data, you'll see a message like the following:

The Report_NoData procedure uses the MsgBox function to display a "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's automatically passed to the event procedure and is automatically checked by Access when the event procedure completes.