This documentation is archived and is not being maintained.

Extending Functionality of PivotCharts and PivotTables

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.
 

Wes Kim
Microsoft Corporation

April 2001

Applies to:
   Microsoft® Access 2002

Summary: Describes how to use events to extend the functionality of PivotTable and PivotChart views in Microsoft Access 2002. (5 printed pages)

Contents

Introduction
Where the Events Live
Data Source Events
Display Events
Change Events
Command Events
Keyboard and Mouse Events
Conclusion

Introduction

Through Microsoft® Office Web Components, Microsoft Access developers can include PivotTable® and PivotChart® views in their applications. In Access 2002, several new events have been added to the Access 10.0 Object Library to give developers even more control over PivotTable and PivotChart views. This article gives a brief introduction to these new events and how they might be used.

For syntax and code examples for these events, see Access Visual Basic® Help.

Where the Events Live

Not all of the events are available in both PivotTable and PivotChart views. The following table outlines where the various events are available.

EventAvailable in PivotTable viewAvailable in PivotChart view
OnConnect, OnDisconnectYesNo
BeforeQuery, QueryYesNo
AfterLayout, BeforeRender, AfterRender, AfterFinalRenderNoYes
DataChangeYesNo
DataSetChangeNoYes
PivotTableChangeYesNo
SelectionChange, ViewChangeYesYes
CommandEnabled, CommandChecked, CommandBeforeExecute, CommandExecuteYesYes
KeyDown, KeyPress, KeyUp, MouseDown, MouseMove, MouseUp, MouseWheel*, Click, DblClickYesYes

* Of the keyboard and mouse events, only the MouseWheel event is entirely new to Access 2002.

Data Source Events

OnConnect, OnDisconnect

The OnConnect event occurs after a PivotTable view establishes a connection to its data source, and the OnDisconnect event occurs after a PivotTable view disconnects from its data source. Generally, the connection is assumed for the duration of the PivotTable session. However, an application could use the OnDisconnect event to alert the user if the connection to the data source was lost.

BeforeQuery, Query

The BeforeQuery and Query events occur before and after a PivotTable view sends a query to its data source. Using these events, an application could notify the user that a query is in progress by displaying a message or changing the pointer to an hourglass.

Display Events

AfterLayout

The AfterLayout event occurs after the entire layout of a PivotChart view has been calculated but before any of the elements have been drawn on the screen. By trapping for this event, an application could use the ChChartDraw object returned by the drawObject argument to draw shapes or text on the empty chart canvas before any of the chart elements are drawn.

BeforeRender, AfterRender

The BeforeRender and AfterRender events occur before and after each element of a PivotChart view is drawn on the screen. By trapping for these events, an application could use the ChChartDraw object returned by the drawObject argument to draw shapes or text on the chart before or after a specific element is drawn. Also, an application could use the object returned by the chartObject argument to change the position or other properties of the element before it is drawn.

AfterFinalRender

The AfterFinalRender event occurs after all elements of a PivotChart view are drawn on the screen. By trapping for this event, an application could use the ChChartDraw object returned by the drawObject argument to draw shapes or text on the topmost layer of the chart canvas after all of the chart elements are drawn.

Change Events

DataChange

The DataChange event occurs when certain properties of a PivotTable view are changed or certain methods are called from a PivotTable view. There are a wide variety of circumstances than can trigger this event. By evaluating the Reason argument, an application can determine what sort of change occurred and take appropriate action.

DataSetChange

The DataSetChange event occurs when the data set for a PivotChart view changes. This can mean either an actual change in the underlying data as a result of a query sent to the data source, or a structural change to the data displayed in the current PivotChart view (for example, the addition of a series). Because this event does not provide specific information about the reasons for a change to the data set, it is recommended to use other events (such as the display events above) to trap for specific changes to the PivotChart view.

PivotTableChange

The PivotTableChange event occurs when a field, field set, or total is added to or deleted from a PivotTable view; the Reason argument indicates which of these has occurred. Trapping for this event could provide feedback to developers about how users are manipulating PivotTable views in their applications.

SelectionChange

The SelectionChange event occurs whenever the current selection changes in a PivotTable or PivotChart view. An application could then test what type of object is currently selected and change the user interface accordingly (for example, to customize a list of available commands based on the current selection).

ViewChange

The ViewChange event occurs whenever the view changes in a PivotTable or PivotChart view. In the case of PivotTable views, the Reason argument will indicate what type of change has occurred; trapping for this event could allow an application to conditionally format a PivotTable view based on how a user has changed the current view. For PivotChart views, the Reason argument always returns the same value (-1), and trapping this event is of limited use.

Command Events

CommandEnabled

The CommandEnabled event occurs when a PivotTable or PivotChart view tests whether a command is available. The Command argument indicates which command's availability is being tested. An application could trap for this event and use the Enabled argument to dynamically enable or disable commands when the user displays a command menu.

CommandChecked

The CommandChecked event occurs when a PivotTable or PivotChart view tests whether a command is checked. The Command argument indicates which command's status is being tested. An application could trap for this event and use the Checked argument to dynamically check or uncheck commands when the user displays a command menu.

CommandBeforeExecute, CommandExecute

The CommandBeforeExecute and CommandExecute events occur before and after a command from a PivotTable or PivotChart view is executed. The Command argument indicates which command is about to execute or has just executed. The Cancel argument of the CommandBeforeExecute event can be used to cancel a pending command. Using these events, an application could prevent certain commands from executing or alert the user as to the status of a pending command.

Keyboard and Mouse Events

KeyDown, KeyPress, KeyUp, MouseDown, MouseMove, MouseUp, MouseWheel, Click, DblClick

Except for the MouseWheel event, these keyboard and mouse events are new events for PivotTable and PivotChart views in Access 2002, but they are not new for Access in general; their use is already covered in Access Visual Basic Help.

The MouseWheel event occurs when the user clicks or scrolls with the mouse wheel. An application could trap for this event if the developer wishes the mouse wheel to trigger a custom action rather than the default mouse wheel behavior (for example, highlighting different columns in a PivotTable view).

Conclusion

With these new events in the Access 10.0 Object Library, developers are now able to exert even greater control over PivotTable and PivotChart views in their applications.

Show: