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!!
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!!