totn Access

MS Access 2003: Determine if a report has no data

This MSAccess tutorial explains how to determine if a report has no data to return in Access 2003 (with screenshots and step-by-step instructions).

Question: In Microsoft Access 2003/XP/2000/97, I have a report and I want to be able to stop a report from opening if it does not have any data. Instead, I'd like to display a message to the user. How can I tell if a report is not going to have any data?

Answer: There is an "On No Data" event on each report object. This event is triggered when your report result set returns no data. You could place code on this event to close the report is no data is found.

To do this, open your report in design view. Under the View menu, select Properties.

Microsoft Access

When the Report Properties window appears, click on the button (with three dots) to the right of the "On No Data" property.

Paste in the following code:

Private Sub Report_NoData(Cancel As Integer)

   Dim LResponse As Integer

   Cancel = True
   LResponse = MsgBox("Your report selection returned no data.", vbOKOnly, "Report")

End Sub

Now if your report is opened and there is no data, you will get the following message:

Microsoft Access

Share on: