HOW TO: Programmatically Change a PivotChart's Type in Access 2002 (297971)

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: 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:

1. Open the sample database, Northwind.mdb.
2. Create a new, blank form in Design view.
3. Add a combo box to the form, and then set the following properties:

      Combo box
      -------------------------------------------------------------
      Name: cboChartType
      Column Count: 2
      Column Widths: 0";1"
      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.
4. In the Database window, drag the Sales Analysis Subform2 form onto the new form to create a subform.
5. Set the following properties of the subform:

      Subform
      ----------------
      Name: PivotChart
      Left: 0.25"
      Top: 0.5"
      Width: 7"
      Height: 3.25"
   					

6. On the View menu, click Code to view the module of the form.
7. On the Tools menu, click References.
8. Click the Browse button.
9. 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.
10. Click OK to close the References dialog box.
11. 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("frmMyForm")
       '
       Set frmChart = Me.Controls("PivotChart").Form
       Set objChartSpace = frmChart.ChartSpace
       Set objPivotChart = objChartSpace.Charts.Item(0)
       objPivotChart.Type = Me.Controls("cboChartType").Value
    End Sub
     
    					

12. On the File menu, click Close and Return to Microsoft Access.
13. On the File menu, click Save. Name the form frmMain.
14. On the View menu, click Form View.
15. Select different items from the combo box.

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.

1. If you have not done so already, follow steps 1 through 3 from the "Changing the Chart Type of a Form" section earlier in this article.
2. Add a command button to the form, and then set the following properties:

      Command Button
      --------------------
      Name: cmdOpenReport
      Caption: Open Report
   					

3. On the View menu, click Code to view the module of the form.
4. Add the following code to the Click event of the command button:

   Private Sub cmdOpenReport_Click()
       DoCmd.OpenReport "rptMain", acViewPreview
   End Sub
   					

5. On the File menu, click Save. Name the form frmMain, and then close the form.
6. Create a new, blank report in Design view.
7. In the Database window, drag the Sales Analysis Subform2 form onto the detail section of the new report to create a subform.
8. Set the following properties of the subform:

      Subform
      ----------------
      Name: PivotChart
      Left: 0.25"
      Top: 0.5"
      Width: 7"
      Height: 3.25"
   					

9. On the View menu, click Code to view the module of the report.
10. On the Tools menu, click References.
11. Click the Browse button.
12. 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.
13. Click OK to close the References dialog box.
14. 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("PivotChart").Form
       Set objChartSpace = frmChart.ChartSpace
       Set objPivotChart = objChartSpace.Charts.Item(0)
       objPivotChart.Type = Forms("frmMain").Controls("cboChartType").Value
    End Sub
    					

15. On the File menu, click Close and Return to Microsoft Access.
16. On the File menu, click Save. Name the report rptMain, and then close the report.
17. Open the frmMain form.
18. Select a chart type from the combo box, and then click the Open Report button.

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

back to the top