How to populate report based on criteria specified in form?
Posted on 2004-11-17
I have noticed several references to this subject but I’m having trouble creating a solution that meets my needs.
My goal is to have a form that allows the user to select the criteria used to filter an existing query and populate the report based on the results of that query.
In Access 2000 I have a query called “qryReport”. This query contains several expressions that generate a master record set of all the data I need for my reports.
The report is called “rptReport”. It contains selected fields found in qryReport. These fields will not change on the report (at least for now).
Finally, I have a form that contains about 30 text, combo and checkbox objects that will be used to filter data found in qryReport and populate the report. I also have a combobox that allows users to sort data based on the fields represented in the report.
I will also be looking into exporting this report as and Excel spreadsheet. I’m not looking for a solution for this particular feature right now, but I thought I’d mention my future plans in case it impacts the solution.
I have tried sending parameters to qryReport, modifying the record source in rptReport on the fly, and applying filters to rptReport but I keep running into roadblocks.
I’m looking for a solution that can easily be managed if the criteria on the form must change.
Any suggestions or samples would be greatly appreciated.