Access: Cancel Opening Report if No Data

There’s hardly any point in opening a report if it has no data to display, so how do you get your Microsoft Access database to just let you know that there’s nothing there and to not bother opening it? The answer is all about the On No Data event.

With the report open in Design View, go to the Property Sheet. If the Property Sheet isn’t already open, you can access it by clicking the ‘Property Sheet’ button on the ‘Design’ tab of the ribbon. From the ‘Selection type' drop-down list at the very top, make sure you have ‘Report’ selected. Now go to the ‘Event’ tab. Click next to where it says ‘On No Data’ and then click the ellipsis button (…) which will appear just to the right-hand side. In the window which pops up, choose ‘Code Builder’, and then press the ‘OK’ button.

Now you’re in the VBA editor, where it has already set up a subroutine for you called Report_NoData. The code you enter here will run when you open a report and there is no data to be displayed. If you’re comfortable with VBA, you can go ahead and put whatever you like in here. Here’s what I use to make a simple dialog box come up to inform the user that the report is empty, and to then cancel the opening of the report:

Private Sub Report_NoData(Cancel As Integer)

     message = MsgBox("There is no data available for this report.", vbOKOnly, "No Data Available")
     Cancel = True

End Sub

When you’re done, you can save and close the VBA editor window and the report. The next time you try to view the report and there’s no data to display, this handy bit of code you entered will prevent you from seeing a pile of errors and an empty report.

0 comments:

Post a Comment