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.