Multiple MultiSelect ListBoxes

  • Thread starter Thread starter Patrick W via AccessMonster.com
  • Start date Start date
P

Patrick W via AccessMonster.com

I have a form that uses a multiselect listbox as criteria for a query based
on the user selection(s). I would like to add another multiselect listbox to
the form so the user can make multiple selections in eithor, or both
listboxes for the query.

Below is the code I am useing for one list box, but when I try to add a
second list box I can't get it to work.

Private Sub cmdViewQuery_Click()
On Error GoTo Err_Handler
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String

For Each varItem In Me.SelectDate.ItemsSelected
strCriteria = strCriteria & "IT_Billing_Extract.InvoiceMonth =" & Chr(34)
& Me.SelectDate.ItemData(varItem) & Chr(34) & " Or "
Next varItem

strCriteria = Left(strCriteria, Len(strCriteria) - 4)

strSQL = "SELECT IT_Billing_Extract.InvoiceMonth, Lookup_FNA_Department.[Roll-
Up Org], IT_Billing_Extract.BillToDept, IT_Billing_Extract.ProdServDrill2,
IT_Billing_Extract.ProdServDrill3, IT_Billing_Extract.ChargeDescription,
IT_Billing_Extract.PhoneNumber, IT_Billing_Extract.WorkUnit,
IT_Billing_Extract.UnitCost, IT_Billing_Extract.AmountBilled " & _
"FROM Lookup_FNA_Department INNER JOIN (tblInvoiceMonth RIGHT JOIN
IT_Billing_Extract ON tblInvoiceMonth.InvoiceMonth = IT_Billing_Extract.
InvoiceMonth) ON Lookup_FNA_Department.Department = IT_Billing_Extract.
BillToDept " & _
"WHERE " & strCriteria & _
"ORDER BY IT_Billing_Extract.InvoiceMonth "


CurrentDb.QueryDefs("2005 FNA Tcom Pagers Detail").SQL = strSQL

DoCmd.OpenQuery "2005 FNA Tcom Pagers Detail"

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number = 5 Then
MsgBox "Must Make A Selection First", , "Make A Selection First"
Exit Sub
Else
MsgBox Err.Number & " " & Err.Description
Resume Exit_Handler
End If

End Sub

Thanks in advance for your help!!
 
Try replacing your section

For Each varItem In Me.SelectDate.ItemsSelected
strCriteria = strCriteria & "IT_Billing_Extract.InvoiceMonth =" & Chr(34)
& Me.SelectDate.ItemData(varItem) & Chr(34) & " Or "
Next varItem

strCriteria = Left(strCriteria, Len(strCriteria) - 4)

with something like

Dim strCriteria1 As String
Dim strCriteria2 As String

For Each varItem In Me.SelectDate.ItemsSelected
strCriteria1 = strCriteria1 & "IT_Billing_Extract.InvoiceMonth =" &
Chr(34)
& Me.SelectDate.ItemData(varItem) & Chr(34) & " Or "
Next varItem

strCriteria1 = Left(strCriteria1, Len(strCriteria1) - 4)

For Each varItem In Me.OtherListBox.ItemsSelected
strCriteria2 = strCriteria2 & "IT_Billing_Extract.Field2 =" & Chr(34)
& Me.OtherListBox.ItemData(varItem) & Chr(34) & " Or "
Next varItem

strCriteria2 = Left(strCriteria2, Len(strCriteria2) - 4)

strCriteria = "(" & strCriteria1 & ") AND (" & strCriteria2 & ")"


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Patrick W via AccessMonster.com said:
I have a form that uses a multiselect listbox as criteria for a query based
on the user selection(s). I would like to add another multiselect listbox
to
the form so the user can make multiple selections in eithor, or both
listboxes for the query.

Below is the code I am useing for one list box, but when I try to add a
second list box I can't get it to work.

Private Sub cmdViewQuery_Click()
On Error GoTo Err_Handler
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String

For Each varItem In Me.SelectDate.ItemsSelected
strCriteria = strCriteria & "IT_Billing_Extract.InvoiceMonth =" &
Chr(34)
& Me.SelectDate.ItemData(varItem) & Chr(34) & " Or "
Next varItem

strCriteria = Left(strCriteria, Len(strCriteria) - 4)

strSQL = "SELECT IT_Billing_Extract.InvoiceMonth,
Lookup_FNA_Department.[Roll-
Up Org], IT_Billing_Extract.BillToDept, IT_Billing_Extract.ProdServDrill2,
IT_Billing_Extract.ProdServDrill3, IT_Billing_Extract.ChargeDescription,
IT_Billing_Extract.PhoneNumber, IT_Billing_Extract.WorkUnit,
IT_Billing_Extract.UnitCost, IT_Billing_Extract.AmountBilled " & _
"FROM Lookup_FNA_Department INNER JOIN (tblInvoiceMonth RIGHT JOIN
IT_Billing_Extract ON tblInvoiceMonth.InvoiceMonth = IT_Billing_Extract.
InvoiceMonth) ON Lookup_FNA_Department.Department = IT_Billing_Extract.
BillToDept " & _
"WHERE " & strCriteria & _
"ORDER BY IT_Billing_Extract.InvoiceMonth "


CurrentDb.QueryDefs("2005 FNA Tcom Pagers Detail").SQL = strSQL

DoCmd.OpenQuery "2005 FNA Tcom Pagers Detail"

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number = 5 Then
MsgBox "Must Make A Selection First", , "Make A Selection First"
Exit Sub
Else
MsgBox Err.Number & " " & Err.Description
Resume Exit_Handler
End If

End Sub

Thanks in advance for your help!!
Click to expand...
 
THANKS Douglas,

I did all of this before except:

strCriteria = "(" & strCriteria1 & ") AND (" & strCriteria2 & ")"

This fixed my SQL statment (oops), everything works fine now.

Thank you, thank you!!
Try replacing your section

For Each varItem In Me.SelectDate.ItemsSelected
strCriteria = strCriteria & "IT_Billing_Extract.InvoiceMonth =" & Chr(34)
& Me.SelectDate.ItemData(varItem) & Chr(34) & " Or "
Next varItem

strCriteria = Left(strCriteria, Len(strCriteria) - 4)

with something like

Dim strCriteria1 As String
Dim strCriteria2 As String

For Each varItem In Me.SelectDate.ItemsSelected
strCriteria1 = strCriteria1 & "IT_Billing_Extract.InvoiceMonth =" &
Chr(34)
& Me.SelectDate.ItemData(varItem) & Chr(34) & " Or "
Next varItem

strCriteria1 = Left(strCriteria1, Len(strCriteria1) - 4)

For Each varItem In Me.OtherListBox.ItemsSelected
strCriteria2 = strCriteria2 & "IT_Billing_Extract.Field2 =" & Chr(34)
& Me.OtherListBox.ItemData(varItem) & Chr(34) & " Or "
Next varItem

strCriteria2 = Left(strCriteria2, Len(strCriteria2) - 4)

strCriteria = "(" & strCriteria1 & ") AND (" & strCriteria2 & ")"
I have a form that uses a multiselect listbox as criteria for a query based
on the user selection(s). I would like to add another multiselect listbox
Click to expand...
[quoted text clipped - 51 lines]
Thanks in advance for your help!!
Click to expand...
Click to expand...
 
Back
Top