This question has been asked before without any answer. Has anyone been able to set the rowsource property of the embedded graph with VB. I've tried to create a sql and simply set it equal to the rowsource for the embedded graph. When I execute the report, I receive the message "Run time '438' - Object doesn't support this property or method". Normally, when I create a graph I create a query and then reference that query when the graph is created. I then manipulate the query vi VB to get the dynamic data display, but WHY can't I simply set the rowsource = to the sql string and eliminate all this overhead? Thanks ever so much for responding.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Embedded Graph based on VB code 1
- Thread starter ca268339
- Start date
- Thread starter
- #3
First, thanks for the reply. Yes, I'm putting the SQL equivalent of a saved query directly into the rowsource property. For example:
dim sqlstr as string
sqlstr = "SELECT [FrameRelay-DLCI-Port].CustomerName, OVDB_ATTFRAMESTATS.RECORDING_WEEK, FROM [FrameRelay-DLCI-Port] ORDER BY [FrameRelay-DLCI-Port].CustomerName, OVDB_ATTFRAMESTATS.RECORDING_WEEK "
me.reportgraphobjectname.rowsource=sqlstr
When the preceding statement is executed, I receive the error. This string is manipulated by a form that builds the unique query.
However, if I initially set the graph's rowsource to some query name and then delete and recreate that query within the VB code (using DeleteObject and CreateQueryDef) the graph works fine. Requiring the overhead of deleting and recreating the query each and every time that the graph is generated seems to be a real wasted of time.
Again, thanks for the response.
dim sqlstr as string
sqlstr = "SELECT [FrameRelay-DLCI-Port].CustomerName, OVDB_ATTFRAMESTATS.RECORDING_WEEK, FROM [FrameRelay-DLCI-Port] ORDER BY [FrameRelay-DLCI-Port].CustomerName, OVDB_ATTFRAMESTATS.RECORDING_WEEK "
me.reportgraphobjectname.rowsource=sqlstr
When the preceding statement is executed, I receive the error. This string is manipulated by a form that builds the unique query.
However, if I initially set the graph's rowsource to some query name and then delete and recreate that query within the VB code (using DeleteObject and CreateQueryDef) the graph works fine. Requiring the overhead of deleting and recreating the query each and every time that the graph is generated seems to be a real wasted of time.
Again, thanks for the response.
i thought you meant the following, and now i'm questioning why you just don't do this:
in the graph's record source, why don't you just paste in your sql statement? i've never done exactly what you are doing; however, i put sql statement directly in the record source. how come you dont do this? why are you having to manipulate using vba? why, in your former method (the method that works) do you have to delete then recreate the query each time? i feel like i'm missing the point....
in the graph's record source, why don't you just paste in your sql statement? i've never done exactly what you are doing; however, i put sql statement directly in the record source. how come you dont do this? why are you having to manipulate using vba? why, in your former method (the method that works) do you have to delete then recreate the query each time? i feel like i'm missing the point....
- Thread starter
- #5
Yes, you are missing the point which is that the SQL is constantly changing based on selection criteria provided by the user in an associated form. You couldn't simply insert the SQL in the record source, because the "WHERE" clause is constantly changing for each report generation. The requirement to delete and re-add the query is needed as again the "WHERE" clause is changing.
Thanks again for the response.
Thanks again for the response.
-
1
- #6
CosmoKramer
Programmer
I beleive that you can only modify properties like RowSource in design view of a report. You can fake it out by trying something like this:
DoCmd.OpenReport "YourReport", acViewDesign
DoCmd.OpenReport "YourReport", acViewNormal
DoCmd.Close acReport, "YourReport", acSaveNo
This was the method needed in a previous thread where they wanted to change the ControlSource of a report. You might want to try it for a chart also....
Code:
dim sqlstr as string
sqlstr = "SELECT [FrameRelay-DLCI-Port].CustomerName, OVDB_ATTFRAMESTATS.RECORDING_WEEK, FROM [FrameRelay-DLCI-Port] ORDER BY [FrameRelay-DLCI-Port].CustomerName, OVDB_ATTFRAMESTATS.RECORDING_WEEK "DoCmd.OpenReport "YourReport", acViewDesign
Code:
me.reportgraphobjectname.rowsource=sqlstrDoCmd.OpenReport "YourReport", acViewNormal
DoCmd.Close acReport, "YourReport", acSaveNo
This was the method needed in a previous thread where they wanted to change the ControlSource of a report. You might want to try it for a chart also....
- Thread starter
- #7
Again, you're missing the point - but I really do appreciate your comments. What you've suggested would work if the report wasn't already open. On the OnOpen Event, I've coded a procedure whereby a Form is opened that allows the user to select criteria by which the report will be generated. (The form is opened in modal control, so nothing else will occur until the form is closed.) Once the form is closed, the criteria is then built into a SQL which I would like to attach directly to the embedded graph and not reference the graph's rowsource to a saved query. Because I can't figure out the incantation to put the SQL directly into the rowsource property for the graph, I must delete and then redefine the query that is assocatied with the rowsource for the graph. While the former method works, it seems like needless overhead to create the saved query. As you've suggested, you normally would do this by attaching a sql directly to the rowsource property for the object. For graphs, it appears that the process is different.
CosmoKramer
Programmer
Actually, this is the first time I have allegedly missed the point.
A MS Knowledge base article says:
You must open the report in Design view first, set the RowSource property of the Graph object, and then print or view the report.
You can't perform this in the OnOpen event of the report. Did you try the method I mentioned??
A MS Knowledge base article says:
Code:
If you try to set the RowSource property of a Graph object on a report in Print Preview, you may receive the following error message.
You can't set the Row Source property after printing has started.You can't perform this in the OnOpen event of the report. Did you try the method I mentioned??
CosmoKramer
Programmer
i'm sorry but i'm still lost. I've been building charts for five years and do not understand why you do not put the sql statement (including it's where clause) directly into the row source of the chart and leave it at that.
what does your WHERE clause say? you haven't given that detail yet.
what does your WHERE clause say? you haven't given that detail yet.
- Thread starter
- #11
CosmoKramer - My apologies for implying that you "missed the point". I thought that I was replying to the other responder to this question. Your insight and clarification into the problem appears to reside in the fact that once the OnOpen event is invoked, then the report is "printing" and I can't modify the design. I can, however, change the contents of the referenced Query by redefining the sql each and every time that the report is generated. Yes, your methods would work fine, except for the fact that I'm modifying the report's underlying sql within the OnOpen event. Did I miss something?
GingerR. - The contents of the "Where" very each time the report is run. Are you suggesting that I can simply automatically redefine the underlying sql for a query? If so, what's the methods used or what controls do you reference.
GingerR. - The contents of the "Where" very each time the report is run. Are you suggesting that I can simply automatically redefine the underlying sql for a query? If so, what's the methods used or what controls do you reference.
CosmoKramer
Programmer
The above code would probably go in the On Click event of a command button on your form, not in the On Open event of your report. By then, it's too late.
- Thread starter
- #13
CosmoKramer - Again, thanks for the response. You're right about the placement of the code if a form were used to OPEN the report. Unfortuantely, - or by design - I create a report that opened the form on the report's OnOpen event, so I believe the code option is out as the form is already opened and loaded.
CosmoKramer
Programmer
Unfortunately, unless you can change your design and open the form first, have the user select the criteria, then click on a command button to open the report with this assigned criteria, it appears that you're out of luck. That method is documented and works well. Once again, from MS:
Good luck!!
Code:
You can't set the Row Source property after printing has started.Good luck!!
what is/are your possible WHERE statements?
is there multiple criteria the users can select that applies to diff fields in your table/query?
where are they selecting/entering the criteria? in a form?
is the chart always based on the same table or basic query, but the WHERE clause changes diff ways, i.e. sometimes it's
Field1 = Forms!Main!txtField1Choice
and another time it's
Field2 = Forms!Main!txtField2Choice
etc etc?
is there multiple criteria the users can select that applies to diff fields in your table/query?
where are they selecting/entering the criteria? in a form?
is the chart always based on the same table or basic query, but the WHERE clause changes diff ways, i.e. sometimes it's
Field1 = Forms!Main!txtField1Choice
and another time it's
Field2 = Forms!Main!txtField2Choice
etc etc?
- Thread starter
- #16
GingerR,
Normally I was lucky if I ever got a reply back on a question, but on this one WOW - I hit the jackpot!! As for your question, yes the user must select the criteria from a drop-down list of selection variables and then those values are built into the sql's WHERE clause. That's the reason that I couldn't use a static query. Thanks for all of the thoughts on this question.
Normally I was lucky if I ever got a reply back on a question, but on this one WOW - I hit the jackpot!! As for your question, yes the user must select the criteria from a drop-down list of selection variables and then those values are built into the sql's WHERE clause. That's the reason that I couldn't use a static query. Thanks for all of the thoughts on this question.
MichaelRed
Programmer
an alternative might be to just set the sla property of the named query/querydef (at least it avoids the delete and create steps) - but leave the 'saved' query as the record source.
MichaelRed
m.red@att.net
Searching for employment in all the wrong places
MichaelRed
m.red@att.net
Searching for employment in all the wrong places
- Thread starter
- #20
GingerR
Hi again and sorry that I wasn't more clear in the description. The data for the report's sql WHERE clause comes from the specifications provided from a form called in the OnOpen event. The WHERE data is selected by the user from a drop-down list and the values can be a single value or multiple values. This data is then build into a WHERE clause. The data that populates the drop-down list box is constantly changing as that data is derived from another table that is updated on a nightly basis. I like your idea of the Query referencing a form's data variable, (i.e., the SQL WHERE clause created), but I never been able to build a static query with a referenced variable. (Use the query builder and create a SQL that uses a reference variable, forms!) Anyway, I think that we're both on the same track in that I'm creating a SQL and then using that SQL to define the new query that is created each time vs using a static query that references a reference variable. I'd be interested to know if you have created static queries , (i.e., saved queries) that use an embedded reference.
Hi again and sorry that I wasn't more clear in the description. The data for the report's sql WHERE clause comes from the specifications provided from a form called in the OnOpen event. The WHERE data is selected by the user from a drop-down list and the values can be a single value or multiple values. This data is then build into a WHERE clause. The data that populates the drop-down list box is constantly changing as that data is derived from another table that is updated on a nightly basis. I like your idea of the Query referencing a form's data variable, (i.e., the SQL WHERE clause created), but I never been able to build a static query with a referenced variable. (Use the query builder and create a SQL that uses a reference variable, forms!) Anyway, I think that we're both on the same track in that I'm creating a SQL and then using that SQL to define the new query that is created each time vs using a static query that references a reference variable. I'd be interested to know if you have created static queries , (i.e., saved queries) that use an embedded reference.
- Status
Similar threads
- Locked
- Question
- Locked
- Question
