ACC2: Empty Recordset Report Hangs with Group KeepTogether (114549)

CAUSE

Microsoft Access can stop unexpectedly while you are previewing a report if the report is based on an empty recordset and the group KeepTogether property is set to With First Detail or Whole Group.

This is a known problem with "retreating" in Microsoft Access version 2.0 reports. "Retreating" refers to returning to an earlier report section in order to determine where certain controls and sections are on a report, and whether they will fit in a given space. Examples of retreating include:

Where group levels (except for page headers and footers) are used, and the KeepTogether property is set to either Whole Group or With First Detail.
In subreports or subforms where the CanGrow or CanShrink property is set to Yes.

In these situations, the Format event occurs as Microsoft Access determines how the sections will fit on the printed page. If a section cannot be printed on the current page, Microsoft Access retreats so that the section can be printed on the following page.

RESOLUTION

To avoid this problem, 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. Two methods for avoiding the problem are described below.

Method 1

Use this method when the report is based directly on a table or on a query that does not have parameters.

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

   Sub Report_Open (cancel As Integer)
       If IsNull(DLookup("<AnyFieldInQuery>","<QueryName>")) Then
         DoCmd CancelEvent
       End If
   End Sub

Method 2

Use this method if the report is based on a parameter query. The query must be open, with the parameter values filled in, before the number of records in the recordset can be referenced.

Use the following sample code to cancel the report if the recordset is empty. This sample code references two date parameters. The key is to have the parameters filled in with values.

   Sub Report_Open (Cancel As Integer)
      Dim MyDb As Database, MyQuery As QueryDef, DataRecs As Recordset
      Set MyDb = DBEngine.Workspaces(0).Databases(0)
      Set MyQuery = MyDb.QueryDefs("QueryName")
      MyQuery("Beginning Date:") = #1/1/96# ' or Forms!FormName![Control]
      MyQuery("Ending Date:") = #1/3/96#    ' or Forms!FormName![Control]
      Set DataRecs = MyQuery.OpenRecordset()
      If DataRecs.EOF Then DoCmd CancelEvent
      DataRecs.Close
      MyQuery.Close
   End Sub

MORE INFORMATION

Steps to Reproduce Problem

WARNING: Following these steps causes Microsoft Access to stop. Be sure to save any work in progress before following these steps.

Open the sample database NWIND.MDB.
In the Database window, select the Orders table, and then choose the AutoReport button on the toolbar.
View the new report in Design view.
Choose the Sorting And Grouping button on the toolbar.

Set the following group properties:

       Customer ID
          GroupHeader: Yes
          KeepTogether: With First Detail
       Order ID
          GroupHeader: Yes
          KeepTogether: With First Detail
       Order Date
          Ascending sort only

Set the height for the Customer ID header section and the Order ID header section to zero.
Save the report as A Test Report.

Create a new macro with the OpenReport action and the following arguments:

        Report Name: A Test Report
        View: Print Preview
        Where: [Order Id] = 222

Save the macro as A Test Macro and then run it. The report will begin formatting, but will never finish. Microsoft Access will stop.
Close Microsoft Access and restart Microsoft Windows.

Start Microsoft Access and open the NWIND database. If you receive the prompt

       NWIND.MDB is corrupted or is not a database file. Attempt to
       repair?

choose Yes.

To avoid the problem in this example, add the following code to the report's OnOpen property:

    Sub Report_Open (cancel As Integer)
       If IsNull(DLookup("[Order Id]","Orders", "[Order Id] = 222")) Then
          DoCmd CancelEvent
       End If
    End Sub