How to Dynamically Change the Rowsource of an Access Chart

Question

I would like to know if there is any way I can set the rowsource property of a chart in my report at run time.

I intend to have a chart in my report's group header section. The rowsource of this chart should be updated according to the group header's value.

I got the error 2455 - invalid reference to the property RowSource when I tried to do this in VBA.

I am using Access 2003.

Thank you.

Answer 1

I just got an inspiration after searching over the internet for some time. Here is the solution I currently implement.

Firstly, it is true that the rowsource property of a chart cannot be changed programmatically at run time. However, what we can do is to set the rowsource property to be a Query object and later update this query object in VBA.

Here is part of my code.

CurrentDb.QueryDefs("myQuery").SQL = "a new query"
Me.myChart.Requery

I have set my chart's row source to a query object named "myQuery". I placed the above code in the Format event of my group header, so every time when the group header is loaded I can use the value of my group header to update the Query object.

Answer 2

Another approach would be to open the form or report the chart is embedded in two steps. In the example below I am using a report, but it works just as fine with forms:

• First step: open report in design view but hidden mode. Now the chart row source can be edited (because you are in design view) but the process is invisible (because you are in hidden mode).
• Second step: save and close hidden report and re-open it now in a "visible" mode

'report name
strReportmName = "SomeReportName"

'open report in design view but hidden
DoCmd.OpenReport strReportmName , acViewDesign, , , , acHidden

'edit chart RowSource
strSQL = "TRANSFORM Sum(Cabecas) AS SomaDeCabecas " & _
    "SELECT Data " 
     ...etc...
    "PIVOT Categoria In (" & Chr(34) & strTitColunas & Chr(34) & ")"

'update chart RowSource
Reports![SomeReportName].Controls![SomeChartName].RowSource = strSQL

'Save report with edited RowSource
DoCmd.Close acReport, strReportmName , acSaveYes

're-open it in normal, visible mode
DoCmd.OpenReport strReportmName , acViewPreview