ACC2: How to Cancel a Report Which Contains No Records

• Microsoft Access version 2.0

SYMPTOMS

Moderate: Requires basic macro, coding, and interoperability skills.

Reports continue to display a page even when the recordset that the report is based on does not return data. When the recordset does not return data, controls based on the recordset can return errors to the page.

RESOLUTION

To avoid this behavior, test the report's underlying recordset to see if data is returned. If no data is returned, use the CancelEvent action to stop the report from processing.

NOTE: For more information about canceling a blank report using the new NoData event in Microsoft Access for Windows 95, search for "NoData event," using the Microsoft Access for Windows 95 Help Index.

The following method shows how to cancel printing a report when it is blank. A message box notifying the user that printing has been canceled is also displayed.

If the report is based on a parameter query, use the Query-by-Form technique to obtain the values for the parameter query before the report is run. The form and values will then be available as the DLookup function re- runs the query to determine if any records are available to be printed.

Call the following Sub procedure from the report's OnOpen event:

   Sub Report_Open (Cancel as Integer)
      Dim Msg as String
      If IsNull(DLookup("<AnyFieldInQuery>","<QueryName>")) Then
         Msg = "The report has no data. " & Chr(13) & Chr(10)
         Msg = Msg & "Check the source of data for the" & Chr(13) & Chr(10)
         Msg = Msg & "report to make sure you entered the" & Chr(13) & _
               Chr(10)
         Msg = Msg & "correct criteria (for example, a " & Chr(13) &  _
               Chr(10)
         Msg = Msg & "valid range of dates). " & Chr(13) & Chr(10)
         MsgBox Msg
         Cancel = True
      End If

   End Sub

REFERENCES

For more information about the Query-by-Form technique, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q95931
   TITLE     : ACC: How to Use the Query-by-Form (QBF) Technique