5

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.

braX
  • 11,506
  • 5
  • 20
  • 33
got2nosth
  • 578
  • 2
  • 8
  • 27
  • Yes. There is. Have you tried it yet? – david Feb 06 '14 at 08:36
  • @david yes, I have tried several times in different ways. But whenever I tried to assign value to the RowSource property, I got the error 2455 – got2nosth Feb 06 '14 at 10:09
  • You can dynamically replace a chart with a new chart that has a different rowsource, but your suggested solution is normally better – david Feb 08 '14 at 00:16
  • I set a chart RowSource with an SQL statement that has WHERE clause referencing a textbox on report: `SELECT Field2, Field3 FROM Table WHERE Field1=[textbox];`. – June7 Oct 22 '22 at 07:07

2 Answers2

5

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.

got2nosth
  • 578
  • 2
  • 8
  • 27
  • Equivilantly, you can use a local table as the data source, and refill it for every group/page/item/chart. It's partucularly appropriate where you have a lot of charts based on simple subsets of complex data. Depending on report design, you may not need to requery - in some places the charts will requery automatically – david Feb 08 '14 at 00:08
  • Thanks @david I leaned something new. – got2nosth Feb 10 '14 at 00:53
1

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
Az1807
  • 23
  • 6