Using a field list combo box as query criteria?

[dux4all]

I would like to make a flexible query where users would select the desired criteria field from a field list combo box in a form and then pick the criteria from another combo box. My question is how do you use the field list output for a query criteria? Any suggestions would be appreciated.

[pbaldy]

To the best of my knowledge, you can't do this type of thing without VBA. Presuming it's for a form or report, I'd use this technique:

BaldyWeb wherecondition

First building a string using your combos:

strCriteria = Me.FirstComboName & " = " & Me.SecondComboName

and using that string in the wherecondition argument.

[pbaldy]

I should add that it gets more complicated if your fields are of varying data types. As you can see in that link, different data types require different delimiters.

[Access_Blaster]

Could you use the combo box after update event to trigger a query, and pass the criteria?

Private Sub Combo0_AfterUpdate()
     DoCmd.OpenQuery "SomeQuery"
End Sub

[pbaldy]

Could you use the combo box after update event to trigger a query, and pass the criteria?

Private Sub Combo0_AfterUpdate()
     DoCmd.OpenQuery "SomeQuery"
End Sub

I don't see how that accomplishes the goal in the OP, but perhaps you can post a sample db demonstrating what you mean?

[dux4all]

Thanks for all the suggestions. I'll try to better explain my needs. I do believe this will require VBA. I have created a sample table below:

ID   Color   Shape   Size
 1   red   circle   small       
2   green   square   small       
3   red   rectangle   medium       
4   yellow   square   large

I would like to create a form using a field list combo box to have the user select Color, Shape, or Size as the criteria field from the combo box(1). Say the user selects Color from the field list combo box another combo box(2) would list red, green, yellow. These two combo boxes would make up the WHERE statement of my query: WHERE combo box(1)=combo box(2). The field list combo box is easy. I just don't know how to get that information from the form into the query. My real table has dozens of fields and that is why I don't want to create/maintain several "static" queries, when a "flexible" one sounds like a good idea.

Thanks again!

[pbaldy]

I understood the request, so I'll stand by my first response. You could also use a DAO QueryDef to change the SQL of your query. Given the number of options and presumably varying data types, neither solution will be trivial. Another option if you want to give the user the option to filter on multiple fields is demonstrated in the sample db here:

http://www.baldyweb.com/BuildSQL.htm