Jun 28 2005, 04:06 PM
I consolidated 10 reports into one main report with 10 subreports. Each subreport reports data on 7 different companies. I linked each master/child field to company. Now when I want to view or print I am prompted with a parameter field in which I must enter the company name to view/print that specific companies information on the 10 different subreports.
How do I make a macro that lets me only enter the company name in once and it will automatically generate the full new report (with 10 subreports in it)? It's annoying entering the same company in 10 times, and then running that report seven times (one for each company).
Jun 28 2005, 04:21 PM
Create a small form and add a text field to this. Then you can have code that forces the user to enter a company name.
Better yet make it a combo box and lookup the data... that way they dont need to worry about spelling mistakes.
Jun 28 2005, 04:26 PM
How do I do that? I'm VERY new to Access. When I open the report, I'm immediately prompted to enter a company in, I know how to make a form, but how do I make the form work into the parameter field I'm being prompted with?
Jun 28 2005, 05:00 PM
no problem.. we'll go through it step by step...
The data that the report(s) are based on must ask for a parameter... Are the reports based on saved queries or does the SQL sit in the report?
Jun 28 2005, 05:54 PM
based on saved queries.
Jun 28 2005, 06:07 PM
ok.. So if you go into the design view of the queries there will be a parameter prompt in the criteria of the company name?
You need to replace this with the name of the text box control on your form..
Let's assume that your form is called "Frm_ReportP" and the company Name control is called "CompanyName"...
You would replace the parameter prompt (i.e. [Please input company name]) with Forms![Frm_ReportP]![CompanyName].
Then with the form open you input the company name and it will run the report without asking for the name (as it's getting it from the company name)
Jun 29 2005, 05:45 AM
no, no parameter prompt in the company name portion of the query, never was, the parameter value only began showing up after i made the initial report a subreport in a consolidated report. in order to filter all 10 subreports to report information on just one company i linked the master/child fields to "company" and put company in the filter fields, now the parameter prompt appears.
Jun 29 2005, 10:37 AM
can you post a copy of the DB?
Jul 1 2005, 06:11 PM
The database is 16 megabytes, not sure if I can post it, but I did find out the problem. The problem was that the master report had nothing in it but subreports, so when I linked the child and master fields there was nothing in the master field to link to, which is why I'd get the prompts for which company to view for every subreport. Fix was to make a query in the master report for company names.
Thank you so much for your help!
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here