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

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

1. 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.
   
   
2. Open the sample database Northwind.mdb.
3. Create a new, blank query that is not based on any table or query.
4. On the Query menu, point to SQL Specific, and then click Union.
5. 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.
6. On the File menu, click Save. When you are prompted for the query name, enter qryUnionDetails, click OK, and then close the query.
7. Click the qryUnionDetails query in the Database window, and then on the Insert menu, click Form.
8. In the New Form dialog box, click Form Wizard, and then click OK.
9. Add all fields from the qryUnionDetails query to the Selected Fields list, and then click Next.
10. Click the PivotTable option, and then click Finish. This creates a new form in PivotTable view named qryUnionDetails.
11. From the PivotTable field list, drag the ProductID field to the Drop Row Fields Here area.
12. From the PivotTable field list, drag the OrderID field to the Drop Totals or Detail Fields Here area, and then close the form.
13. Create a new, blank form that is based on the Products table, and then open it in Design view.
14. Drag the ProductID and ProductName fields from the field list to the detail section of the form.
15. In the Database window, drag the qryUnionDetails form onto the new form to create a subform.
16. Set the LinkChildFields and LinkMasterFields properties of the subform as follows:

       Subform: qryUnionDetails
       -----------------------------
       Link Child Fields: ProductID
       Link Master Fields: ProductID
    					

17. On the View menu, click Form View.
18. Use the navigation buttons to scroll between records on the main form. For each record, note that the "Preparing Data" progress meter appears at the bottom of the Access application window. Depending on the speed of your computer, this may happen very quickly.
19. On the Records menu, point to Filter, and then click Filter by Form.
20. Type Like 'C*' in the ProductName box.
21. On the Filter menu, click Apply Filter/Sort.
22. Use the navigation buttons to scroll between records on the main form.

Note that for each record, the "Preparing Data" progress meter appears twice.