Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Use a form and query to send criteria to a report
How To
I like to use a form where the user can select various criteria for a report. The report recordsource will be a query, and my form will pass the selection info to it in the form of criteria.
First, set up a form with all the fields desired for the user to select his/her criteria. I usually use comboboxes or listboxes based on a table or query. BUT, to allow wildcard lookups, I generally add a new item to the table. For instance, in a table of U.S. states, I added a state code of "%" (without the quote marks), and for the description I used "<all states>".
Second, the form should have a command button that allows the user to run the report. It is at this time you can check to make sure that the user has selected all the required criteria. Of course, the code would include the call to run the report, e.g., "DoCmd.OpenReport "rptStates", acViewPreview
Third, I set up a query to gather the information needed. For each of the fields that appears on the selection form, I set the criteria box to a specific field on the form. For instance, let's say that I let the user select the year from a table of accounting records. Here is how the criteria line will look for the field called fldYear:
=[Forms]![frmInputCriteria]![txt_Year]
(By the way, this same line can appear as the Control Source for a text box on the header area of a report, thereby allowing a user of the report to know what criteria was used to select that particular set of data)
Fourth, set up a report, and base the report on the query you have just created. If you want, you may print the selection criteria in the header as just explained.
So, here is what happens:
1) The user enters the criteria into the form, and clicks the cmdReport button.
2) The code will check to make sure all necessary criteria has been selected (instead of having nulls).
3) The code will then start the report, which will turn around and run the query.
4) The query will check all the criteria, and build the result set, which is then returned back to the report.
5) The report is built, and then displayed to the user.
Feedback, comments?
Randy Smith, MCP
rsmith@cta.org![[pc2]](/data/assets/smilies/pc2.gif "[pc2] [pc2]")
First, set up a form with all the fields desired for the user to select his/her criteria. I usually use comboboxes or listboxes based on a table or query. BUT, to allow wildcard lookups, I generally add a new item to the table. For instance, in a table of U.S. states, I added a state code of "%" (without the quote marks), and for the description I used "<all states>".
Second, the form should have a command button that allows the user to run the report. It is at this time you can check to make sure that the user has selected all the required criteria. Of course, the code would include the call to run the report, e.g., "DoCmd.OpenReport "rptStates", acViewPreview
Third, I set up a query to gather the information needed. For each of the fields that appears on the selection form, I set the criteria box to a specific field on the form. For instance, let's say that I let the user select the year from a table of accounting records. Here is how the criteria line will look for the field called fldYear:
=[Forms]![frmInputCriteria]![txt_Year]
(By the way, this same line can appear as the Control Source for a text box on the header area of a report, thereby allowing a user of the report to know what criteria was used to select that particular set of data)
Fourth, set up a report, and base the report on the query you have just created. If you want, you may print the selection criteria in the header as just explained.
So, here is what happens:
1) The user enters the criteria into the form, and clicks the cmdReport button.
2) The code will check to make sure all necessary criteria has been selected (instead of having nulls).
3) The code will then start the report, which will turn around and run the query.
4) The query will check all the criteria, and build the result set, which is then returned back to the report.
5) The report is built, and then displayed to the user.
Feedback, comments?
Randy Smith, MCP
rsmith@cta.org