How to use the same query for multiple combo boxes

[jlyn123]

I have created a form where I have two columns of combo boxes. Once you select a drop down option from your list column you move to the second row and have a selection based on your first column pick. I can get this to work for the first line (row) but need it to work for all rows. Below is my query.

Field: Carrier/Account Number Client/Linecode
Table: tbl_Trion tbl_Trion
Total: Group By Where
Sort: Ascending
Show: check
Criteria: [Forms]![Trion]![txt_client1]

However, I have a list of 20 rows. I don't want to make individual queries for each row [txt_client2]; how can I combine all of these in one so I can use only 1 query?

Thanks

 

[ranman256]

cycle thru the controls to build your Where clause...

'----------------
sub btnOpenQry_click()
'----------------
dim sWhere as string 
dim qdf as querydef


sWhere = " where 1=1"
if not IsNUll(cboST) then sWhere = sWhere & " and [State]='" & cboST & "'"
if not IsNUll(cboCity) then sWhere = sWhere & " and [city]='" & cboCity & "'"
if not IsNUll(cboZip) then sWhere = sWhere & " and [ZipCode]='" & cboZip & "'"


set qdf = currentdb.querydefs("qsMyQry")
qdf.sql = "Select * from table " & swhere
qdf.close
docmd.openquery qdf.name