creating same graph but with different data in a report in Access

I'm trying to create an on the fly report where each page of the report shows a informationa and a graph.  The graph template is the same for every page but the data is not for each page.  What I am doing now is when the report is launched, a have a form that pops up and you do your selection criteria.  I know in the past, I've had to put my graph row_source info in the onclick button because you can't set the rowsource property in print preview or after the printing has started in the report. However, that worked great because I had only 1 report to run. I don't want to have to open up the report a gazillion times and re-run each individual report, save it to a pdf file, then run the next one. Is there anyway of getting around this?
Thanks!

Micky
mickygeneAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

puppydogbuddyCommented:
Yes there is. You can change the record source dynamically.  See the following link:

         http://ourworld.compuserve.com/homepages/attac-cg/ATip.htm#MSG
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
harfangCommented:
Hello,

The chart control has two properties called "LinkChildFields" and "LinkMasterFields". These have exaclty the same function as the corresponding properties of a subform or subreport. Let's say you want to see graphs showing the evolution of sales over time for each country, you would create a report showing one page (one detail section) for each country, with all needed summary information. The chart will then use the country code as link master field and the chart's query will automatically filter the data for a given country, using the child field.

If you have installed the sample database Northwind, make a copy of it, open it and look for a report called "Sales by Category", it does just that.

Although it's often suggested on this site, changing the record source or row source dynamically is _always_ a bad idea or a clear symtom of bad design.

Cheers!
(°v°)
0
puppydogbuddyCommented:
Harfang,
<<<<<Although it's often suggested on this site, changing the record source or row source dynamically is _always_ a bad idea or a clear symtom of bad design.>>>>>>>

Please elaborate for my benefit.  ATTAC Consulting Group, the company that provided the tip, is a well respected and experienced company.
0
harfangCommented:
puppydogbuddy,

This was not meant as a criticism of the company at the other end of your link. Obviously they do know their field.

However, in my experience (professional and here as expert) I have never encoutered a situation where writing or otherwise changing dynamically a record source string was a good idea. When there seemed to be no other solution, I could always trace it back to bad table design or to bad interface design, although some of it might be a matter of taste.

If you study the implementation of Access, you will notice that all possible mechanisms have been included to avoid that need. Namely link fields for subforms (and charts) and the systematic exportation of the local name space to the JetEngine. In other words, a query on form object (including charts) can use all the other form's objects as implicit parameters. In some rare cases (cross tab queries for instance) it might be necessary to explicitely declare them as parameters, but they can always be used.

Of course, when that mechanism does not work, with non-Jet backend databases, rewriting these strings might be the only solution, but I claim that it is due to a bad dialog between Access and that particular database engine... thus being a symtom of poor design again.

It is my impression that many programmers simply like the idea and do not think about other solutions much. I have had this discussion a few times already, so I guess that in the end it's really more a matter of taste than anything else. However I still claim that if you show me an application using that trick, I can find a better solution or blame the need for it to some flaw upstream...

Cheers!
(°v°)
0
puppydogbuddyCommented:
Harfang,
Interesting!  Thanks for the info.

                      PDB
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.