> ## Content Index
> Fetch the complete content index at: https://nolongerset.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# Show a Friendly Message When a Report Has No Data
- URL: https://nolongerset.com/informnodata/
- Published: 2022-05-11T03:19:39.000Z
- Updated: 2026-05-08T12:57:41.000Z
- Description: 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.
- Author: Mike Wolfe
- Tags: Code Library, #ReportFunctions, Reports, #Import 2026-05-20 02:59

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:

1. "**No current record**" error
2. **#Type!** errors on text boxes with calculated Control Source properties
3. **#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.

---

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2022/05/image-12.png)

The "**No current record**" error

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2022/05/image-11.png)

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.

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2022/05/image-13.png)

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](https://docs.microsoft.com/en-us/office/vba/api/access.report.onnodata):

> 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:

```vba
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](https://nolongerset.com/the-openreport-action-was-canceled/):

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2022/05/image-14.png)

[I use vbWatchdog](https://nolongerset.com/error-handling-evolution/). 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](https://docs.microsoft.com/en-us/office/vba/api/access.report.hasmodule) 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:

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2022/05/image-15.png)

## The InformNoData() Function

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

```vba
' ----------------------------------------------------------------
' 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:

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2022/05/image-16.png)

#### 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:

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2022/05/image-17.png)

## 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

---

### Referenced articles

[The ‘OpenReport’ Action Was CanceledDoes it annoy you that using the Report NoData event as Codd intended results in a runtime error? Because it annoys me.![](https://nolongerset.com/favicon.png)No Longer SetMike Wolfe![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2021/03/event-4927525_1920.jpg)](https://nolongerset.com/the-openreport-action-was-canceled/)

### External references

[Report.OnNoData property (Access)Office VBA reference topicMicrosoft Docso365devx![](https://docs.microsoft.com/en-us/media/logos/logo-ms-social.png)](https://docs.microsoft.com/en-us/office/vba/api/access.report.onnodata)

[Report.HasModule property (Access)Office VBA reference topicMicrosoft Docso365devx![](https://docs.microsoft.com/en-us/media/logos/logo-ms-social.png)](https://docs.microsoft.com/en-us/office/vba/api/access.report.hasmodule)

*Image by [Gerd Altmann](https://pixabay.com/users/geralt-9301/?utm%5Fsource=link-attribution&utm%5Fmedium=referral&utm%5Fcampaign=image&utm%5Fcontent=895399) from [Pixabay](https://pixabay.com/?utm%5Fsource=link-attribution&utm%5Fmedium=referral&utm%5Fcampaign=image&utm%5Fcontent=895399)*