Microsoft KB Archive/297971

= HOW TO: Programmatically Change a PivotChart's Type in Access 2002 =

Article ID: 297971

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



This article was previously published under Q297971



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
 * Changing the Chart Type of a Form
 * Changing the Chart Type of PivotChart Subform on a Report



SUMMARY
This article shows you how to programmatically change the chart type of a Microsoft Access form in PivotChart view.

Microsoft Access automatically saves any programmatic changes that you make to forms in PivotChart view. Unlike with other properties, Access stores programmatic changes to properties related to PivotCharts with the object when Access closes. For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

282350 ACC2002: PivotChart and PivotTable Views Are Saved Automatically

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

Changing the Chart Type of a Form
This example demonstrates how to change the chart type of a form in PivotChart view. This example uses an unbound main form that contains a subform in PivotChart view. The main form contains a combo box whose list contains various chart types that a user may select. The code on the AfterUpdate event of the combo box then changes the chart type of the subform. To programmatically change the chart type of a form in PivotChart view, follow these steps:  Open the sample database, Northwind.mdb. Create a new, blank form in Design view.  Add a combo box to the form, and then set the following properties:   Combo box -  Name: cboChartType Column Count: 2 Column Widths: 0&quot;;1&quot; RowSourceType: Value List RowSource:0;Clustered Column;47;3D Clustered Column;1;Stacked Column;48;3D Stacked Column;3;Clustered Bar;51;3D Clustered Bar;4;Stacked Bar;52;3D Stacked Bar;6;Line;54;3D Line;18;Pie NOTE: The combo box in this example only shows a small subset of the various chart types available for forms in PivotChart view. To add additional chart types and their appropriate numeric values to the combo box, add a reference to the Microsoft Office XP Web Components library, and search the Object Browser for the enum ChartChartTypeEnum.  In the Database window, drag the Sales Analysis Subform2 form onto the new form to create a subform.  Set the following properties of the subform:   Subform Name: PivotChart Left: 0.25&quot; Top: 0.5&quot; Width: 7&quot; Height: 3.25&quot;  On the View menu, click Code to view the module of the form.</li> On the Tools menu, click References.</li> Click the Browse button.</li> Browse to the file C:\Program Files\Common Files\Microsoft Shared\Web Components\10\OWC10.DLL. Click the file, 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 code to the AfterUpdate event procedure of the combo box: Private Sub cboChartType_AfterUpdate Dim objChartSpace As OWC10.ChartSpace Dim objPivotChart As OWC10.ChChart Dim frmChart As Access.Form 'In this example, the following Form reference refers 'to a subform that is in PivotChart view. You can modify 'this reference to point to a main form by using this syntax: '  '   Set frmChart = Forms(&quot;frmMyForm&quot;) '  Set frmChart = Me.Controls(&quot;PivotChart&quot;).Form Set objChartSpace = frmChart.ChartSpace Set objPivotChart = objChartSpace.Charts.Item(0) objPivotChart.Type = Me.Controls(&quot;cboChartType&quot;).Value End Sub </li> On the File menu, click Close and Return to Microsoft Access.</li> On the File menu, click Save. Name the form frmMain .</li> On the View menu, click Form View.</li> Select different items from the combo box.</li></ol>

Note that the subform PivotChart's type changes to correspond to the value in the combo box.

back to the top

Changing the Chart Type of PivotChart Subform on a Report
Changing the chart type of a PivotChart subform on a report is similar to changing the chart type of other forms in PivotChart view. The difference is that you can only change properties of the PivotChart during the Format event of the report section that contains the subform. <ol> If you have not done so already, follow steps 1 through 3 from the &quot;Changing the Chart Type of a Form&quot; section earlier in this article.</li>  Add a command button to the form, and then set the following properties: <pre class="fixed_text">  Command Button Name: cmdOpenReport Caption: Open Report </li> On the View menu, click Code to view the module of the form.</li>  Add the following code to the Click event of the command button: Private Sub cmdOpenReport_Click DoCmd.OpenReport &quot;rptMain&quot;, acViewPreview End Sub </li> On the File menu, click Save. Name the form frmMain, and then close the form.</li> Create a new, blank report in Design view.</li> <li>In the Database window, drag the Sales Analysis Subform2 form onto the detail section of the new report to create a subform.</li> <li> Set the following properties of the subform: <pre class="fixed_text">  Subform Name: PivotChart Left: 0.25&quot; Top: 0.5&quot; Width: 7&quot; Height: 3.25&quot; </li> <li>On the View menu, click Code to view the module of the report.</li> <li>On the Tools menu, click References.</li> <li>Click the Browse button.</li> <li>Browse to the file C:\Program Files\Common Files\Microsoft Shared\Web Components\10\OWC10.DLL. Click the file, and then click Open. This adds a reference to the Microsoft Office XP Web Components library.</li> <li>Click OK to close the References dialog box.</li> <li> Add the following code to the Format event procedure of the detail section: Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) Dim objChartSpace As OWC10.ChartSpace Dim objPivotChart As OWC10.ChChart Dim frmChart As Access.Form Set frmChart = Me.Controls(&quot;PivotChart&quot;).Form Set objChartSpace = frmChart.ChartSpace Set objPivotChart = objChartSpace.Charts.Item(0) objPivotChart.Type = Forms(&quot;frmMain&quot;).Controls(&quot;cboChartType&quot;).Value End Sub </li> <li>On the File menu, click Close and Return to Microsoft Access.</li> <li>On the File menu, click Save. Name the report rptMain, and then close the report.</li> <li>Open the frmMain form.</li> <li>Select a chart type from the combo box, and then click the Open Report button.</li></ol>

Note that the report opens and that the subform PivotChart type corresponds to the value that you selected.

back to the top

Additional query words: OfficeKBHowTo inf

Keywords: kbhowto kbhowtomaster KB297971

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.