The Refresh command in PivotTable or PivotChart view does not display new and deleted records in Office Access 2003 and in Access 2002 (299825)
The information in this article applies to:
- Microsoft Office Access 2003
- Microsoft Access 2002
This article was previously published under Q299825 Moderate: Requires basic macro, coding, and interoperability skills.
This article applies only to a Microsoft Access database (.mdb).
SYMPTOMS
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.
CAUSE
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.
RESOLUTION
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. - Start Microsoft Access, and then open your database.
- On the Insert menu, click Module to open the Visual Basic Editor and to insert a new module.
- 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
- On the File menu, click Save <project name>.
- On the File menu, click Close and Return to Microsoft Access.
- On the Tools menu, click Customize.
- On the Toolbars tab, click to select the PivotTable check box to display the PivotTable toolbar.
- Click the Commands tab.
- In the Categories list, click File.
- In the Commands list, drag Custom to the PivotTable toolbar.
- On the PivotTable toolbar, right click the Custom control that you just added, and then click Properties on the shortcut menu.
- Set the properties of the Custom control as follows:
Caption: Requery
OnAction: =RequeryActiveObject()
- Click Close to close the property sheet.
- On the Toolbars tab, click to clear the PivotTable check box to hide the PivotTable toolbar.
- Click to select the PivotChart check box to display the PivotChart toolbar.
- Repeat steps 8 through 13 to create a Requery control on the PivotChart toolbar.
- On the Toolbars tab, click to clear the PivotChart check box to hide the PivotChart toolbar.
- 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.
STATUS
This behavior is by design.
Modification Type: | Major | Last Reviewed: | 3/22/2006 |
---|
Keywords: | kbExpertiseAdvanced kbtshoot kbprb KB299825 kbAudDeveloper kbAudITPRO |
---|
|