Setting combo box to select all/use all values if none selected

[kenjones]

I have a number of combo boxes set up to allow users to select values with which to query tables. How can I setup these boxes so that if nonvalue is selected, the query is carried out using all values?

 

[SBendBuckeye]

You might try setting up a global string to hold the WHERE clause of your query. You could set it to vbNullString in the Form_Current code and then build it from the various combo box After Update event code.

As an example, assume that you had address combo boxes set up for city and state named cboCity and cboState. Then you could code as follows:

Private Sub cboCity_AfterUpdate()
strWhereClause = strWhereClause & " City = " & cboCity _
& " AND "
End Sub

Private Sub cboState_AfterUpdate()
strWhereClause = strWhereClause & " State = " _
& cboState & " AND "
End Sub

Always use a leading a trailing space to ensure that two values don't accidently touch each other. That way, your city and state where clause parts are only updated if they are selected by the user and changed.

After the last one, remove the trailing " AND " from your strWhereClause as so:

strWhereClause = IIF(Right$(strWhereClause, 5) = " AND ", _
Left$(strWhereClause, Len(strWhereClause) - 5, _
strWhereClause)

strSQL = strSQL & IIF(Len(strWhereClause)=0, vbNullString, _
" WHERE " & strWhereClause&quot

Hope this help! Good Luck!

 

[Jaggernull]

Hi yall!

I have used a system like this for search criteria lots. For simplicity, I generally put a tick box next to each criteria selction combo. Call the tick box "ALL" and make it default to "yes". Then you can test each criteria for a value in the combo OR a tick in the "ALL" box.

Not high tech, but works!

GL