Search code examples
vbams-accessdynamicmschartms-access-2003

How to Dynamically Change the Rowsource of an Access Chart

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.

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.