Here's a problem that's been around for a long time—how to cancel an empty Access report. Fortunately, there's an easy solution, but I still get mail about it.
Here's the problem—if you open a report where the criteria matches no records, Access displays an empty report. That's seldom what you'll want users to see. Instead, you'll want to inform the user that there's no data to report, and then cancel the report.
If you're using Access 97 or later, use the report's No Data event as follows:
Private Sub Report_NoData(Cancel As Integer)
MsgBox "There are no matching records", _
Cancel = True
The procedure displays an informational message explaining that the report contains no matching records. After the user dismisses the message box, the event sets the Cancel parameter to True, canceling the report. The user never sees the empty report.
If you're still using or supporting Access 95, use the report's Open event as follows:
Private Sub Report_Open(Cancel As Integer)
If Dcount("*", Me.RecordSource) = 0 Then
MsgBox "No records match the current criteria"
Cancel = True
The result is the same, but this event actually counts the report's records. If there are no matching records, the procedure displays a message box and then cancels the report.
Sometimes, the Dcount() function slows down a report a bit, but unless you're viewing thousands of records, you probably won't notice.
Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.