Using two list boxs to use as query criteria

[reptar]

Hello everyone, i am trying to get 2 listboxs (the first is for name, the second is for state). I need to be able to select a name from one listbox and a state from the other and have a query display after a button is clicked. At the moment i have found a section of code which runs under the OnClick Event Procedure of the button to display the query (one listbox only though). It is as follows:

Private Sub cmdOpenQuery_Click()

On Error GoTo Err_cmdOpenQuery_Click
Dim MyDB As DAO.Database
Dim qdef As DAO.QueryDef
Dim i As Integer
Dim strSQL As String
Dim strWhere As String
Dim strIN As String
Dim flgSelectAll As Boolean
Dim varItem As Variant

Set MyDB = CurrentDb()

strSQL = "SELECT * FROM tblCompanies"

'Build the IN string by looping through the listbox
For i = 0 To name_listbox.ListCount - 1
If name_listbox.Selected(i) Then
If name_listbox.Column(0, i) = "All" Then
flgSelectAll = True
End If
strIN = strIN & "'" & name_listbox.Column(0, i) & "',"
End If
Next i

'Create the WHERE string, and strip off the last comma of the IN string
strWhere = " WHERE [strCompanyCounty] in (" & Left(strIN, Len(strIN) - 1) & ")"

'If "All" was selected in the listbox, don't add the WHERE condition
If Not flgSelectAll Then
strSQL = strSQL & strWhere
End If

MyDB.QueryDefs.Delete "qryCompanyCounties"
Set qdef = MyDB.CreateQueryDef("qryCompanyCounties", strSQL)

'Open the query, built using the IN clause to set the criteria
DoCmd.OpenQuery "qryCompanyCounties", acViewNormal

'Clear listbox selection after running query
For Each varItem In Me.name_listbox.ItemsSelected
Me.name_listbox.Selected(varItem) = False
Next varItem


Exit_cmdOpenQuery_Click:
Exit Sub

Err_cmdOpenQuery_Click:

If Err.Number = 5 Then
MsgBox "You must make a selection(s) from the list", , "Selection Required !"
Resume Exit_cmdOpenQuery_Click
Else
'Write out the error and exit the sub
MsgBox Err.Description
Resume Exit_cmdOpenQuery_Click
End If

End Sub




Is anyone familiar with the code to tell me how i go about adding code to this so to have a second listbox working?

The working example with one list box can be found here: http://www.databasedev.co.uk/downloads/multi_select_listbox_2000.zip

Cheers

Pete

 

[Sergeant]

Bandaid:

Private Sub cmdOpenQuery_Click()

On Error GoTo Err_cmdOpenQuery_Click
Dim MyDB As DAO.Database
Dim qdef As DAO.QueryDef
Dim i As Integer
Dim strSQL As String
Dim strWhere As String
Dim strIN As String
Dim flgSelectAll As Boolean
[COLOR="Red"]dim flgFirstIn as Boolean '<<<Add this[/COLOR]
Dim varItem As Variant

Set MyDB = CurrentDb()

strSQL = "SELECT * FROM tblCompanies"

'Build the IN string by looping through the listbox
For i = 0 To name_listbox.ListCount - 1
If name_listbox.Selected(i) Then
If name_listbox.Column(0, i) = "All" Then
flgSelectAll = True
End If
strIN = strIN & "'" & name_listbox.Column(0, i) & "',"
End If
Next i

'Create the WHERE string, and strip off the last comma of the IN string
strWhere = " WHERE [strCompanyCounty] in (" & Left(strIN, Len(strIN) - 1) & ")"

'If "All" was selected in the listbox, don't add the WHERE condition
If Not flgSelectAll Then
strSQL = strSQL & strWhere
End If
[COLOR="red"]'**********BEGIN ADD CODE**********************
StrIN = "" 
strWhere = "" 
flgFirstIn = Not flgSelectAll 
flgSelectAll = False 

For i = 0 To state_listbox.ListCount - 1 '<<< Check Name of ListBox (x4)
If state_listbox.Selected(i) Then 
If state_listbox.Column(0, i) = "All" Then 
flgSelectAll = True 
End If 
strIN = strIN & "'" & state_listbox.Column(0, i) & "'," 
End If 
Next i 

strWhere = "[strCompanyState] in (" & Left(strIN, Len(strIN) - 1) & ")" '<<<< Check name of field

If Not flgSelectAll Then 
  If flgFirstIn Then 
    strSQL = strSQL & " AND " & strWhere 
  Else '<<<<
    strSQL = strSQL & " WHERE " & strWhere 
  End If 
End If 
'***************END ADD CODE***********************[/COLOR]  
MyDB.QueryDefs.Delete "qryCompanyCounties"
Set qdef = MyDB.CreateQueryDef("qryCompanyCounties", strSQL)

'Open the query, built using the IN clause to set the criteria
DoCmd.OpenQuery "qryCompanyCounties", acViewNormal

'Clear listbox selection after running query
For Each varItem In Me.name_listbox.ItemsSelected
Me.name_listbox.Selected(varItem) = False
Next varItem

[COLOR="Red"]For Each varItem In Me.state_listbox.ItemsSelected '<<<check control name (x2)
Me.state_listbox.Selected(varItem) = False
Next varItem[/COLOR]

Exit_cmdOpenQuery_Click:
Exit Sub

Err_cmdOpenQuery_Click:

If Err.Number = 5 Then
MsgBox "You must make a selection(s) from the list", , "Selection Required !"
Resume Exit_cmdOpenQuery_Click
Else
'Write out the error and exit the sub
MsgBox Err.Description
Resume Exit_cmdOpenQuery_Click
End If

End Sub

Double check field and control names.

 

[reptar]

Thankyou soooo much, works perfectly

If i was to add a final 3rd list box, would it be the same sort of thing you have done?

 

[Sergeant]

The only thing that gets a little funny is keeping track of whether the SQL string has had a "WHERE" added to it. If it has, you need the "AND" prefix for further WHERE's, otherwise you need a "WHERE" prefix.
One down-and-dirty way to do this is to always have a where clause and then append as many AND clauses as you want...
"SELECT * FROM MyTable WHERE SomeField Like '*' "

Then you don't need the logic trail, you just append "AND" clauses built from your listbox.

 

[reptar]

argh im at loose ends with this, i can't get it to work with a third (and final) listbox. Do you think you can help me out? This will be the last listbox promise

 

[Sergeant]

I'm sorry, I'm out of answers for tonight. I'm thinking that someone out there has a better way of doing this, and maybe they'll chime in with that. That way, you could have a concise solution instead of a patched-up one.