Friday, 9 October 2015

Closing a report automatically if no data exists

There's nothing worse than running a report and it returning a blank page, especially if you choose to print the report rather than preview it. There are two ways round this conundrum: the first is to perform a manual check on the eventual record source the report will use before you open, and then to only open the report is the record count is greater than zero. This can be done using DCount() or with a recordset (preferred, as it is faster). However, I don't like this approach as, assuming there is data, it means running two queries - one to check there is data and a second when you actually open the report. I prefer to make use of the report's OnNoData event instead, like so:

Private Sub Report_NoData(Cancel As Integer)
    MsgBox "No data matches the specified criteria.", vbInformation
    Cancel=True
End Sub

This way, when the report opens if no data is found up pops a message box to that effect, and the report closes. What could be simpler?

No comments:

Post a comment