Cancel Report No Records

This tip, Cancel Report No Records, is for those of you who do not know how to cancel a report by using the on ‘No Data’ event for reports. Many thanks to Stuart for the code he sent to me.

As an example, if you create a command button that opens a report, then the code created by Microsoft Access would look similar to this:

Private Sub Command7_Click()

On Error GoTo Err_Command7_Click

Dim stDocName As String

stDocName = "NameOfReport"

DoCmd.OpenReport stDocName, acPreview

Exit_Command7_Click:

Exit Sub

Err_Command7_Click:

MsgBox Err.Description

Resume Exit_Command7_Click

End Sub

Cancel Report No Records. To cancel a report we will not be adding code to this section, but to the ‘On No Data” event for the report. To access this section of the database:

Close the VBE (coding) window (if you have it open);

Open the Report you wish to add the code to, in Design view;

Right-click on the upper left-hand corner of the report; where the two rulers meet, and select properties from the list displayed;

Click on the ‘Event’ tab;

Place your cursor next to ‘On No Data’. This will display the ‘…’ button - click on this button;

Then, in the pop-up window, select ‘Code Builder’ and click on ‘OK’.

The following code will be displayed:

Option Compare Database

Option Explicit

Private Sub Report_NoData(Cancel As Integer)

End Sub

To this code, add the following (Bold):

Option Compare Database

Option Explicit

Dim bool_nodata As Boolean

Private Sub Report_NoData(Cancel As Integer)

bool_nodata = True

End Sub

When you have finished that code, close the VBE window. This time place your cursor next to the ‘On Page’ Event. Create the code, as per above, and add the new code in Bold:

Private Sub Report_Page()

If bool_nodata = True Then

MsgBox 'No data to display' DoCmd.Close acReport, "report name", acSaveNo

End If

End Sub

When finished, close the VBE window.

Now when you try to open the report based on a query that returns no records, the report will not open and the user will be informed.

Do you like this tip? Subscribe to my Newsletter to receive tips via email. Click Here to subscribe.