Nothing To See Here

Did you know that Microsoft Access provides an event that fires whenever you open a bound report but all of the underlying data has been filtered out?

For example, consider the Invoice report from the Northwind sample database.  The two screenshots below show the report with data (on the left) and without data (on the right):

Improving the User Experience

Showing a report with no data leads to user confusion.  They'll often assume–understandably–that there's a bug in the program.  However, in most cases, there simply is no data to report.  

Handling the No Data event for a report is an easy way to improve the user experience.  It's a simple thing, but explicitly telling the user there is nothing to report is a big improvement over showing a mostly empty report with some random error text.

A Simple Improvement

Generating the above user messages does not require custom code for every report.  Rather, we can take advantage of the DoCmd.CancelEvent method and add this functionality to our reports without even having to add a code-behind to the report.

Here's the function:

'Usage: Paste the following into the OnNoData property of a report:
'       =InformNoData(Report)
Function InformNoData(Rpt As Report)
    MsgBox "There is nothing to report" & _
            IIf(Len(Rpt.Filter) > 0 And Rpt.FilterOn, _
               " for the criteria you specified", vbNullString) & _
            ".", vbInformation, Rpt.Caption
    DoCmd.CancelEvent
End Function

To add it to your report:

The OpenReport Action Was Canceled

You should be aware that canceling a report in the OnNoData event will trigger an Access error (2501).  The easiest way to avoid seeing that error message is to use my PreviewReport function.

Screenshot of Leslie Nielsen as Det. Frank Drebin in The Naked Gun