Dynamically Changing Rowsource on Graph
in [Reports]
|
From: rmcompute on 6 Jul 2008 09:05 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?
From: Allen Browne on 6 Jul 2008 13:46 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 Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "rmcompute" <rmcompute(a)discussions.microsoft.com> wrote in message news:FB49C167-F535-444D-9A18-EFDF41D2D329(a)microsoft.com... >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?
From: rmcompute on 6 Jul 2008 19:37 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. "Allen Browne" wrote: > 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 > Tips for Access users - http://allenbrowne.com/tips.html > Reply to group, rather than allenbrowne at mvps dot org. > > "rmcompute" <rmcompute(a)discussions.microsoft.com> wrote in message > news:FB49C167-F535-444D-9A18-EFDF41D2D329(a)microsoft.com... > >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? > >
From: Allen Browne on 6 Jul 2008 23:39 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. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "rmcompute" <rmcompute(a)discussions.microsoft.com> wrote in message news:FBDC8777-8462-4FEA-9222-189774A66E10(a)microsoft.com... > 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.
From: rmcompute on 7 Jul 2008 19:06 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" wrote: > 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. > > -- > Allen Browne - Microsoft MVP. Perth, Western Australia > Tips for Access users - http://allenbrowne.com/tips.html > Reply to group, rather than allenbrowne at mvps dot org. > > "rmcompute" <rmcompute(a)discussions.microsoft.com> wrote in message > news:FBDC8777-8462-4FEA-9222-189774A66E10(a)microsoft.com... > > 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. > >
|
Next
|
Last
Pages: 1 2 Prev: change a reports control backcolor Next: Case statement Excellent Idea |