How to programmatically build a pivotTable view in an Access 2002 form (298764)

SUMMARY

Microsoft Access 2002 introduces the ability to view tables, queries, views, functions, and forms in PivotTable view by natively hosting the Microsoft Office Web Components. This article shows you how to programmatically build a PivotTable view in a Microsoft Access form by using the Office Web Components object model.

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. back to the top

Procedure

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

Name	Description
InsertFieldSet	Adds an existing FieldSet to a particular axis (row, column, data, or filter) in the PivotTable view.
AddFieldSet	Creates a new, empty FieldSet and adds it to the PivotTable field list.
AddCalculatedField	Creates a calculated or derived field within a particular FieldSet.
AddTotal	Creates a new aggregate Total object based on a particular field, and adds it to the PivotTable field list under Totals.
InsertTotal	Adds an existing Total to the data axis of the PivotTable view.
HideDetails	Collapses the PivotTable view so that summary data is displayed.

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.

1. Open the sample database Northwind.mdb.
2. Create a new query that is based on the Employees, Orders, and Order Details tables.
3. Add the following fields to the query:

      Query: qrySales
      ------------------------------------------------
      Type: Select Query
      Join: Employees.EmployeeID <-> Orders.EmployeeID
      Join: Orders.OrderID <-> [Order Details].OrderID
   
      Field: LastName
      Table: Employees
   
      Field: OrderDate
      Table: Orders
   
      Field: ShipCountry
      Table: Orders
   
      Field: UnitPrice
      Table: Order Details
   
      Field: Quantity
      Table: Order Details
   
      Field: Discount
      Table: Order Details
   					

4. Save the query as qrySales, and then close it.
5. Create a new form that is based on the qrySales query, and then open it in Design view.
6. Add all fields to the form.
7. Save the form as frmPivotTable, and then close it.
8. On the Insert menu, click Module. This opens the Visual Basic Editor and inserts a new, blank module.
9. On the Tools menu, click References.
10. Click Browse.
11. Browse to and select the file C:\Program Files\Common Files\Microsoft Shared\Web Components\10\OWC10.DLL, and then click Open. This adds a reference to the Microsoft Office XP Web Components library.
12. Click OK to close the References dialog box.
13. Add the following Visual Basic for Applications code to the new module:

    Sub BuildPivotTable()
        Dim frm As Access.Form
        Dim pTable As OWC10.PivotTable
        Dim pFieldset As OWC10.PivotFieldSet
        Dim pField As OWC10.PivotField
        Dim pTotal As OWC10.PivotTotal
        Dim strExpression As String
        
        'Open the form in PivotTable view
        DoCmd.OpenForm "frmPivotTable", acFormPivotTable
        Set frm = Forms("frmPivotTable")
        Set pTable = frm.PivotTable
        
        'Add the LastName FieldSet to the Column drop zone
        Set pFieldset = pTable.ActiveView.FieldSets("LastName")
        pTable.ActiveView.ColumnAxis.InsertFieldSet pFieldset
        
        'Reference the OrderDate by Month Fieldset
        Set pFieldset = pTable.ActiveView.FieldSets("OrderDate By Month")
    
        
        'Exclude every field in the Fieldset, except for
        'Years and Quarters fields
        For Each pField In pFieldset.Fields
            pField.IsIncluded = False
        Next
        pFieldset.Fields("Years").IsIncluded = True
        pFieldset.Fields("Quarters").IsIncluded = True
        
        'Add the OrderDate by Month (Years/Quarters) Fieldset to the
        'Row drop zone
        pTable.ActiveView.RowAxis.InsertFieldSet pFieldset
        
        'Add the ShipCountry Fieldset to the Filter drop zone
        Set pFieldset = pTable.ActiveView.FieldSets("ShipCountry")
        pTable.ActiveView.FilterAxis.InsertFieldSet pFieldset
        
        'Create a new, empty Fieldset named Sales and
        'show it in the Field List
        Set pFieldset = pTable.ActiveView.AddFieldSet("Sales")
        pFieldset.DisplayInFieldList = True
        
        'Create a new calculated field within the FieldSet, using
        'the expression below:
        strExpression = "([UnitPrice]*[Quantity]*(1-[Discount])/100)*100"
        Set pField = pFieldset.AddCalculatedField("Sales", _
                  "Sales", "Sales", strExpression)
        
        'Format the field as Currency
        pField.NumberFormat = "Currency"
        
        'Insert the new FieldSet into the Data drop zone
        'to show the calculation for every Order (detail row).
        pTable.ActiveView.DataAxis.InsertFieldSet pFieldset
        
        'Create a new Total and add it to the Totals collection
        'Base the Total on the Sum of the calculated field you created
        'previously
        Set pTotal = pTable.ActiveView.AddTotal("Sales Totals", pField, plFunctionSum)
        pTable.ActiveView.DataAxis.InsertTotal pTotal
            
        'Collapse the PivotTable so that summary data is shown by default
        pTable.ActiveData.HideDetails
        frm.SetFocus
        Set pTotal = Nothing
        Set pField = Nothing
        Set pFieldset = Nothing
        Set pTable = Nothing
        Set frm = Nothing
    End Sub
    					

14. On the View menu, click Immediate Window to open the Immediate window.
15. Type the following into the Immediate window, and then press ENTER:

    BuildPivotTable

16. On the File menu, click Close and Return to Microsoft Access.

Note that the frmPivotTable form contains a PivotTable view representing sales by employee per quarter.

back to the top