Dynamically Changing Rowsource on Graph

[rmcompute]

I searched all over to find how to change the rowsource on a graph. What I
have found is that it can be done in design mode or by changing the source
query. I have multiple users on the application and would like to use the
same procedure I use for running reports. When I user runs a report they are
able to select their own search criterion. From this information a query is
dynamically created using the user's id: qryModelRpt_smithj. This query name
is saved to a variable which can be read in the open event of the report:
Me.RecordSource = strQueryID. I would like to do the same for the graph but
cannot locate where to put the rowsource code. Does anyone know if it can be
done in this way: Me.RowSource = strQueryID with strQueryID containing the
correct query to use for this graph?

 

[Allen Browne]

You could try setting the RowSource for the graph in Report_Open. Note
that's not Me, as you are not setting the (non-existent) RowSource of the
report, but of the chart object.

Alternatively, it might be possible to have the graph's query read its
criteria from a form, e.g.:
[Forms].[Form1].[Text0]

If you are desperate, it would even be possible to build the entire SQL
statement for the chart's query, and assign it to the SQL property of the
QueryDef, e.g.:
Dim strSql As string
strSql = "SELECT ...
CurrentDb.QueryDefs("Query1").SQL = strSql

 

[rmcompute]

Thank you for the response. I would like to continue pursuing the first
solution you suggested of using the Report_Open event. I typed the name of
the graph object and a period after it in the open event:

RptChart1.

After this, it listed all of the possible values which could be used such as:

Application
Column
Controls
Dropdown
etc.

I explored many of them and could not locate rowsource. Do you know where
it might be found?

Thanks.

You could try setting the RowSource for the graph in Report_Open. Note
that's not Me, as you are not setting the (non-existent) RowSource of the
report, but of the chart object.

Alternatively, it might be possible to have the graph's query read its
criteria from a form, e.g.:
[Forms].[Form1].[Text0]

If you are desperate, it would even be possible to build the entire SQL
statement for the chart's query, and assign it to the SQL property of the
QueryDef, e.g.:
Dim strSql As string
strSql = "SELECT ...
CurrentDb.QueryDefs("Query1").SQL = strSql

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I searched all over to find how to change the rowsource on a graph. What I
have found is that it can be done in design mode or by changing the source
query. I have multiple users on the application and would like to use the
same procedure I use for running reports. When I user runs a report they
are
able to select their own search criterion. From this information a query
is
dynamically created using the user's id: qryModelRpt_smithj. This query
name
is saved to a variable which can be read in the open event of the report:
Me.RecordSource = strQueryID. I would like to do the same for the graph
but
cannot locate where to put the rowsource code. Does anyone know if it can
be
done in this way: Me.RowSource = strQueryID with strQueryID containing the
correct query to use for this graph?

 

[Allen Browne]

With the report open in design view, click (once) on the graph.

In the Properties sheet, I expect you will see it is named something like
OLEUnbound0. Now on the Data tab, you should be able to see its RowSource.
If so, you should be able to code:
Me.OLEUnbound0.RowSource = "SELECT ...

That should work, even though the Intellisense is not aware of the
properties that apply to this particular OLE Object.

 

[rmcompute]

I went into design view and clicked once on the graph. In the properties
sheet, the name was OLEUnbound0 and the Row Source contained the SQL used by
the graph. I copied the SQL and created the following code in the open event
of the form:

Me.OLEUnbound0.RowSource = "TRANSFORM Sum([MIF]) AS [SumOfMIF] SELECT
(Format([RptPeriod],'MMM YY')) FROM [qryGPHMIF] GROUP BY
(Year([RptPeriod])*12 + Month([RptPeriod])-1),(Format([RptPeriod],'MMM YY'))
PIVOT [ModelNum];"

I received the following error:
Run-Time error ‘2455’
You entered and expression that has an invalid reference to the property
RowSource.

I changed to the following code to determine if the RowSource could be read
and got the same error: MsgBox Me.OLEUnbound0.RowSource.

I moved the code to the Activate event and got the same error.

Can it be that the RowSource cannot be referenced in this way?

 

[Allen Browne]

Okay: that seems to work in design view only.

Take one of the other approaches suggested earlier.

 

[rmcompute]

I used this solution and it seems to be working fine:

strSql = "SELECT ...
CurrentDb.QueryDefs("Query1").SQL = strSql

Thanks for the help.

 

[David W. Fenton]

I used this solution and it seems to be working fine:

strSql = "SELECT ...
CurrentDb.QueryDefs("Query1").SQL = strSql

Right -- altering a stored QueryDef is the only way to do this kind
of thing in a graph. This has been the case forever (I first
encountered in an Access 2 app in 1996).

 

[rmcompute]

Thanks for the response. I tried everything and was getting pretty
frustrated. Hopefully others will read this before going through the same.

 

[The Merg]

This is exactly what I need to do as well. Where do you put the code for the
QueryDefs.SQL property?

Thanks,
Merg