The Refresh command in PivotTable or PivotChart view does not display new and deleted records in Office Access 2003 and in Access 2002

The Refresh command in PivotTable view or in PivotChart view does not display new records or remove records that have been deleted from a table.

The Refresh command only displays changes that have been made to existing records. It does not requery the object to remove deleted records or to display newly added records.

You must requery the object that you are viewing in PivotTable view or in PivotChart view. You can do this by switching the object into Form or Datasheet view, pressing SHIFT+F9, and then switching back to the PivotTable view or PivotChart view. You can also programmatically requery the object by calling the Requery method.

You can also customize the PivotTable and PivotChart toolbars to call a user-defined function that requeries the active object. The following example demonstrates how to create the custom function, and how to call it from the PivotTable and PivotChart toolbars.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

1. Start Microsoft Access, and then open your database.
2. On the Insert menu, click Module to open the Visual Basic Editor and to insert a new module.
3. Type the following function into the module:

   Function RequeryActiveObject()
       On Error GoTo RequeryActiveObject_Err
       Screen.ActiveDatasheet.Requery
   
   RequeryActiveObject_Exit:
       Exit Function
   
   RequeryActiveObject_Err:
       MsgBox Err.Description, vbExclamation, Err.Number
       Resume RequeryActiveObject_Exit
   End Function
   					

4. On the File menu, click Save <project name>.
5. On the File menu, click Close and Return to Microsoft Access.
6. On the Tools menu, click Customize.
7. On the Toolbars tab, click to select the PivotTable check box to display the PivotTable toolbar.
8. Click the Commands tab.
9. In the Categories list, click File.
10. In the Commands list, drag Custom to the PivotTable toolbar.
11. On the PivotTable toolbar, right click the Custom control that you just added, and then click Properties on the shortcut menu.
12. Set the properties of the Custom control as follows:

       Caption: Requery
       OnAction: =RequeryActiveObject()
    					

13. Click Close to close the property sheet.
14. On the Toolbars tab, click to clear the PivotTable check box to hide the PivotTable toolbar.
15. Click to select the PivotChart check box to display the PivotChart toolbar.
16. Repeat steps 8 through 13 to create a Requery control on the PivotChart toolbar.
17. On the Toolbars tab, click to clear the PivotChart check box to hide the PivotChart toolbar.
18. Click Close to close the Customize dialog box.

Now when you open any object in PivotTable view or in PivotChart view, the Requery control requeries the object's underlying data source. This causes the PivotTable view or the PivotChart view to display newly added records and to remove deleted records.

This behavior is by design.

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. Close the Main Switchboard form if it appears.
4. On the View menu, point to Database Objects, and then click Queries.
5. Click New.
6. In the New Query dialog box, click Design View, and then click OK.
7. In the Show Table dialog box, click the Customers table, click Add, and then click Close.
8. Click the asterisk (*) at the top of the Customers field list to add Customers.* to the Field row of the query design grid.
9. Click Customers.* in the Field row of the query design grid.
10. On the View menu, click PivotTable View.
11. In the PivotTable field list, drag the CustomerID and CompanyName fields to the Drop Totals or Detail Fields Here area. Note that the PivotTable list displays all records returned by the query.
12. Press F11 to view the Database window.
13. On the View menu, point to Database Objects, and then click Tables.
14. Click the Customers table, and then click Open.
15. Modify the CompanyName column of the first record.
16. On the Insert menu, click New Record.
17. Enter AAAAA in the CustomerID column and My New Company in the CompanyName column.
18. Close the table.
19. Switch to the query window that contains the PivotTable list.
20. On the PivotTable menu, click Refresh.

Note that the changes that you made to the existing records are displayed; however, the new record that you added does not appear.