Adding a series to a Chart Object in Access MS Chart
Posted on 2011-03-20
I have a chart problem. It must be easy to do, but I cannot seem to figure out how to do it.
I have a chart control in an Access 2007 VBA application. At runtime, the chart is created, and data is added.
The user is able to select whether they want 1, 2, or 3 lines on the chart. It works fine if they only want 1 or 2 lines, but at runtime, when II attempt to add another series, I cannot.
The code looks like this:
Dim ch As Chart
Dim cs As SeriesCollection
Dim ser1 As Series
Dim ser2 As Series
Dim ser3 As Series
'I get the chart series from the Chart object, and set it to my series
Set ch = Me.chart1.Object.Application.Chart
Set cs = ch.SeriesCollection
Set ser1 = cs.Item(1)
Set ser2 = cs.Item(2)
'fail on this next line
Set ser3 = cs.Item(3)
I guess my question is: How do I make sure there are 3 items in the SeriesCollection? Or, How can I add a 3rd Item to the SeriesCollection if count < 3 ? This is in Access 2007, not Excel.
Thanks for your help.