SeriesCollection order changing for no reason

[rick]

I have a problem which I do not understand as it makes no sense to me
what so ever.

I am using the following piece of code to manipulate a pivot chart in
access.

It is supposed to cycle through all the Series and see how many rows of
data there are for that series in [Qry Results - Data - Prices -
All]. For series where there is only row of data, the line type should
be changed to chChartTypeLineMarkers. All other instances should be set
to chChartTypeLine.

This should be simple enough. However, whenever I change the
SeriesCollection().type property, the series order changes. i.e.
SeriesCollection(i).Caption is different on the 2 debug.print lines.
Can you please tell me of a way of changing the .Type without
re-ordering the Collection.

If Me.Average_Chart.Visible = True Then

With Me.Average_Chart.Form.ChartSpace.Charts(0)

For i = .SeriesCollection.Count - 1 To 1 Step -1

Debug.Print .SeriesCollection(i).Caption

If .Legend.LegendEntries(i).Visible Then
If DCount("[" & .SeriesCollection(i).Caption & "]", _
"[Qry Results - Data - Prices - All]") = 1 Then

.SeriesCollection(i).Type = chChartTypeLineMarkers

Else

.SeriesCollection(i).Type = chChartTypeLine

End If

End If

Debug.Print .SeriesCollection(i).Caption

Next i

End With

End If


Please help - This is driving me mad!

 

[Alvin Bruney - ASP.NET MVP]

This is by design. Several events can cause the series to be re-ordered.
Re-rendering is one culprit that may force the series to be re-ordered. You
best work-around is to force a particular order on the series collection
thru code.

--
Warm Regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
The Microsoft Office Web Components Black Book with .NET
Now Available @ www.lulu.com/owc
Professional VSTO 2005 - Wrox/Wiley 2006
Blog: http://msmvps.com/blogs/Alvin/

 

[rick]

Alvin,

What exactly do you have in mind as the method for forcing the order?

 

[Alvin Bruney - ASP.NET MVP]

I posted some code a few moons ago on how to do this. Essentially, you
iterate the series collection and re-arrange the indices according to your
own pattern.

--
Warm Regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
The Microsoft Office Web Components Black Book with .NET
Now Available @ www.lulu.com/owc
Professional VSTO 2005 - Wrox/Wiley 2006
Blog: http://msmvps.com/blogs/Alvin/