Query using 3 listboxes

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

.

Relevant Pages

  • Boolean value stored in a combobox!
    ... I use the stored values in tblStorage to set some kind of default value next ... cboMemberCard instead of an empty string. ... selection. ... Dim db As Database, rst As Recordset ...
    (microsoft.public.access.formscoding)
  • Re: Query using 3 listboxes
    ... Dim MyDB As DAO.Database ... Dim strWhere As String ... Dim varItem As Variant ... MsgBox "Please make a selection from each list",, "Selection ...
    (microsoft.public.access.formscoding)
  • Re: Boolean value stored in a combobox!
    ... > I use the stored values in tblStorage to set some kind of default value ... > cboMemberCard instead of an empty string. ... > selection. ... > Dim db As Database, ...
    (microsoft.public.access.formscoding)
  • Re: Unique Listbox Sort Order
    ... Dim uLAs String ... Dim lcaseIndex As Integer ... The items in the listbox are alphanumeric strings, ...
    (microsoft.public.vb.general.discussion)
  • Re: List Box linked to Query
    ... you're ignoring the date comparison when flgSelectAll is true. ... Dim MyDB As DAO.Database ... Dim strWhere As String ... 'Build the IN string by looping through the listbox ...
    (microsoft.public.access.modulesdaovba)