Off the Chart Access

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

EASYaccess

LEVEL: Experienced Programmer Access 2002 / PivotCharts / VBA / Office XP / OWC

Formatting Access 2002 PivotCharts with VBA

By Mike Gunderloy

For many years, customizing the display of visual information in Microsoft Access has depended on understanding the Microsoft Graph object model. With Access 2002, however, that's a thing of the past. Two big advances in Office XP give Access developers unprecedented control over graphing. First, the Access team tightly integrated the Pivot Chart from Office Web Components (OWC) into the PivotChart view of forms. Second, the OWC team extended their object models to encompass nearly every corner of the OWC.

The result is an object model that makes serious graph manipulation and customization possible. The downside to this, of course, is that you need to learn the new object model to use it effectively. This article will demonstrate some of the more useful and important parts of the PivotChart object model, and provide examples of using it in code.

There are four components in the OWC library:

• DataSourceControl is used to hook up other components to data sources.
• ChartSpace represents a PivotChart (the model discussed in this article).
• PivotTableList represents a PivotTable.
• Spreadsheet represents an OWC spreadsheet.

Any one of these object models includes dozens of objects and hundreds of methods and properties. Rather than attempt to exhaustively cover the entire ChartSpace object model, we'll concentrate on some key areas and leave the rest for your own exploration. Fortunately, the Office Web Components install an excellent help file on your hard drive when you set up Office XP. You'll find it at Program Files\Common Files\Microsoft Shared\Web Components\10\1033\OWCVBA10.CHM.

The ChartSpace Object Model

FIGURE 1 shows the objects from the ChartSpace object model that we'll be using in this article. At the root of the object hierarchy is an Access Form object. Although it comes from a different library than the Access objects, the ChartSpace object model is tightly tied to the Access library via the Form object. Forms in Access 2002 expose a ChartSpace property, which can be used to retrieve the ChartSpace object.

FIGURE 1: Chart formatting objects from the ChartSpace object model.

The ChartSpace object represents the overall drawing canvas for a PivotChart. You can have multiple charts open within a ChartSpace, but Access always creates a single chart when you place a form in PivotChart view (we'll be using a single chart in this article). Note that in the ChartSpace object model, the names of objects aren't the same as the names of the properties that retrieve those objects. For instance, to retrieve a ChChart object representing the first (default) ChChart object in a ChartSpace object, you'd use code something like this:

  Dim oChart As ChChart
Set oChart = Forms(strFormName).ChartSpace.Charts(0) 

The Charts property of the ChartSpace object retrieves a ChCharts collection, which is numbered (as are all OWC collections) starting at zero. As another example, the ChChart object has a PlotArea property that retrieves a ChPlotArea object, and so on.

The ChPlotArea object represents the part of the chart where the data and the grid lines are drawn. Depending on the type of chart, the ChPlotArea object has one or more ChSurface objects. In 3D charts, for example, there are ChSurface objects named BackWall, Floor, and SideWall. Each of these objects contains a ChInterior object that can be used to manipulate the color and texture properties of the corresponding surface.

The ChChart object is one of several objects that contains a ChTitle object, representing (in this case), the chart title. The ChTitle object in turn contains a ChFont object that controls the font properties of the title.

The ChChart object also contains a collection of ChAxis objects. There's a collection for the category axis (the x axis on the sample chart, where the dates are presented), and one for the value axis (the y axis on the sample chart, where the counts are presented). Although both axes are represented by instances of the ChAxis objects, these two objects have different sets of valid properties. On the category axis, you can control the tick mark and tick label spacing separately. On the value axis, both are controlled by the MajorUnit property, but you set the maximum and minimum values by using properties of the axis' ChScaling object. Both types of axis use a ChTitle object to set their captions.

The ChAxis object representing the category axis also includes a collection of ChCategoryLabel objects that hold the labels used along the lower edge of the chart. In addition to holding individual ChCategoryLabel objects, this collection contains a pointer to a PivotAxis object. The PivotAxis object comes from the PivotTable object model, and provides a link from the chart to the underlying data. We'll use this later in the article to retrieve some label information.

Finally, there's a hierarchy of objects beneath the ChChart object that you can use to drill down to individual points plotted on the chart. These objects are:

• ChSeriesCollection contains individual ChSeries objects.
• ChSeries represents a single series (set of data points) on the chart.
• ChPoints represents the data points in an individual series.
• ChPoint represents an individual data point.
• ChInterior represents formatting for a data point on a 3D chart.

Note that almost all the objects we're using are concerned with formatting the chart, rather than with the data it contains. This is because Access PivotCharts are bound to the data in a table or query, and you can't modify bound data through the object model of the chart. The object model is more useful for dressing up the data after it's been chosen, as you'll see in the remainder of this article.

Setting the Chart Type

Let's start with a simple example. FIGURE 2 shows a PivotChart, together with a form that lets you set the type of chart (Area, Line, Pie, and so on). Like the other samples in this article, the PivotChart and the controls are on two separate forms. That's because the PivotChart automatically expands to fill the entire display surface of the form. Even form headers and footers aren't displayed when a form is in PivotChart mode.

FIGURE 2: Setting the chart type.

FIGURE 3 shows the entire code for this example. As you can see, it manipulates a single property of the ChartSpace object model, ChChart.Type. The code starts by ensuring the chart form is open in PivotChart mode. Then it navigates through the ChartSpace object model to set a variable, oChart, to point to the PivotChart itself.

  Option Compare Database
Option Explicit
 Dim oChart As ChChart
  Private Sub cboType_Change()
  oChart.Type = cboType
End Sub
  Private Sub Form_Open(Cancel As Integer)
   If Not CurrentProject.AllForms("frmEggs").IsLoaded Then
    DoCmd.OpenForm "frmEggs", acFormPivotChart
   End If
   Set oChart = Forms("frmEggs").ChartSpace.Charts(0) 
  cboType = oChart. Type
End Sub

FIGURE 3: The code for the PivotChart and form shown in FIGURE 2.

The combo box on the Chart Type form uses a Value List to set two columns of data:

  29;Area;60;3-D Area;50;3-D Bar;47;3-D Clustered Column; 
  32;Doughnut;18;Pie;58;3-D Pie;12;Smooth Line

The first column contains the constants for some of the many chart types the PivotChart can display; the second column contains friendlier human descriptions of these types. You can see the entire list of possible values, together with their symbolic constants, by looking at the ChartChartTypeEnum list of enumerated values with Object Browser. When the user chooses a name in the combo box, the code sets the Type property of the chart to the corresponding numeric value. It's that simple to make quick changes to a chart on screen.

Note that these changes are independent of the real-time editing capabilities of the PivotChart. New users might choose to not display the PivotChart toolbar (which allows access to all the formatting properties of the chart), but to use their own form to provide a limited amount of safe customization.

Manipulating Chart Details

FIGURE 4 shows a sample form that digs beneath the ChChart object to manipulate some of the individual objects that make up the chart. The example in Listing One shows the code behind the Chart Designer form. It uses a relatively simple strategy to keep the PivotChart in synch with the values shown on the Chart Designer form. When opened, the Chart Designer form retrieves values from the appropriate spots in the object model to populate its user interface. It then traps Change events on its own controls and writes any changes back to the object model. Any changes made to objects in the ChartSpace hierarchy take effect immediately.

FIGURE 4: Using the Chart Designer form.

You can get a feel for how the ChartSpace objects fit together by inspecting the code in Listing One in conjunction with the object model diagram shown in FIGURE 1. For example, the HasLegend and HasTitle properties of the ChChart object control whether a legend or an overall title for the chart are displayed. Note that you can only retrieve the ChChart.Title object if the HasTitle property is set to True; otherwise, there's nothing there to retrieve, and code that attempts to do so will fail.

Another point to note in this code is that although both the category axis and the value axis are represented by ChAxis objects, those two objects have slightly different sets of valid properties. For example, the tick spacing on the category axis is set via its TickMarkSpacing property, whereas the tick spacing on the value axis is set via its MajorUnit property. If you're writing general-purpose code that accepts a passed-in ChAxis object, you can determine which axis you're dealing with by inspecting the ChAxis.Type property.

The code that sets the color and pattern of the back wall (a ChSurface object) uses a method (in this case, SetTwoColorGradient) rather than manipulating properties. This is generally how the ChartSpace object model handles setting colors and textures. There are six methods that are used with the ChInterior object to draw 3D surfaces: SetSolid, SetOneColorGradient, SetTwoColorGradient, SetPresetGradient, SetPatterned, and SetTextured. Note that the combo boxes on the sample form used to choose the gradient colors use names to represent colors. Any HTML color name is valid here, as well as the standard RGB color numbers used throughout Windows.

Changing Points

FIGURE 5 shows a final example that digs even deeper into the object model by manipulating the properties of individual data points. This form allows you to pick a date, then highlights the data for that date. Alternatively, you can click the Strobe Colors button to highlight the bars in turn, round-robin fashion. Listing Two shows the code behind the color strober form. Most of it is similar to code you've already seen in this article, but a few points deserve comment.

FIGURE 5: A form to change the color of data points.

First, take a look at the code in the Form_Open event that adds the values from the category axis to the combo box on the form. This code takes advantage of one of the links between the ChartSpace object model and the PivotTable object model. When Access creates a PivotChart view of a form, it binds the view to data stored in a PivotTable based on the data in the form's recordsource. This code uses the PivotAxis property of the ChCategoryLabels object to drill far enough into the PivotTable object model to retrieve some data.

Second, note the code that modifies the color of individual points. Because this particular chart is displayed using a 3D type, the color of a point is controlled through a ChInterior object attached to the point. To change the colors of points in sequence, the form uses an OnTimer event to change the color of the ChInterior object from -1 (the default) to chartreuse and back. Although this takes fewer than 20 lines of code, it leads to a rather striking visual effect. I call such simple and flashy techniques "bossware." They're good for impressing people who don't know how easy things like the ChartSpace object model make life for the developer.

A Final Word of Caution

This article only touches on the depth and complexity of the ChartSpace object model. But every facet of a chart's appearance, from the width of the lines between points on a 2D chart to the texture of the side wall on a 3D chart, are open to customization through the object model and VBA. Don't let this power overwhelm your design sense. Remember, the point of a chart is to convey data to the user of your database. While techniques like highlighting a point in a contrasting color or setting titles at run time can enhance the data, be careful to not spend time customizing just for the sake of demonstrating that you can.

Mike Gunderloy (mailto:MikeG1@larkfarm.com) is an independent developer and author living in eastern Washington state. He's the co-author of SQL Server Developer's Guide to OLAP (SYBEX, 2001) and the forthcoming Access 2002 Developer's Handbook (SYBEX, 2001) and ADO Developer's Handbook (SYBEX, 2001).

Begin Listing One - frmChartDesigner

  Option Compare Database
Option Explicit
  
Private oChart As ChChart
Private oCategoryAxis As ChAxis
Private oValueAxis As ChAxis
Private oBackWall As ChSurface
  
Private Sub cboCategoryAxisCaptionSize_Change()
  oCategoryAxis.Title.Font.Size = _
    cboCategoryAxisCaptionSize.Text
End Sub
  
Private Sub cboTitleFontSize_Change()
  oChart.Title.Font.Size = cboTitleFontSize.Text
End Sub
  
Private Sub cboValueAxisCaptionSize_Change()
  oValueAxis.Title.Font.Size = cboValueAxisCaptionSize.Text
End Sub
  
Private Sub chkHasLegend_Click()
  oChart.HasLegend = chkHasLegend
End Sub
  
Private Sub chkHasTitle_Click()
  oChart.HasTitle = chkHasTitle
End Sub
  
Private Sub cmdApply_Click()
   On Error Resume Next
  oBackWall.Interior.SetTwoColorGradient _
    cboGradientStyle, cboGradientVariant, _
     Replace(cboColor1, " ", ""), _
    Replace(cboColor2, " ","") 
End Sub
  
Private Sub Form_Open(Cancel As Integer)
  ' Open the PivotChart form and set persistent
  ' references to the objects we'll be working with. 
   If Not CurrentProject.AllForms("frmEggs").IsLoaded Then
    DoCmd.OpenForm "frmEggs", acFormPivotChart
   End If
  
   Set oChart = Forms("frmEggs").ChartSpace.Charts(0) 
  oChart.Type = chChartTypeColumnClustered3D
   Set oCategoryAxis = oChart.Axes(0) 
   Set oValueAxis = oChart.Axes(1) 
   Set oBackWall = oChart.PlotArea.BackWall
  
   ' Synch the UI to the chart. 
  chkHasLegend = oChart.HasLegend
  chkHasTitle = oChart.HasTitle
   If oChart.HasTitle Then
    txtTitle = oChart.Title.Caption
    cboTitleFontSize = oChart.Title.Font.Size
   End If
  
  txtCategoryAxisTickMarkSpacing = _
    oCategoryAxis.TickMarkSpacing
  txtCategoryAxisTickLabelSpacing = _
    oCategoryAxis.TickLabelSpacing
  oCategoryAxis.HasTitle = True
  txtCategoryAxisCaption = oCategoryAxis.Title.Caption
  cboCategoryAxisCaptionSize = _
    oCategoryAxis.Title.Font.Size
  
  txtValueAxisMinimum = oValueAxis.Scaling.Minimum
  txtValueAxisMaximum = oValueAxis.Scaling.Maximum
  txtValueAxisTickSpacing = ValueAxis.MajorUnit
  oValueAxis.HasTitle = True
  txtValueAxisCaption = oValueAxis.Title.Caption
  cboValueAxisCaptionSize = oValueAxis.Title.Font.Size
  
   ' Some of these properties might not exist. 
   On Error Resume Next
  cboGradientStyle = oBackWall.Interior.GradientStyle
  cboGradientVariant = oBackWall.Interior.GradientVariant
  cboColor1 = oBackWall.Interior.Color
  cboColor2 = oBackWall.Interior.BackColor
End Sub
  
Private Sub txtCategoryAxisCaption_Change()
  oCategoryAxis.Title.Caption = txtCategoryAxisCaption.Text
End Sub
  
Private Sub txtCategoryAxisTickLabelSpacing_Change()
  oCategoryAxis.TickLabelSpacing = _
    txtCategoryAxisTickLabelSpacing.Text
End Sub
  
Private Sub txtCategoryAxisTickMarkSpacing_Change()
  oCategoryAxis.TickMarkSpacing = _
    txtCategoryAxisTickMarkSpacing.Text
End Sub
  
Private Sub txtTitle_Change()
  oChart.Title.Caption = txtTitle.Text
End Sub
  
Private Sub txtValueAxisCaption_Change()
  oValueAxis.Title.Caption = txtValueAxisCaption.Text
End Sub
  
Private Sub txtValueAxisMaximum_Change()
  oValueAxis.Scaling.Maximum = txtValueAxisMaximum.Text
End Sub
  
Private Sub txtValueAxisMinimum_Change()
  oValueAxis.Scaling.Minimum = txtValueAxisMinimum.Text
End Sub
  
Private Sub txtValueAxisTickSpacing_Change()
  oValueAxis.MajorUnit = txtValueAxisTickSpacing.Text
End Sub

End Listing One

Begin Listing Two - frmColorStrober

  Option Compare Database
Option Explicit
  
Dim oChart As ChChart
Dim oSeries1 As ChSeries
Dim oSeries2 As ChSeries
  
Private mfStrobingColors As Boolean
Private mintPoint As Integer
  
Private Sub cboDates_Change()
  oSeries1.Points(cboDates.ListIndex).Interior.Color = _
    "Yellow" 
  oSeries2.Points(cboDates.ListIndex).Interior.Color = _
    "Yellow" 
End Sub
  
Private Sub cmdStrobeColors_Click()
   If mfStrobingColors Then
    oSeries1.Points(mintPoint).Interior.Color = -1
    oSeries2.Points(mintPoint).Interior.Color = -1
    mfStrobingColors = False
    cmdStrobeColors.Caption = "Strobe Colors" 
   Else
    mfStrobingColors = True
    cmdStrobeColors.Caption = "Stop Strobing Colors" 
   End If
End Sub
  
Private Sub Form_Open(Cancel As Integer)
   Dim intI As Integer
  
   If Not CurrentProject.AllForms("frmEggs").IsLoaded Then
    DoCmd.OpenForm "frmEggs", acFormPivotChart
   End If
  
   Set oChart = Forms("frmEggs").ChartSpace.Charts(0) 
  oChart. Type = chChartTypeColumnClustered3D
   Set oSeries1 = oChart.SeriesCollection(0) 
   Set oSeries2 = oChart.SeriesCollection(1) 
  
   For intI = 0 To oChart.Axes(0).CategoryLabels. _
        PivotAxis.SourceAxis.FieldSets(0).Members.Count - 1
    cboDates.AddItem oChart.Axes(0).CategoryLabels. _
      PivotAxis.SourceAxis.FieldSets(0).Members(intI)._ 
      Caption
   Next intI
End Sub
  
Private Sub Form_Timer()
   Dim oLastPoint1 As ChPoint
   Dim oThisPoint1 As ChPoint
   Dim oLastPoint2 As ChPoint
   Dim oThisPoint2 As ChPoint
  
   Set oLastPoint1 = oSeries1.Points(mintPoint) 
   Set oLastPoint2 = oSeries2.Points(mintPoint) 
  mintPoint = mintPoint + 1
   If mintPoint = oSeries1.Points.Count Then
    mintPoint = 0
   End If
   Set oThisPoint1 = oSeries1.Points(mintPoint) 
   Set oThisPoint2 = oSeries2.Points(mintPoint) 
  
   If mfStrobingColors Then
    oThisPoint1.Interior.Color = "Chartreuse" 
    oLastPoint1.Interior.Color = -1
    oThisPoint2.Interior.Color = "Chartreuse" 
    oLastPoint2.Interior.Color = -1
   End If
End Sub

End Listing Two