ACC2: Empty Recordset Report Hangs with Group KeepTogether

• Microsoft Access version 2.0

SYMPTOMS

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

Microsoft Access stops while you are previewing a report and may mark the database as corrupted. Running Repair Database on the database eliminates the error message that states that the database is corrupted.

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.

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

STATUS

Microsoft has confirmed this to be a problem in Microsoft Access version 2.0. This problem no longer occurs in Microsoft Access version 7.0.

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.

1. Open the sample database NWIND.MDB.

2. In the Database window, select the Orders table, and then choose the AutoReport button on the toolbar.

3. View the new report in Design view.

4. Choose the Sorting And Grouping button on the toolbar.

5. 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
   
   

6. Set the height for the Customer ID header section and the Order ID header section to zero.

7. Save the report as A Test Report.

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

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

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

10. Close Microsoft Access and restart Microsoft Windows.

11. 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.

12. 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
    
    

REFERENCES

For more information about the Retreat event, search for "Retreat," and then "Retreat Event" using the Microsoft Access Help menu.