Software

How to inhibit an empty Access report

Cancel empty Access report

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",  _

   vbInformation

   Cancel = True

End Sub

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

  End If

End Sub

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.

About Susan Harkins

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.

Editor's Picks

Free Newsletters, In your Inbox