Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 657
  • Last Modified:

Problem with changing a charts rowsource programatically

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?
1 Solution
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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.

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
MannSoftAuthor Commented:
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.
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

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now