Multiple selections for report

[santosm]

Hi All,
I have a report page that I am putting together. On the page I would like the user to be able to select one or more regions that will determine the output of the report. In this case, they user can select one or more U.S. states to get the data they want. I have the queries written that will create the output report. What would be the best way to use a selection area and pass that to info the query? I will have a simple block that has 50 check boxes in it and they can select any of them for the report. I don't recal doing anything like this before so any insight would be greatly appreciated.

Thanks,
Mark

[Rudi]

Hi Mark,

I'm not sure if you have progressed with this query yet, but I have come across a sample web page that can provide you with some ideas for building this filtering system you propose. If you intend for a person to filter by any number of states so that a report is generated based on this I could agree that a set of 50 check boxes might be the best route to take; it would certainly be the quickest and easiest presentation for the user to select from.

You would need to build a form with these 50 check boxes. Also on the form you would have an OK (filter) and Cancel button.
Once the user selects any number of check boxes, the code behind the OK button would loop through the check boxes to determine which ones are checked and then compile that into the filters of your query, which in turn would update the linked report you want to display. To give you some samples of the process and the code to make this work, review this webpage for details. I am not expert in Access so this is the best I can do until someone else can provide better solutions for your assistance.

Cheers (and good luck with this project)

[Pat]

Rudi's approach will work well if you name the checkboxes correctly so the VBA code loop is easy to code. (eg, chkState1, chkState2 .....etc)

What I do in scenario like this is to have a subform on the form that is based upon a work table, this would be used to select the states desired. On the main form I would have a clear button to delete all entries from the temporary table to start again if you have to. Having a subform like that means you can delete a state you inadvertently entered.
I would use a combo box to select states and populate the temporary table as you go.

The query for the report then just joins that table to the appropriate table in the query.

[santosm]

Hi Pat and Rudi,
Thanks for the pointers. I ended up using a multi-select list box which seems to give me what I need.

Thanks,
Mark

[Rudi]

Ah...that is a good option too. (Can you select multiples just by clicking or must you hold down CTRL and click?)
Glad you came right.
Cheers