How to Programmatically Build a PivotChart View in an Access Form
This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).
SUMMARY
MORE INFORMATION
Name | Description |
---|---|
object | Represents one chart in the chart workspace. |
method | Creates a new, empty chart or adds a series to a chart, depending on the proceeding object. |
method | Deletes every chart in the chart workspace and resets all the formatting to the default values. |
property | Returns the | collection for the specified chart.
Sets data for the specified chart object. |
: 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.
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 Access.
- On the menu, point to , and then click .
- Create a new query that is based on the Employees and the Orders tables.
- Add the following fields to the query:
Query: qrySales ------------------------------------------------ Type: Select Query Join: Employees.EmployeeID <-> Orders.EmployeeID Field: LastName Table: Employees Total: Group By Sort: Ascending Field: OrderID Table: Orders Total: Count
- Save the query as , and then close it.
- Create a new form that is based on the qryOrdersbyEmployees query, and then open it in Design view.
- Add all the fields to the form.
- Save the form as , and then close it.
- On the menu, click . This opens the Visual Basic Editor and inserts a new, blank module.
- On the menu, click .
- Click .
- Browse to and select the following file, and then click C:\Program Files\Common Files\Microsoft Shared\Web Components\10\OWC10.DLL
. This adds a reference to the Microsoft Office XP Web Components library.
- Click to close the dialog box.
- Add the following Visual Basic for Applications (VBA) code to the new module:
Sub BuildPivotChart() Dim objPivotChart As OWC10.ChChart Dim objChartSpace As OWC10.ChartSpace Dim frm As Access.Form Dim strExpression As String Dim rs As Recordset Dim values Dim axCategoryAxis Dim axValueAxis 'Open the form in PivotChart view. DoCmd.OpenForm "frmPivotChart", acFormPivotChart Set frm = Forms("frmPivotChart") Set rs = frm.Recordset 'Loop through Recordset to obtain data for the chart and put in strings. rs.MoveFirst Do While Not rs.EOF strExpression = strExpression & rs.Fields(0).Value & Chr(9) values = values & rs.Fields(1).Value & Chr(9) rs.MoveNext Loop rs.Close Set rs = Nothing 'Trim any extra tabs from string. strExpression = Left(strExpression, Len(strExpression) - 1) values = Left(values, Len(values) - 1) 'Clear existing Charts on Form if present and add a new chart to the form. 'Set object variable equal to the new chart. Set objChartSpace = frm.ChartSpace objChartSpace.Clear objChartSpace.Charts.Add Set objPivotChart = objChartSpace.Charts.Item(0) 'Set a variable to the Category (X) axis. Set axCategoryAxis = objChartSpace.Charts(0).Axes(0) ' Set a variable to the Value (Y) axis. Set axValueAxis = objChartSpace.Charts(0).Axes(1) ' The following two lines of code enable, and then ' set the title for the category axis. axCategoryAxis.HasTitle = True axCategoryAxis.Title.Caption = "Employees" ' The following two lines of code enable, and then ' set the title for the value axis. axValueAxis.HasTitle = True axValueAxis.Title.Caption = "Orders" 'Add Series to Chart and set the caption. objPivotChart.SeriesCollection.Add objPivotChart.SeriesCollection(0).Caption = "Orders" 'Add Data to the Series. objPivotChart.SeriesCollection(0).SetData chDimCategories, chDataLiteral, strExpression objPivotChart.SeriesCollection(0).SetData chDimValues, chDataLiteral, values 'Set focus to the form and destroy the form object from memory. frm.SetFocus Set frm = Nothing End Sub
- On the menu, click .
- Type the following into the Immediate window, and then press ENTER:
BuildPivotChart
- On the menu, click .