Filter report with multiselect listbox

[Mike Revis]

Hi Group,
Access 2007. WinXPpro.

My app has a report that lists parts by category.

I found some earlier discussion in the group about filtering a report based
on a combo box selection.

I was able to do that and have the report show only one category.

I would like to be able to filter the report based on a multiselect listbox.

I adapted the combo box action to a list box and it works as long as I have
multiselect = None.

When I change the listbox to multiselect simple or extended the report opens
with no data.

My command button code as follows.

DoCmd.OpenReport "rptPartsByCategory", acPreview, , " [Category]=' " &
Me.listCategory & " ' "

As always any thoughts, comments or suggestions are welcome.

Best regards,
Mike

 

[Arvin Meyer [MVP]]

What you need is a comma separated list in a text box which uses each of the
items selected. Here's an example using a hidden text box named txtSelected:

With Me!lstElevation
If .MultiSelect = 0 Then
Me!txtSelected = .Value
Else
For Each varItem In .ItemsSelected
strList = strList & .Column(0, varItem) & ","
Next varItem
If strList <> "" Then
strList = Left$(strList, Len(strList) - 1)
End If
Me.txtSelected = strList
End If

End With

Then txtSelected is used in an IN clause in your code:

strSQL = "SELECT * FROM tblItem WHERE (((CategoryID) In (" & Me.txtSelected
& "));"
Me.RecordSource = strSQL

 

[Ken Snell]

Example database that shows how to build SQL for a report, using various
controls on a form, including a multiselect listbox, is here:

Using Controls to filter a form's data
http://www.accessmvp.com/KDSnell/SampleDBs.htm#FilterForm

This demonstrates the code that Arvin has posted.
--

Ken Snell
http://www.accessmvp.com/KDSnell/

What you need is a comma separated list in a text box which uses each of
the items selected. Here's an example using a hidden text box named
txtSelected:

With Me!lstElevation
If .MultiSelect = 0 Then
Me!txtSelected = .Value
Else
For Each varItem In .ItemsSelected
strList = strList & .Column(0, varItem) & ","
Next varItem
If strList <> "" Then
strList = Left$(strList, Len(strList) - 1)
End If
Me.txtSelected = strList
End If

End With

Then txtSelected is used in an IN clause in your code:

strSQL = "SELECT * FROM tblItem WHERE (((CategoryID) In (" &
Me.txtSelected & "));"
Me.RecordSource = strSQL
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi Group,
Access 2007. WinXPpro.

My app has a report that lists parts by category.

I found some earlier discussion in the group about filtering a report
based on a combo box selection.

I was able to do that and have the report show only one category.

I would like to be able to filter the report based on a multiselect
listbox.

I adapted the combo box action to a list box and it works as long as I
have multiselect = None.

When I change the listbox to multiselect simple or extended the report
opens with no data.

My command button code as follows.

DoCmd.OpenReport "rptPartsByCategory", acPreview, , " [Category]=' " &
Me.listCategory & " ' "

As always any thoughts, comments or suggestions are welcome.

Best regards,
Mike

 

[Mike Revis]

Thank You Arvin!!
It would take me forever to not be able to figure this out.
All the people that volunteer their time here are the best.

Best regards,
Mike

What you need is a comma separated list in a text box which uses each of
the items selected. Here's an example using a hidden text box named
txtSelected:

With Me!lstElevation
If .MultiSelect = 0 Then
Me!txtSelected = .Value
Else
For Each varItem In .ItemsSelected
strList = strList & .Column(0, varItem) & ","
Next varItem
If strList <> "" Then
strList = Left$(strList, Len(strList) - 1)
End If
Me.txtSelected = strList
End If

End With

Then txtSelected is used in an IN clause in your code:

strSQL = "SELECT * FROM tblItem WHERE (((CategoryID) In (" &
Me.txtSelected & "));"
Me.RecordSource = strSQL
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi Group,
Access 2007. WinXPpro.

My app has a report that lists parts by category.

I found some earlier discussion in the group about filtering a report
based on a combo box selection.

I was able to do that and have the report show only one category.

I would like to be able to filter the report based on a multiselect
listbox.

I adapted the combo box action to a list box and it works as long as I
have multiselect = None.

When I change the listbox to multiselect simple or extended the report
opens with no data.

My command button code as follows.

DoCmd.OpenReport "rptPartsByCategory", acPreview, , " [Category]=' " &
Me.listCategory & " ' "

As always any thoughts, comments or suggestions are welcome.

Best regards,
Mike

 

[Mike Revis]

Thank You Ken!!
This is just too cool.
It opens up, to me, a whole new way of accessing my data.

Best regards,
Mike

Example database that shows how to build SQL for a report, using various
controls on a form, including a multiselect listbox, is here:

Using Controls to filter a form's data
http://www.accessmvp.com/KDSnell/SampleDBs.htm#FilterForm

This demonstrates the code that Arvin has posted.
--

Ken Snell
http://www.accessmvp.com/KDSnell/

What you need is a comma separated list in a text box which uses each of
the items selected. Here's an example using a hidden text box named
txtSelected:

With Me!lstElevation
If .MultiSelect = 0 Then
Me!txtSelected = .Value
Else
For Each varItem In .ItemsSelected
strList = strList & .Column(0, varItem) & ","
Next varItem
If strList <> "" Then
strList = Left$(strList, Len(strList) - 1)
End If
Me.txtSelected = strList
End If

End With

Then txtSelected is used in an IN clause in your code:

strSQL = "SELECT * FROM tblItem WHERE (((CategoryID) In (" &
Me.txtSelected & "));"
Me.RecordSource = strSQL
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi Group,
Access 2007. WinXPpro.

My app has a report that lists parts by category.

I found some earlier discussion in the group about filtering a report
based on a combo box selection.

I was able to do that and have the report show only one category.

I would like to be able to filter the report based on a multiselect
listbox.

I adapted the combo box action to a list box and it works as long as I
have multiselect = None.

When I change the listbox to multiselect simple or extended the report
opens with no data.

My command button code as follows.

DoCmd.OpenReport "rptPartsByCategory", acPreview, , " [Category]=' " &
Me.listCategory & " ' "

As always any thoughts, comments or suggestions are welcome.

Best regards,
Mike

 

[KenSheridan via AccessMonster.com]

Mike:

Using your existing report, list box and button, you can also do it with code
like this in the button's Click event procedure:

Dim varItem As Variant
Dim strCategoryList As String
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.listCategory

If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strCategoryList = strCategoryList & ",""" & _
ctrl.ItemData(varItem) & """"
Next varItem

' remove leading comma
strCategoryList = Mid(strCategoryList, 2)

strCriteria = "Category In(" & strCategoryList & ")"

DoCmd.OpenReport "rptPartsByCategory", _
View:=acViewPreview, _
WhereCondition:=strCriteria
Else
MsgBox "No categories selected", vbInformation, "Warning"
End If

Ken Sheridan
Stafford, England

Hi Group,
Access 2007. WinXPpro.

My app has a report that lists parts by category.

I found some earlier discussion in the group about filtering a report based
on a combo box selection.

I was able to do that and have the report show only one category.

I would like to be able to filter the report based on a multiselect listbox.

I adapted the combo box action to a list box and it works as long as I have
multiselect = None.

When I change the listbox to multiselect simple or extended the report opens
with no data.

My command button code as follows.

DoCmd.OpenReport "rptPartsByCategory", acPreview, , " [Category]=' " &
Me.listCategory & " ' "

As always any thoughts, comments or suggestions are welcome.

Best regards,
Mike