Microsoft KB Archive/295273

= ACC2002: Subform in PivotTable or PivotChart View Prepares Data Twice If the Main Form Is Sorted or Filtered =

Article ID: 295273

Article Last Modified on 9/25/2003

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



This article was previously published under Q295273



Novice: Requires knowledge of the user interface on single-user computers.

This article applies only to a Microsoft Access database (.mdb).



SYMPTOMS
A subform in PivotTable or PivotChart view prepares its data twice when you move between records on the main form.



CAUSE
You have sorted or filtered the main form on one or more fields.



RESOLUTION
Base the main form on a parameterized query to sort or filter it instead of using the sorting and filtering features available in Form view.

For more information about creating parameter queries, click Microsoft Access Help on the Help menu, type create a parameter query in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

This information is also available on the World Wide Web at the following Microsoft Web site:

Create a parameter query



STATUS
Microsoft has confirmed that this is a problem in Microsoft Access.



MORE INFORMATION
In the situation where the main form is sorted or filtered, but the subform is not, Microsoft Access requeries the subform object twice. The first requery explicitly fetches all records from the subform's RecordSource property. The second requery then builds the subform recordset based on the LinkChildFields and LinkMasterFields properties.

This requerying behavior occurs anytime the main form is sorted or filtered, regardless of the subform's current view. However, if a subform is in PivotTable or PivotChart view, the requery actions take place immediately, whereas in other views, it is delayed and takes place asynchronously. Even though this behavior occurs any time the main form is sorted or filtered, this problem is only noticeable when the subform recordset is extremely large.

Steps to Reproduce the Behavior
 CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

 Open the sample database Northwind.mdb. Create a new, blank query that is not based on any table or query. On the Query menu, point to SQL Specific, and then click Union.  Enter the following SQL statement into the query window:

SELECT * FROM [Order Details] UNION ALL SELECT * FROM [Order Details] UNION ALL SELECT * FROM [Order Details] UNION ALL SELECT * FROM [Order Details] UNION ALL SELECT * FROM [Order Details] UNION ALL SELECT * FROM [Order Details] UNION ALL SELECT * FROM [Order Details]; NOTE: Because this behavior is only noticeable when the subform contains many records, the union query above is used to simulate that situation. </li> On the File menu, click Save. When you are prompted for the query name, enter qryUnionDetails, click OK, and then close the query.</li> Click the qryUnionDetails query in the Database window, and then on the Insert menu, click Form.</li> In the New Form dialog box, click Form Wizard, and then click OK.</li> Add all fields from the qryUnionDetails query to the Selected Fields list, and then click Next.</li> Click the PivotTable option, and then click Finish. This creates a new form in PivotTable view named qryUnionDetails.</li> From the PivotTable field list, drag the ProductID field to the Drop Row Fields Here area.</li> From the PivotTable field list, drag the OrderID field to the Drop Totals or Detail Fields Here area, and then close the form.</li> Create a new, blank form that is based on the Products table, and then open it in Design view.</li> Drag the ProductID and ProductName fields from the field list to the detail section of the form.</li> In the Database window, drag the qryUnionDetails form onto the new form to create a subform.</li>  Set the LinkChildFields and LinkMasterFields properties of the subform as follows: <pre class="fixed_text">  Subform: qryUnionDetails -  Link Child Fields: ProductID Link Master Fields: ProductID </li> On the View menu, click Form View.</li> Use the navigation buttons to scroll between records on the main form. For each record, note that the &quot;Preparing Data&quot; progress meter appears at the bottom of the Access application window. Depending on the speed of your computer, this may happen very quickly.</li> On the Records menu, point to Filter, and then click Filter by Form.</li> Type Like 'C*' in the ProductName box.</li> <li>On the Filter menu, click Apply Filter/Sort.</li> <li>Use the navigation buttons to scroll between records on the main form.</li></ol>

Note that for each record, the &quot;Preparing Data&quot; progress meter appears twice.

Additional query words: pra

Keywords: kbbug kbnofix KB295273

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.