If button then query

[Guest]

I would like to filter the following query based on the result of some
buttons selected on my form. Here is the query:

SELECT tblMaterial.HANDOUT, tblMaterial.SPEAKER, tblMaterial.RATING,
tblMaterial.ASSOC, tblMaterial.REP, tblMaterial.DIS, tblMaterial.DIV,
tblMaterial.REG, tblMaterial.RVP
FROM tblMaterial
ORDER BY tblMaterial.HANDOUT, tblMaterial.ASSOC, tblMaterial.REP,
tblMaterial.DIS, tblMaterial.DIV, tblMaterial.REG, tblMaterial.RVP;

There will be 6 buttons on the form. Multiple buttons may be selected. The
idea is if no button is selected, then the above query brings everything
back, but, e.g. if REP and DIV are selected the above query should be
filtered by showing only those records which have REP and DIV as true on the
Materials table.

The property of what element on the form should your suggestion be applied?

Thanks.

 

[Guest]

The overall concept for this is to create the WHERE clause for the query
using VBA. The code should use the properties of the 6 buttons to decide
which fields should be included in the WHERE clause, and what values it
should be looking for.
Once you have the WHERE clause constructed, you can retrieve the SQL of the
Query, modify it to include the WHERE clause, save it back out to a query.

Typicallly, I will have two stored queries. One that is used and one that
is a template. The Basic code will look something like this:

strSQL = CurrentDb.Querydefs("qselMaterialsTemplate").SQL
strWhere = BuildWhere 'This is a Function that build the WHERE Clause
strSQL = Replace(strSQL, strWhere, ";") & ";"
CurrentDb.QueryDefs("qselMaterials").SQL = strSQL

Now the query has been modified and is ready to use.