Problem with changing a charts rowsource programatically

Posted on 2003-03-27
Medium Priority
Last Modified: 2012-06-27
I'm working on an Access Project that connects to my SQL server for report generation.  In one of my reports I've got a graph that 90% of the time shows the same information, but under some circumstances I want it to be a little more selective.  So I've been trying to change the RowSource of the object in the Report Open event.  Here is an example of the code I'm using (obviously not that SQL statement, the real one is much longer and would actually return rows <g>)

    OLEUnbound1.RowSource = "SELECT * FROM MyTable WHERE 1=2"

When I go to preview the report, I get this error dialog:

Run-time error '2455':

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

Im prompted to hit either End or Debug.  If I hit End, the report closes of course.  If I hit Debug, it highlights the above line.  Now what I've found is that if I change the above line to:

    Me.OLEUnbound1.RowSource = "SELECT * FROM MyTable WHERE 1=2"

and hit F5 to continue, the report loads fine and shows the information I want.  So I think great, problem solved.  But then the next time I go to view the report I get the exact same error message highlighting the same line.  If I remove the Me. part so it's back to the original:

    OLEUnbound1.RowSource = "SELECT * FROM MyTable WHERE 1=2"

and again hit F5 to continue, it again works just fine.

So now I am stumped.  Apparently the code is fine, since it works after making a change that should have no real effect.  But at the same time apparently it isnt fine, because if it was I wouldnt get that stupid error message all the time.

Any suggestions as to what I might be missing here?  Is it just not possible to change the RowSource property at run-time?
Question by:MannSoft
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 58

Accepted Solution

Jim Dettman (Microsoft MVP/ EE MVE) earned 200 total points
ID: 8219371
<<Is it just not possible to change the RowSource property at run-time? >>

  Access can be funny about this.  For example, if you preview first, it considers the report to have already been "printed" and won't let you modify.

Two possible workarounds:

1. Base the charts rowsource on a query and modify that before you open the report.

2. Open the report in design view, make the change to the chart control, then flip the report to print mode.

LVL 18

Expert Comment

ID: 9240422
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
Accept question, points to JDettman
Please leave any comments here within the next seven days.


EE Cleanup Volunteer

Author Comment

ID: 9240519
Wow I forgot all about this question.  We ended up throwing out Access for the report generation so I guess I forgot to check out this section again.  The answer doesnt really help since I wanted to avoid making changes manually all the time, but since it was the only answer given it's good enough for me.

Expert Comment

ID: 37483779
I am working with an Access 2010 .adp database talking to a SQL Server 2008 back end. I have this same issue. I've been tasked with porting an older Access DB to use SQL on the back end  so the queries are not "SQL" at design time since there are runtime parameters that control what the graph will look like. therefore the suggestion of presetting the query at design time won't work.

Example: exec StandardGraph 'acccred',2012,'AF'

where the last two parameters might change.

i've thought of some very NASTY work arounds such as having the calling code prerun the queries and store the results in a simple table and then have a fixed SQL statement for the rowsource at design time. example: Select x,y,z from tableA where thetype = 'acccred'. not a very clean solution.

has anybody, since the time this was posted, come up with a better work around?

is there an event PRIOR to the Report_Open() event that would allow the rowsource to be modified?


Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question