Query using 3 listboxes

G

Ganz

Hope someone can help me. I'd like to run a query on one table with
selections from 3 listboxes. I can manage it with one listbox using
the code below, but how do I bring in the other two listbox selections?
I also want the option of choosing ALL in each listbox.

Is this the way to go, or is there a simpler way?

Any help much appreciated.


Private Sub Cmd394_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 REBArchive"

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

'Create the WHERE string, and strip off the last comma of the IN
string
strWhere = " WHERE [IC] 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 "qryMthRebates"
Set qdef = MyDB.CreateQueryDef("qryMthRebates", strSQL)

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

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


Exit_cmdOpenQuery_Click:
Exit Sub

Err_cmdOpenQuery_Click:

If Err.Number = 5 Then
MsgBox "Please make a selection from each 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
 
D

Douglas J Steele

You'd need to do the same thing for the other list boxes.

BTW, you can make that code a little more efficient. Only loop through the
selected items (as opposed to the whole list), and break out of the loop if
you find that "All" has been selected:

Private Sub Cmd394_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 strWhere1 As String
Dim strWhere2 As String
Dim strWhere3 As String
Dim strIN As String
Dim flgSelectAll As Boolean
Dim varItem As Variant

Set MyDB = CurrentDb()

strSQL = "SELECT * FROM REBArchive"

'Build the IN string by looping through the first listbox
For Each varItem In lbItemClass.ItemsSelected
If lbItemClass.Column(0, varItem) = " All" Then
flgSelectAll = True
Exit For
End If
strIN = strIN & "'" & lbItemClass.Column(0, varItem) & "',"
Next varItem

'Create the WHERE string, and strip off the last comma of the IN string
' If "All" was selected, don't create the WHERE string
If flgSelectAll1 = False Then
strWhere1 = " [IC] IN (" & Left(strIN, Len(strIN) - 1) & ")"
Else
strWhere1 = ""
End If

strIN = ""
flgSelectAll = False
'Build the IN string by looping through the second listbox
For Each varItem In lbBox2.ItemsSelected
If lbBox2.Column(0, varItem) = " All" Then
flgSelectAll = True
Exit For
End If
strIN = strIN & "'" & lbBox2.Column(0, varItem) & "',"
Next varItem

'Create the WHERE string, and strip off the last comma of the IN string
' If "All" was selected, don't create the WHERE string
If flgSelectAll = False Then
strWhere2 = " WHERE [Field2] IN (" & Left(strIN, Len(strIN) - 1) & ")"
Else
strWhere2 = ""
End If

strIN = ""
flgSelectAll = False
'Build the IN string by looping through the third listbox
For Each varItem In lbBox3.ItemsSelected
If lbBox3.Column(0, varItem) = " All" Then
flgSelectAll = True
Exit For
End If
strIN = strIN & "'" & lbBox3.Column(0, varItem) & "',"
Next varItem

'Create the WHERE string, and strip off the last comma of the IN string
' If "All" was selected, don't create the WHERE string
If flgSelectAll = False Then
strWhere3 = " WHERE [Field3] IN (" & Left(strIN, Len(strIN) - 1) & ")"
Else
strWhere3 = ""
End If

' Build the overall WHERE clause
If Len(strWhere1) > 0 Then
strWhere = strWhere1
End If
If Len(strWhere2) > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND " & strWhere2
Else
strWhere = strWhere2
End If
End If
If Len(strWhere3) > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND " & strWhere3
Else
strWhere = strWhere3
End If
End If
If Len(strWhere) > 0 Then
strSQL = strSQL & " WHERE " & strWhere
End If

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

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

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


Exit_cmdOpenQuery_Click:
Exit Sub

Err_cmdOpenQuery_Click:

If Err.Number = 5 Then
MsgBox "Please make a selection from each 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




--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ganz said:
Hope someone can help me. I'd like to run a query on one table with
selections from 3 listboxes. I can manage it with one listbox using
the code below, but how do I bring in the other two listbox selections?
I also want the option of choosing ALL in each listbox.

Is this the way to go, or is there a simpler way?

Any help much appreciated.


Private Sub Cmd394_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 REBArchive"

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

'Create the WHERE string, and strip off the last comma of the IN
string
strWhere = " WHERE [IC] 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 "qryMthRebates"
Set qdef = MyDB.CreateQueryDef("qryMthRebates", strSQL)

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

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


Exit_cmdOpenQuery_Click:
Exit Sub

Err_cmdOpenQuery_Click:

If Err.Number = 5 Then
MsgBox "Please make a selection from each 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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Multiple MultiSelect ListBoxes 2
multi-select list box 1
Delete selected only.... 2
Listbox with Multi-Select Output Question 1
updated hlp with multi-select query 1
listbox ? 1
listboxes and temp tables 2
Querydef Syntax 1

Top