Creating a Report Menu to allow user filters (2 Viewers)

[Gina]

I have been tasked to create a report menu for my users to select a report (there are about 20), select which filters that they want to apply to that report, then run based on what the user selected as the filters.

I have about 30 different filters to create, and based on which report the user selects, the filters that apply will need to be shown. I'm thinking the reports will be in an option group, and based on which report the users selects the filters that apply to that report will be displayed. From there the user can choose to apply a filter or not, and run the report.

I am trying to figure out the best way to tackle this. Should I try to create a table that contains the report name and type of filter to help me loop thru the controls to set visible, and build the SQL?

Any ideas would be appreciated. Thank You.

 

[Alansidman]

Gina, Instead of an options group, I would suggest a combo box. Look at this link and in particular, threads 7 and 10 for an example of how to set that up. Then on the form, depending on the filters you wish to employ, you could set them to visible if a certain report is selected.

http://www.excelforum.com/access-ta...a-specific-record-in-a-table-to-a-report.html

 

[Gina]

I'm wondering if Pat Hartman uses this also as a method for filtering.

http://www.access-programmers.co.uk/forums/showpost.php?p=1183547&postcount=6

Hoping we get a view and reply from Pat

 

[Cronk]

At the risk of being critised for putting words in others' mouths, I'll put what I'm sure Pat would do.

I'd have a second table containing all the filters for the reports. Fields would be FilterID (PK), ReportID (FK to tblReports so you can have several filters per report), FilterName (user friendly name), FilterString (the actual filter to be applied).

In the Report menu form, I'd have a second combo box, which would be populated by whatever filters are applicable to the report selected in the first combo box.

 

[Gina]

This is a picture of my "smaller" report menu. When the user selects a report from the option box, the filter combo boxes will be displayed based on the report selected. I'm thinking I would also like to store in the table whether the filter combo box should be displayed or not for each report.

 

[Pat Hartman]

This is how a report form driven by a table could look. The first picture shows the form as it looks when it opens. The second picture shows the form as it looks once the user selects a report. The code behind the listbox, uses the table to determine which data entry fields to make visible. It also makes the label bold for any values that are required. In the second picture, for the Drawing History report, only the Job is required. All the other values are optional.

For each criteria, the table uses a combo with three possible values - Required, optional, not allowed. The earlier picture I posted showed checkboxes which only allow for two options. I think 3 is better because it gives you the "required" option.

 

[Gina]

I want to thank you all for your guidance. Attached is a shell of what I have done. I am no expert, but it works for my application.