Code Library

Show a Friendly Message When a Report Has No Data

A handy function you can call from an Access report's Property Sheet to gracefully inform the user when their report has no data to display.

Mike Wolfe

May 10, 2022 • 4 min read

Opening a report in Access with all the data filtered out is often an unpleasant user experience.

The user may see one or more of the following:

"No current record" error
#Type! errors on text boxes with calculated Control Source properties
#Error errors on text boxes that call into VBA functions

There is a simple way to avoid all three potential issues. Read on for details.

The Control Source for the text box whose value is highlighted in yellow above is `=[DistrictName] & " Totals"`. The #Type! error appears when there are no records to display.

The Control Source for the text box whose value is highlighted in yellow above is `=TaxTypeLookup([TaxType]) & " Total"`. The function takes a single `Variant` argument and returns a `Variant`, so the error does not come from trying to pass a Null value.

Handling the Situation With OnNoData

Luckily, Access has us covered with the OnNoData event:

The NoData event occurs after Microsoft Access formats a report for printing that has no data (the report is bound to an empty recordset), but before the report is printed.

The problem with handling the OnNoData event is that you have to do it for every single bound report.

Within the NoData event handler, you can set Cancel = True to prevent the report from opening, as seen in the code below from an old Refunds Report of mine:

Private Sub Report_NoData(Cancel As Integer)
    MsgBox "No refunds found for these dates"
    Cancel = True
End Sub

One thing to keep in mind with setting Cancel = True is that it will raise error 2501:

I use vbWatchdog. Within my global error handler, I ignore error 2501 because 99.99% of the time it's this sort of nuisance warning.

Simplifying the OnNoData Handling

One of the downsides to using the OnNoData setting is that you have to add a code module for every report where you want to handle the OnNoData event.

In many cases, the OnNoData event handler will be the only code in the entire module. Wouldn't it be nice to avoid creating a whole bunch of heavyweight reports (i.e., those where the report's HasModule property is set to Yes) if you didn't have to?

To help keep my reports lightweight, I created a simple public function that could be called directly from a report's Property Sheet:

The InformNoData() Function

Here's the code for the InformNoData() function:

' ----------------------------------------------------------------
' Procedure : InformNoData
' Author    : Mike Wolfe <mike@nolongerset.com>
' Source    : https://nolongerset.com/informnodata/
' Purpose   : Improve UX when opening bound reports with no data.
' 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

Attempting to Open a No-Data Report Without a Filter

If you call the DoCmd.OpenReport method with no FilterName or WhereCondition parameters and the recordset is empty, you will see the following message:

Attempting to Open a No-Data Report With a Filter

If you do include the FilterName or WhereCondition parameter in your call to DoCmd.OpenReport, you will see the following message if all the results are filtered out:

Notes About the Function

Even though it returns no result, this procedure must be a Function (and not a Sub) so that it can be called from the Report Property Sheet
The MsgBox title is set to the report's Caption property (make sure you set it for the best user experience!)
You can easily automate the assignment of =InformNoData([Report]) to the OnNoData property of many reports by looping through the report collection in code