[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 341
  • Last Modified:

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
0
mickygene
Asked:
mickygene
  • 3
  • 2
1 Solution
 
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
 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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