Query with Combo Boxes on a Form

[Guest]

I'm using QBF with about six different combo boxes using:
[forms]! [formmain]! [combo1] or [forms]![formmain1]![combo1] -like in a VBA
book. This is so users can select criteria on a form with the combo boxes,
and when they are done, they hit the search button, and it opens up another
form based on the query just performed by the combo selection.

This worked for about three combo boxes, but when I added another one, it
freezes up and opens up a blank page. It's supposed to open the new form
based on the query.

Is there a better way to do this?
Any help would be greatly appreciated.

 

[Guest]

Mattie,

I usually avoid the query by form, and create my own forms with unbound
combos. Each of these combos would have individual queries that return
unique values that correspond to a specific field. Then, I use the Click
event of a button to build the WHERE clause of the SQL statement, and
finally, I set the SQL for the query that is the source of your next form to
the value computed above.

If the 2nd form you mention above is frmMain, and the first one is
frmSearch, the code in the cmd_Filter button on frmSearch might look like:

Private Sub cmd_Filter_Click

currentdb.querydefs("qry_frmMain").SQL = BuildSQL
docmd.openform "frmMain"
docmd.close acform, me.name

End Sub

Private Function BuildSQL() as string

Dim strSQL as string
Dim varWhere as variant

strSQL = "SELECT * FROM yourTable"

varWhere = NULL
'assumes that combo1 bound column is a string
If NOT isnull(me.combo1) then varWHERE = "[Field1] = " & Quotes(me.combo1)

'assumes that combo2 bound column is a string
IF NOT isnull(me.combo2) then
varWhere = (varWhere + " AND ") & "[Field2] = " & Quotes(me.combo2)
end if

'assumes that combo3 bound column is numeric
if not isnull(me.combo3) then
varWhere = (varWhere + " AND ") & "[Field3] = " & me.combo3
endif

strSQL = strSQL & (" WHERE " + varWhere)

End Function

Public Function Quotes(TextToQuote) as string

Quotes = chr$(34) & TextToQuote & chr$(34)

End function

HTH
Dale