Microsoft KB Archive/298764

= How to programmatically build a pivotTable view in an Access 2002 form =

Article ID: 298764

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



This article was previously published under Q298764



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

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

IN THIS TASK
SUMMARY
 * Procedure

REFERENCES



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.

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.

 Open the sample database Northwind.mdb. Create a new query that is based on the Employees, Orders, and Order Details tables.  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  Save the query as qrySales, and then close it. Create a new form that is based on the qrySales query, and then open it in Design view. Add all fields to the form.</li> Save the form as frmPivotTable, and then close it.</li> On the Insert menu, click Module. This opens the Visual Basic Editor and inserts a new, blank module.</li> On the Tools menu, click References.</li> Click Browse.</li> 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.</li> Click OK to close the References dialog box.</li>  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 &quot;frmPivotTable&quot;, acFormPivotTable Set frm = Forms(&quot;frmPivotTable&quot;) Set pTable = frm.PivotTable 'Add the LastName FieldSet to the Column drop zone Set pFieldset = pTable.ActiveView.FieldSets(&quot;LastName&quot;) pTable.ActiveView.ColumnAxis.InsertFieldSet pFieldset 'Reference the OrderDate by Month Fieldset Set pFieldset = pTable.ActiveView.FieldSets(&quot;OrderDate By Month&quot;)

'Exclude every field in the Fieldset, except for 'Years and Quarters fields For Each pField In pFieldset.Fields pField.IsIncluded = False Next pFieldset.Fields(&quot;Years&quot;).IsIncluded = True pFieldset.Fields(&quot;Quarters&quot;).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(&quot;ShipCountry&quot;) pTable.ActiveView.FilterAxis.InsertFieldSet pFieldset 'Create a new, empty Fieldset named Sales and 'show it in the Field List Set pFieldset = pTable.ActiveView.AddFieldSet(&quot;Sales&quot;) pFieldset.DisplayInFieldList = True 'Create a new calculated field within the FieldSet, using 'the expression below: strExpression = &quot;([UnitPrice]*[Quantity]*(1-[Discount])/100)*100&quot; Set pField = pFieldset.AddCalculatedField(&quot;Sales&quot;, _             &quot;Sales&quot;, &quot;Sales&quot;, strExpression) 'Format the field as Currency pField.NumberFormat = &quot;Currency&quot; '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(&quot;Sales Totals&quot;, 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 </li> On the View menu, click Immediate Window to open the Immediate window.</li> Type the following into the Immediate window, and then press ENTER:

BuildPivotTable

</li> On the File menu, click Close and Return to Microsoft Access.</li></ol>

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

back to the top

<div class="references_section">