ACC2002: PivotChart Series Formatting Is Lost in Linked Subforms (285848)


The information in this article applies to:

  • Microsoft Access 2002
This article was previously published under Q285848
Advanced: Requires expert coding, interoperability, and multiuser skills.

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

SYMPTOMS

When you scroll through records on a form that contains a linked subform in PivotChart view, you notice that the formatting for individual series in the PivotChart is lost. For example, trendlines, error bars, and data labels are discarded, and formatting properties such as color and border weight are reset to their defaults.

CAUSE

The PivotChart Web Component is architected to reset formatting when there is no data to format in a series.

RESOLUTION

Write custom Visual Basic for Applications code to programmatically format the members of the PivotChart from the Current event of the main form. To programmatically format series members of a subform in PivotChart view, follow these steps:
  1. Open the sample database Northwind.mdb.
  2. On the Insert menu, click Form.
  3. In the New Form dialog box, click AutoForm: Columnar, click the Employees table in the Choose the table or query where the object's data comes from box, and then click OK. The new form opens in form view.
  4. On the View menu, click Design View.
  5. Delete all controls except the EmployeeID, FirstName, and LastName controls.
  6. In the Database window, drag the Sales Analysis Subform2 form to the new form.
  7. Set the LinkMasterFields and LinkChildFields properties of the subform to LastName.
  8. On the View menu, click Code.
  9. On the Tools menu, click References.
  10. In the References dialog box, click Browse, and locate the C:\Program Files\Common Files\Microsoft Shared\Web Components\10\OWC10.DLL file.
  11. Click Open to select the file.
  12. Click OK to close the References dialog box.
  13. Add the following code to the module of the form:
    Private Sub Form_Current()
   Dim objChartSpace As OWC10.ChartSpace
   Dim objChart As OWC10.ChChart
   Dim objSeries As OWC10.ChSeries
    
   Set objChartSpace = Me.PivotChart.Form.ChartSpace
   Set objChart = objChartSpace.Charts(0)
    
   For Each objSeries In objChart.SeriesCollection
      With objSeries
         'Set Border weight to Thick    
         .Border.Weight = owcLineWeightThick

         'Set Border Color to Black
         .Border.Color = 0

         'Set Border style to Solid
         .Border.DashStyle = chLineSolid
             
         'Add an Error bar if one doesn't exist
         If .ErrorBarsCollection.Count = 0 Then
            .ErrorBarsCollection.Add
         End If

         'Add a trendline if one doesn't exist
         If .Trendlines.Count = 0 Then
            .Trendlines.Add

             'Set trendline color to Aquamarine
            .Trendlines.Item(0).Line.Color = "AquaMarine"


             'Turn off Trendline equation and R-Squared value
            .Trendlines.Item(0).IsDisplayingEquation = False
            .Trendlines.Item(0).IsDisplayingRSquared = False
         End If
      End With
   Next
End Sub
  14. On the File menu, click Close and Return to Microsoft Access.
  15. On the File menu, click Save.
  16. On the View menu, click Form View.
  17. Scroll through each record on the main form.
Note that the PivotChart contains an error bar and trendline, and that individual series members retain their formatting.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

When multiple series of data are being displayed in a PivotChart, it is possible to customize the formatting of each individual series. However, when the PivotChart is filtered where a series is not displayed, the formatting for that series is reset to the defaults.

For example, suppose the linked subform is displaying data for "Davolio." In this case, all other series do not exist within the PivotChart. Because the series members no longer exist, the PivotChart Web component has discarded their formatting. When you scroll to another record on the main form, the series is re-created and contains the default formatting.

Steps to Reproduce the Behavior

  1. Open the sample database Northwind.mdb.
  2. Open the Sales Analysis Subform2 form in PivotChart view.
  3. On the View menu, click Properties.
  4. In the property sheet, click the General tab, click the arrow in the Select combo box, and then click Buchanan.
  5. Under the Add section, click Add Errorbar.
  6. Repeat steps 4 through 5 for other series of the chart (Callahan, Davolio, Dodsworth, Fuller, and King). Note that each series now has an error bar.
  7. Close the form.
  8. On the Insert menu, click Form.
  9. In the New Form dialog box, click AutoForm: Columnar, click the Employees table in the Choose the table or query where the object's data comes from box, and then click OK. The new form opens in Form view.
  10. On the View menu, click Design View.
  11. Delete all controls except the EmployeeID, FirstName, and LastName controls.
  12. In the Database window, drag the Sales Analysis Subform2 form to the new form.
  13. Set theLinkMasterFields and LinkChildFields properties of the subform to "LastName."
  14. On the View menu, click Form View. Note that the PivotChart subform displays a chart with error bars as you would expect.
  15. On the Edit menu, point to Go To, and then click Next.
Note that the PivotChart subform loses its formatting for individual series members. The error bars have been removed, and each series is displayed using the same color.
Modification Type:MajorLast Reviewed:11/6/2003
Keywords:kbbug kbnofix KB285848
©2003 Microsoft Corporation. All rights reserved.