How to Dynamically Change the Rowsource of an Access Chart

dynamic, ms-access, ms-access-2003, mschart, vba

Solution

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.

Problem

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.

Original source