ACC97: How to Change a Chart's Colors Dynamically

• Microsoft Access 97

SUMMARY

Advanced: Requires expert coding, interoperability, and multi-user skills.

The colors of a chart can be changed dynamically to correspond with the data in the current record of the form or report or to reflect special conditions of the data. During the OnCurrent, OnFormat, and OnPrint events, the data displayed in a chart is not normally synchronized with the data in the current record of the form or report. Because you must use a Visual Basic for Applications procedure to clear and reload the data of a chart every time one of these events occurs, you can use the same procedure to change the colors of each data point on the graph.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and 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 needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please seethe following page on the World Wide Web:

   http://www.microsoft.com/supportnet/refguide/

MORE INFORMATION

The following example demonstrates how to create a chart whose colors are dynamic.

CAUTION: Following the steps in this example will modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and perform these steps on a copy of the database.

Modify the Query Product Sales for 1995

1. Start Microsoft Access and open the query "Product Sales for 1995" in the sample database Northwind.mdb.

2. Add the Shippers table to the query. Microsoft Access automatically creates a join between the ShipVia field in the Orders table and the ShipperID field in the Shippers table.

3. Drag the CompanyName field from the Shippers table to the first empty column on the query design grid. Leave the Total row for this field set to the default value of Group By.

4. Save and close the query.

Create the Chart

1. Create a new form based on the Categories table. Include only the CategoryID and CategoryName fields in the detail section of the form.

2. On the Insert menu, click Chart to create a chart in the detail section of the form.

3. In the first screen of the Chart Wizard, click Queries and select Product Sales for 1995. Click Next.

4. Move the CompanyName field from the Available Fields list to the Fields for Chart list. Then move the ProductSales field from the Available Fields list to the Fields for Chart list. Click Next.

5. Click Next in the chart type screen to select the default type of Column Chart.

6. In the layout screen of the Chart Wizard, click Next.

7. Click OK if you receive the following message

   You must add field CategoryID if you want to use this link.

8. In the Form Fields list, click CategoryName; in the Chart Fields list, click CategoryName, and then click Finish.

9. On the View menu, click Properties and change the Name property of the chart to "chtColorChart" (without the quotation marks).

10. Save the form as frmChartColors and switch to Form view. Notice that the columns corresponding to each of the three shippers are all the same color.

Create the Event Procedure

1. Open frmChartColors in Design view, and on the View menu, click Code.

2. Type the following lines in the Declarations section of the class module:

         Option Compare Database
         Option Explicit
         Option Base 1
   

   Note that the first two lines may already be present in the Declarations section.

3. On the Tools menu, click References.

4. In the References dialog box, scroll down the Available References list and select the Microsoft Graph 8.0 Object Library. Click OK, and then close the module.

5. On the Edit menu, click Select Form; then, Click Properties on the View menu to open the property sheet of the form.

6. Click the OnCurrent property box, and then click the Build (...) button. In the Choose Builder dialog box, click Code Builder, and then click OK.

7. Set the form's OnCurrent property to the following event procedure:

          Private Sub Form_Current()
   

             Dim chtObj As Object, strRowSource As String
             Dim rsRowSourceFiltered As Recordset
             Dim intMaxShippers As Integer
             Dim i As Integer, j As Integer
             Dim strArrShipperNames() As String
             Dim intArrShipperColors() As Integer
   
             ' The color integers are those that are used
             ' by the QBColor function to assign point colors.
   
             Const cFederal_Blue = 1
             Const cSpeedy_Green = 2
             Const cUnited_Red = 4
   
             intMaxShippers = 3
             ' Place all the shipper name values
             ' into an array.
             ReDim strArrShipperNames(intMaxShippers)
             strArrShipperNames(1) = "Federal Shipping"
             strArrShipperNames(2) = "Speedy Express"
             strArrShipperNames(3) = "United Package"
             ' Place the shipper color values
             ' into an array.
             ReDim intArrShipperColors(intMaxShippers)
             intArrShipperColors(1) = cFederal_Blue
             intArrShipperColors(2) = cSpeedy_Green
             intArrShipperColors(3) = cUnited_Red
   
             Set chtObj = Me!chtColorChart.Object
   
             ' In the chart's RowSource, insert a WHERE
             ' clause based on the value of the form
             ' field contained in the chart control's
             ' LinkMasterFields property.
             strRowSource = Left(Me!chtColorChart.RowSource, _
                InStr(Me!chtColorChart.RowSource, "GROUP BY") - 1) _
                & "WHERE " & Me!chtColorChart.LinkChildFields & _
                " = '" & Me(Me!chtColorChart.LinkMasterFields) & _
                "'" & " " & Right(Me!chtColorChart.RowSource, _
                Len(Me!chtColorChart.RowSource) _
                - InStr(Me!chtColorChart.RowSource, "GROUP BY") + 1)
             Set rsRowSourceFiltered = CurrentDb. _
                OpenRecordset(strRowSource, dbOpenSnapshot)
   
             ' Check to see if the filtered recordset has any records.
             If rsRowSourceFiltered.BOF And _
                   rsRowSourceFiltered.EOF Then
                MsgBox "There are no records to chart."
                Exit Sub
             End If
   
             ' Clear the rows required for the maximum number of
             ' data rows. The first row contains the column
             ' headers. Data rows being at the second row.
             With chtObj.Application.DataSheet
                For i = 1 To intMaxShippers
                   .Rows(i + 1).Include = False
                Next
             End With
   
             ' Ensure the RecordCount value is updated.
             rsRowSourceFiltered.MoveLast
             ' Populate the chart's datasheet with the
             ' filtered recordset, starting with the
             ' datasheet's second row.
             rsRowSourceFiltered.MoveFirst
             For i = 1 To rsRowSourceFiltered.RecordCount
                For j = 0 To rsRowSourceFiltered.Fields.Count - 1
                   ' Assign data to the datasheet cells starting
                   ' at row 2, column 1.
                   chtObj.Application.DataSheet. _
                      Cells(i + 1, j + 1).Value = _
                      rsRowSourceFiltered.Fields(j).Value
                Next
                rsRowSourceFiltered.MoveNext
             Next
   
             ' Loop through the recordset containing
             ' the chart's filtered RowSource.
             rsRowSourceFiltered.MoveFirst
             i = 0
             While Not rsRowSourceFiltered.EOF
                ' Index i synchronizes the Points collection
                ' index with the current recordset row.
                i = i + 1
                ' Loop through the shipper names array and look
                ' for a match with the field names of the chart's
                ' filtered RowSource.
                For j = 1 To UBound(strArrShipperNames) ' 1-based
                   ' The first field in the recordset contains
                   ' the shipper name. Some shippers may not
                   ' be in the filtered recordset.
                   If rsRowSourceFiltered.Fields(0).Value _
                      = strArrShipperNames(j) Then
                      ' Because every shipper has a corresponding color, the
                      ' arrays strArrShipperNames and intArrShipperColors
                      ' always contain the same number of elements.
                      ' Assign the color of the chart column, bar,
                      ' slice etc.
                      chtObj.SeriesCollection(1).Points(i). _
                         Interior.Color = _
                         QBColor(intArrShipperColors(j))
                   End If
                Next
                rsRowSourceFiltered.MoveNext
             Wend
   
          End Sub
   
   

8. Save the form and switch to Form View.

9. Notice that the three data points in the chart have different colors.

REFERENCES

For further information about manipulating a chart with Visual Basic for Applications code, see the file Vba8grp.hlp; this file is located in the Morehelp subfolder of the Valupack folder on your Microsoft Office 97 Professional Edition CD-ROM or your Microsoft Access 97 CD-ROM.