multiple multiselect listbox to limit records in report

[Kelly S]

Can someone help me with the proper code in order to use multiple multiselect
listboxes to limit records in a report. I want to have two listboxes, one
with contractor types and one with regions. I want to pick options from each
listbox and limit my report based on both.

I have used the following code from the
http://www.mvps.org/access/forms/frm0007.htm website and can run my report
using one multiselect listbox, but I can't seem to get the syntax down for
two multiselect listboxes.

Also, do I need to use separate forms for the two listboxes or can I put
them both on one form?


************ Code Start **********
Private Sub btnTestQuery_Click()
Dim vItm As Variant
Dim stWhat As String
Dim stCriteria As String
Dim stSQL As String
Dim loqd As QueryDef

stWhat = "": stCriteria = ","
For Each vItm In Me!mslbxTest.ItemsSelected
stWhat = stWhat & Me!mslbxTest.ItemData(vItm)
stWhat = stWhat & stCriteria
Next vItm
Me!txtCriteria = CStr(left$(stWhat, Len(stWhat) - Len(stCriteria)))
Set loqd = CurrentDb.QueryDefs("qryMultiSelTest")
stSQL = "SELECT EmployeeID, LastName, FirstName, TitleOfCourtesy, "
stSQL = stSQL & "Title FROM Employees WHERE EmployeeID"
stSQL = stSQL & " IN (" & Me!txtCriteria & ")"
loqd.SQL = stSQL
loqd.Close
DoCmd.OpenQuery "qryMultiSelTest"
End Sub
'************ Code End **********

Thank you so much for any help,

Kelly S

 

[Klatuu]

Here is some example code that will do this form you. I have copied some
snipets from a form where I have 7 multiselect list boxes to deal with.

This function will return the criteria portion of a Where argument.

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function


This next bit shows how you can string the two together so it will include
either, both, or neither.


'Billable Product Offering
strWhere = BuildWhereCondition("lstBillProdOffering")
strFieldName = "quniBPOReportsPVA.ProjectID "
If Len(strWhere) > 0 Then
strWhere = strFieldName & strWhere
End If
'Master Activity
strWhereNext = BuildWhereCondition("lstMActivity")
strFieldName = "tblMasterActivity.MActivity "
If Len(strWhereNext) > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND " & strFieldName & strWhereNext
Else
strWhere = strFieldName & strWhereNext
End If
End If

 

[Chuck]

Can someone help me with the proper code in order to use multiple multiselect
listboxes to limit records in a report. I want to have two listboxes, one
with contractor types and one with regions. I want to pick options from each
listbox and limit my report based on both.

I have used the following code from the
http://www.mvps.org/access/forms/frm0007.htm website and can run my report
using one multiselect listbox, but I can't seem to get the syntax down for
two multiselect listboxes.

Also, do I need to use separate forms for the two listboxes or can I put
them both on one form?


************ Code Start **********
Private Sub btnTestQuery_Click()
Dim vItm As Variant
Dim stWhat As String
Dim stCriteria As String
Dim stSQL As String
Dim loqd As QueryDef

stWhat = "": stCriteria = ","
For Each vItm In Me!mslbxTest.ItemsSelected
stWhat = stWhat & Me!mslbxTest.ItemData(vItm)
stWhat = stWhat & stCriteria
Next vItm
Me!txtCriteria = CStr(left$(stWhat, Len(stWhat) - Len(stCriteria)))
Set loqd = CurrentDb.QueryDefs("qryMultiSelTest")
stSQL = "SELECT EmployeeID, LastName, FirstName, TitleOfCourtesy, "
stSQL = stSQL & "Title FROM Employees WHERE EmployeeID"
stSQL = stSQL & " IN (" & Me!txtCriteria & ")"
loqd.SQL = stSQL
loqd.Close
DoCmd.OpenQuery "qryMultiSelTest"
End Sub
'************ Code End **********

Thank you so much for any help,

Kelly S

The two list boxes can be in a single form.
The short answer to you problem is you need to make the record source of the
second list box be determined from , (a function of), the records chosen in the
first list box.

If someone can show some nice concise, neat compact code, us it.
If not I'll show you a Rube-Goldburg, Mickey-Mouse, monstrosity that can do the
job.

 

[Klatuu]

Chuck,
Not really necessary to cascade the list boxes. See my previous post for
some example code.

 

[Chuck]

Chuck,
Not really necessary to cascade the list boxes. See my previous post for
some example code.

Thank you! I intuitively knew there just had to be a better to do the job.

Chuck
--

 

[Chuck]

Here is some example code that will do this form you. I have copied some
snipets from a form where I have 7 multiselect list boxes to deal with.

This function will return the criteria portion of a Where argument.

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function


This next bit shows how you can string the two together so it will include
either, both, or neither.


'Billable Product Offering
strWhere = BuildWhereCondition("lstBillProdOffering")
strFieldName = "quniBPOReportsPVA.ProjectID "
If Len(strWhere) > 0 Then
strWhere = strFieldName & strWhere
End If
'Master Activity
strWhereNext = BuildWhereCondition("lstMActivity")
strFieldName = "tblMasterActivity.MActivity "
If Len(strWhereNext) > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND " & strFieldName & strWhereNext
Else
strWhere = strFieldName & strWhereNext
End If
End If

Your code looks straight forward and simple. But I still couldn't make it
work. I have a simple Database with one table, One form, one query that gets
its data from the table, one query list only the Mfg field and Total is set to
Group By, and one report that gets its data from the query that lists all the
data. The form has one Multiselect listbox that gets its data from the query
showing only the list of Mfgs. I want to filter the field 'Mfg' in the 'big'
query for the report. The code is located in the form module. I have been
unable to filter the query with your code.
Even the very simple statement of:
Case 0 'Include All
strWhere = ""
does not return any data.
No errors occur. The code runs without a hiccup. It just simply does not
return any data. No errors occur when stepping thru the code. No selection,
one selection and several selections step thru the proper Case.

To check Case 1:
In the form list box, I clicked on only the ABC Mfg
In the SQL statement I first tried: WHERE Mfg=BuildWhereCondition.
No data returned so I backed off to: Where Mfg=strWhere. Still no data.
Then tried Where Mfg=ABC and the query did return only the records for the ABC
Mfg.

Chuck
--