Dynamically add a series to a chart in Access, using VBA

[TimPen]

In Access 2007 I have a report that contains a chart.
I need to be able to change the series of the report, because sometimes it can have one series, other times three.
What I have is the following:

Dim Rpt As Report
Dim grphChart As Graph.Chart
Dim recSource As String

DoCmd.OpenReport ReportName, acViewDesign

Reports(ReportName).graph0.RowSource = "qryDataSource"

Set Rpt = Reports(ReportName)

Set grphChart = Rpt(ChartObjectName).Object

grphChart.Activate

grphChart.SeriesCollection(0).Add
grphChart.SeriesCollection(0).Caption = "Series Number"

I keep getting an error 438 - object does not support this property or method

What I am doing wrong?

 

[PHV]

I keep getting an error 438
Which line of code is highlighted when in debug mode ?

BTW, what is ChartObjectName ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TimPen]

Which line of code is highlighted when in debug mode ?

grphChart.SeriesCollection.Add

BTW, what is ChartObjectName ?
the name of the chart on the report (Graph0)

 

[TimPen]

I am know desperate for a solution. Even happy to pay for it!

 

[TimPen]

Can I ask the following:

I am currently trying to create the chart in an Access report.
Should I rather create the chart in Excel? Would this be the preferred method?

 

[dhookom]

I always place code in the On Format or On Print of the section containing the object. If the chart is in the Detail section then place your code in the On Format event of the Detail section. Most of the time I record graph modification code in Excel and then paste/modify the code in my On Format event in the report.



Duane
Hook'D on Access
MS Access MVP

 

[TimPen]

I have abandend trying to create the graph / chart in Access and am creating it in Excel from Access
In hindsight I should have done this from the beginning