Dim sSql As String, sWhere As String
Dim qdf As querydef
Const kQRY = "qsFormFilter" 'here is the query we use ...in the report too
sWhere = "1=1"
'the query is built depending on the various filters the user picks...
If Not IsNull(cboState) Then sWhere = sWhere & " and [state]='" & cboState & "'"
If Not IsNull(txtName) Then sWhere = sWhere & " and [Name]='" & txtName & "'"
If Not IsNull(chkContact) Then sWhere = sWhere & " and [Contact]=" & chkContact.Value
'use it to filer the form records
if sWhere = "1=1" then
me.filterOn = false
else
me.filter = sWhere
me.filterOn = true
endif
'---------------
'or 'BUILD the query from the 'where'
'---------------
Set qdf = currentdb.querydefs(kQRY)
qdf.Sql = "SELECT * FROM tblCompany WHERE " & sWhere
qdf.Close
'open the query here!
DOCMD.openquery kQRY