Microsoft KB Archive/114549

= ACC2: Empty Recordset Report Hangs with Group KeepTogether =

Article ID: 114549

Article Last Modified on 7/8/2002

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition

-



This article was previously published under Q114549



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



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

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("","")) 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.



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.</li>  Set the following group properties:

<pre class="fixed_text">      Customer ID          GroupHeader: Yes KeepTogether: With First Detail Order ID         GroupHeader: Yes KeepTogether: With First Detail Order Date Ascending sort only </li> Set the height for the Customer ID header section and the Order ID header section to zero.</li> Save the report as A Test Report.</li>  Create a new macro with the OpenReport action and the following arguments:

<pre class="fixed_text">       Report Name: A Test Report View: Print Preview Where: [Order Id] = 222 </li> Save the macro as A Test Macro and then run it. The report will begin formatting, but will never finish. Microsoft Access will stop.</li> Close Microsoft Access and restart Microsoft Windows.</li>  Start Microsoft Access and open the NWIND database. If you receive the prompt

<pre class="fixed_text">      NWIND.MDB is corrupted or is not a database file. Attempt to      repair? choose Yes. </li>  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 </li></ol>

<div class="references_section">