Hints to determine which combo boxes were selected?

Question

I have 8 combo boxes in an Access database. Each combo box can either have a value or not have a value (2 options). In total, there can be 256 combinations (2^8). I am trying to create some code in VBA that loops through these combinations to determine which combination currently exists, with the ultimate goal of writing an SQL query within VBA based on that combination. So for example, let's say combo1 and combo2 both have selections, but not combo3 through combo8. If that is the combination I would like my SQL query to do a SELECT FROM query WHERE a column in db = combo1 and a column in db = combo2. Can anyone provide hints as to how I would structure my code?

Thanks!

Answer 1

Dim a as string, b as string 
const myAND as string = "AND "

a = ""
a = "SELECT * FROM a table "

b = ""
if cbo1.value <> "" then
  b = b & myAND & "AND field1 = '" & cbo1.value & "'"
end if

if cbo2.value <> "" then
  b = b & myAND & "field2 = '" & cbo2.value & "'"
end if

etc for each cbo box

If b <> "" Then

   ' Lazy way 
   ' a = a & "WHERE 1=1 " & b 

   ' remove the first AND way
   a = a & "WHERE 1=1 " & mid(b,len(myAND))

End if

' a now contains the SQL you need.

Answer 2

Dim where_condtion as String
Dim sqlquery as String

where_condtion = ""

IF combo1 <>"" then
    where_condtion = where_condtion + "~fieldname~ = " & combo1
End IF
IF combo2 <>"" then
    where_condtion = where_condtion + "AND ~fieldname~ = " & combo2
End IF
*
*
*
IF combo8 <>"" then
    where_condtion = where_condtion + "AND ~fieldname~ =" & combo8
End IF

IF where_condtion <> "" then
    sqlquery = "Select * from ~table name~ where" + where_condtion
ELSE
    sqlquery = "Select * from ~table name~
End IF

sqlquery = Replace(sqlquery, "where AND ", "where ")

DoCmd.OpenQuery "sqlquery", acViewNormal, acEdit

OR

CurrentDb.OpenRecordset("sqlquery")

Answer 3

Am option would be a concatenated string

Code Example

Dim strSQL as String
'basic string
strSQL = "SELECT tbl.fieldA, tbl.fieldB FROM tbl "

Dim strSQLwhere as String
strSQLwhere = ""
'Combobox cmbbox1
If Not isNull(cmbbox1) And cmbbox1.ListIndex <> -1 then
    strSQLwhere = strSQLwhere & "tbl.fieldToFilter1=" & cmbbox1
End if

'Combobox cmbbox2
If Not isNull(cmbbox2) And cmbbox2.ListIndex <> -1 then
    iF NOT strSQLwhere = "" then     
       strSQLwhere = strSQLwhere & " AND "
    end if
    strSQLwhere = strSQLwhere & "tbl.fieldToFilter2=" & cmbbox2
End if

'And so on until cmbBox 8

'Combine all Strings
if not strSQLwhere = "" then
    strSQL  = strSQL  & " WHERE (" & strSQLwhere & ")"
End if

'Add here further thing like ORDER BY, GROUP BY
'Show SQL sting if it is well fomratted, change string concatenation if not
debug.print strSQL  

You could do the combobox if-then-(else) cases in a separate function if you are able to do that in VBA.