Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. ACC2002: How to Programmatically Build a PivotChart View in an Access Form

ACC2002: How to Programmatically Build a PivotChart View in an Access Form

Advanced: Requires expert coding, interoperability, and multiuser skills.


This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).


↑ Back to the top

Summary

In Microsoft Access 2002, you can view tables, queries, views, functions, and forms in PivotChart view. This is possible because Access 2002 natively hosts the Microsoft Office Web Components. This article shows you how to use the Office Web Components object model to programmatically build a PivotChart view in a Microsoft Access form.

↑ Back to the top

More Information

Microsoft Access exposes the PivotChart object model through the ChartSpace property of a form. There are a number of objects and methods that you must use to programmatically build a PivotChart view in a Microsoft Access form. The following table briefly explains what each method does.


NameDescription
ChChart objectRepresents one chart in the chart workspace.
Add methodCreates a new, empty chart or adds a series to a chart, depending on the proceeding object.
Clear methodDeletes every chart in the chart workspace and resets all the formatting to the default values.
SeriesCollection propertyReturns the ChSeriesCollection collection for the specified chart.
SetDataSets data for the specified chart object.

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.

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 Access.
  2. On the Help menu, point to Sample Databases, and then click Northwind Sample Database.
  3. Create a new query that is based on the Employees and the Orders tables.
  4. 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
  5. Save the query as qryOrdersbyEmployees, and then close it.
  6. Create a new form that is based on the qryOrdersbyEmployees query, and then open it in Design view.
  7. Add all the fields to the form.
  8. Save the form as frmPivotChart, and then close it.
  9. On the Insert menu, click Module. This opens the Visual Basic Editor and inserts a new, blank module.
  10. On the Tools menu, click References.
  11. Click Browse.
  12. Browse to and select the following file, and then click Open. This adds a reference to the Microsoft Office XP Web Components library.
    C:\Program Files\Common Files\Microsoft Shared\Web Components\10\OWC10.DLL
  13. Click OK to close the References dialog box.
  14. 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
  15. On the View menu, click Immediate Window.
  16. Type the following into the Immediate window, and then press ENTER:
    BuildPivotChart
  17. On the File menu, click Close and Return to Microsoft Access.

↑ Back to the top

References

For more information about the PivotTable object model, see the "Microsoft Office Web Components Visual Basic Reference" Help file (Owcvba10.chm). You can find this Help file in the language-specific folder (for example, the 1033 folder) at the following default location:

C:\Program Files\Common Files\Microsoft Shared\Web Components\10
For example, if you are using the English version of Microsoft Office XP, you can find this Help file in the following location:

C:\Program Files\Common Files\Microsoft Shared\Web Components\10\1033

↑ Back to the top

Keywords: kbbillprodsweep, kbhowto, kb, kbarchive

↑ Back to the top

Article Info
Article ID : 304143
Revision : 1
Created on : 1/7/2017
Published on : 6/23/2005
Exists online : False
Views : 205