If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Filter report with multiselect listbox
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 |
#2
|
|||
|
|||
Filter report with multiselect listbox
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 "Mike Revis" wrote in message ... 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 |
#3
|
|||
|
|||
Filter report with multiselect listbox
Example database that shows how to build SQL for a report, using various
controls on a form, including a multiselect listbox, is he Using Controls to filter a form's data http://www.accessmvp.com/KDSnell/Sam...htm#FilterForm This demonstrates the code that Arvin has posted. -- Ken Snell http://www.accessmvp.com/KDSnell/ "Arvin Meyer [MVP]" wrote in message ... 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 "Mike Revis" wrote in message ... 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 |
#4
|
|||
|
|||
Filter report with multiselect listbox
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 "Arvin Meyer [MVP]" wrote in message ... 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 "Mike Revis" wrote in message ... 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 |
#5
|
|||
|
|||
Filter report with multiselect listbox
Thank You Ken!!
This is just too cool. It opens up, to me, a whole new way of accessing my data. Best regards, Mike "Ken Snell" wrote in message ... Example database that shows how to build SQL for a report, using various controls on a form, including a multiselect listbox, is he Using Controls to filter a form's data http://www.accessmvp.com/KDSnell/Sam...htm#FilterForm This demonstrates the code that Arvin has posted. -- Ken Snell http://www.accessmvp.com/KDSnell/ "Arvin Meyer [MVP]" wrote in message ... 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 "Mike Revis" wrote in message ... 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 |
#6
|
|||
|
|||
Filter report with multiselect listbox
Mike:
Using your existing report, list box and button, you can also do it with code like this in the button's Click event procedu 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 Mike Revis wrote: 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 -- Message posted via http://www.accessmonster.com |
Thread Tools | |
Display Modes | |
|
|