Modify Query in code on form

[SparrowHawk]

Hi All,

I have a form with a listbox (lstVolunteers). The data for the list comes from a query. The form also has a series of 6 check boxes to drill down the query - each check box is a different criteria to be used in the query (things like brown hair, blue eyes, white hair, brown eyes, etc). The user can select all, some or none of the checkboxes and only data meeting all that criteria will be listed. There is a command button (cmdSearch) which will activate the query and populate the listbox. For example, if the user checks the box for brown eyes and the box for gray hair, only people with BOTH those criteria will come up listed in the box.

How would I go about modifying the query itself based upon which check boxes are selected when he search button is pressed? (the fields in the table for these selections are called Select1, Select2, Select3, ... Select6 and will be either 0 (for not applicable) or -1 if true.

Hope I explained that clearly enough ...
TIA
Ken

[XLGibbs]

What you can do is have a prebuilt query that looks to the fields in your form (whether visible or not) for the criteria...such as a parameter query....if you the field is null, then there is no criteria

Otherwise, you would have to specify the conditions within the where clause depending on the choices....

For example the code would have a variable for hair color
If Select1 then vHair = "Brown"

and the query would have

Where [Haircolor] is vHair

to specify the filter...

You can use the form wizard to create a bogus form with a button that filters data..the code created by the wizard would be similar to that which you would have to modify...

[SparrowHawk]

Thanks Gibbs ... I'll have to play around with that when time permits today and see if I can manipulate it to solve this problem of mine.

Ken

[matthewspatrick]

Ken,

The way I've handled similar things in the past is to use a form with one multi-select ListBox for each field I want to apply criteria to, and populate the ListBoxes with the possible values for those fields. The users would then pick the values they wanted from the ListBoxes, and I would use code to dynamically build IN statements for the WHERE clause.

For really big tables, using IN like this can slow things down, though...

Patrick

[SparrowHawk]

Thanks Patrick ... the problem is that I have 6 different criteria and the user can pick any combination of them. (isn't that something like 6x5x4x3x2 possibilities? [forgotten my probability classes]) So wouldn't trying to combine all of that take quite awhile even with a dB of only a couple hundred entries?

Ken

[matthewspatrick]

It might be manageable. Can you upload perhaps a truncated version of the app?

[SparrowHawk]

Hi Patrick. I don't think that would help. At this point I only have the volunteer information form (standard .. name, address, phone, etc) and a form for the selection process which only has the listbox and 6 checkboxes. There is no relative code yet. I just know how I want the selection form to work in my head. The check boxes are all grouped together with a clear and a search button below them. Clear, obviously clears checks and seach should populate the listbox according to the criteria in the checks above.

I was thinking that the code for the seach button ought to build the query somehow but I have no clue as to exactly how to do that. Gibbs touched on this above but I've never done anything like that so I have to play around with it.

Thanks,
Ken