Iteratively referring in code to a chart name that varies

[robert demo via AccessMonster.com]

In code, how do I refer to chart names on a report that are exactly the
same except for the last number?

Example:

Usage_Chart1, Usage_Chart2, Usage_Chart3, etc.

I would like to be able to iterate through the charts to set the RowSource
property.

For j = 1 to 3
Me("Usage_Chart" & j).RowSource = 'Some Select statement'
Next j

Me("Usage_Chart" & j) seems to be generating the following error:

"You entered an expression that has an invalid reference to the property
RowSource'"

Thanks for any help.

 

[SteveS]

In code, how do I refer to chart names on a report that are exactly the
same except for the last number?

Example:

Usage_Chart1, Usage_Chart2, Usage_Chart3, etc.

I would like to be able to iterate through the charts to set the RowSource
property.

For j = 1 to 3
Me("Usage_Chart" & j).RowSource = 'Some Select statement'
Next j

Me("Usage_Chart" & j) seems to be generating the following error:

"You entered an expression that has an invalid reference to the property
RowSource'"

Thanks for any help.

Robert,

I am using Access 2003 in A2K format. The code above works for me... except
that 'Some Select statement' needs to be in double quotes instead of single
quotes.

I created 3 charts, used your names and added a button with the folllowing
code:

'***********
Private Sub Command3_Click()
Dim j As Integer

For j = 1 To 3
Me("Usage_Chart" & j).RowSource = "SELECT qryTestUpdate.FileNum,
qryTestUpdate.LoanAmount, Sum(qryTestUpdate.LoanAmount) AS SumOfLoanAmount,
qryTestUpdate.Commission FROM qryTestUpdate GROUP BY qryTestUpdate.FileNum,
qryTestUpdate.LoanAmount, qryTestUpdate.Commission;"
Me.Requery
Next j
End Sub
'************88

HTH

 

[robert demo via AccessMonster.com]

It turns out that I was wrong about the problem. The error message was for
real, but I didn't understand what caused it.

Apparantly, the RowSource property for a chart can't be set in a report.
Did you test your code in a form or report? Setting the RowSource property
in a form works fine but not in a report (at least in Access 2000 and 2002)
.. Other people in on-line forums have tested this and come to the same
conclusion for reports.

Me("Usage_Chart" & j) works fine in either forms or reports.

The lack of double quotes for the SQL statement was my mistake when
posting. They are included in my code.

However, now I have no way of setting the RowSource property in code.
Oddly enough, though, if you click on the Data tab of the Properties sheet
of the report chart there is a RowSource line that can be set. If you're
in a report code module and are typing Me.Usage_Chart1. VBA will come up
with a list of properties to select from. RowSource is not on that list for
reports but is for forms, at least with my versions of Access.

So it looks like the only thing I can do is to create a query, set the
chart RowSource property in the property sheet to the query, and then
change the query in the report module code.

Thanks.