need help w/ query based on multiple controls 1

[pduncan]

I have a table that contains static data - a collection of game pieces. I would like to have a form display a datasheet (or listbox?) based on that table. To limit the records returned, I would like to have several radio buttons or check boxes (or the best suited control) that "builds" a filter (or a query? - getting lost in the terminology.
I would like the controls to work together - for example:

control 1 = own
control 2 = Team 1
control 3 = Team 2
control 4 = Ability 1
control 5 = Ability 2

If you selected control 1, 2, and 4 the datasheet would only return those rows that I own, are on team 1, and have ability 1.
Can someone plese point me in the proper direction? Should I use a query or recordset? Should I use a listbox?


Thanks, PDUNCAN

 

[abig99]

Point the relevant fields in your query to the controls on the form mentioned, then in the after update event for each control put

me.requery

.

 

[FancyPrairie]

Check out this FAQ FAQ181-5497 It will do what you want. It handles 0 to many listboxes, comboboxes, textboxes, ranges, option groups, and checkboxes. The key is setting up the tag property. You only have to do 3 things to make it work:

1. Create a new module and copy the function from the FAQ and paste it into your new module
2. Define the tag property of your contols as described in the FAQ.
3. Call the function. It will return the Where clause without the word where

 

[pduncan]

wow FancyPrairie, that's extremely cool! thanks...

abig99 - sorry but i dont understand what you are trying to tell me - but thanks for answering.

Thanks, PDUNCAN

 

[pduncan]

FancyPrairie, I got this working on a report, but how do I call this function and have it query a recordset?

Thanks, PDUNCAN

 

[FancyPrairie]

I'm confused about your question. But note that it returns the Where clause. So, if want to create a recordset, then simply build the Select part of the query and then add the Where clause. Something like this:

Dim strSQL as string
Dim strWhere as string

strWhere = vbNullString
strSQL = "Select * From YourTable "

strWhere = BuildWhere(Me)

if (len(strWhere) > 0) then strSQL = strSQL & " Where " & strWhere