Filter report with multiselect listbox
in [Getting Started]
|
Prev: ODBC Error
Next: how do i change pixels of my photos?
From: Mike Revis on 4 Oct 2009 19:33 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
From: Arvin Meyer [MVP] on 4 Oct 2009 21:57 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" <mike(a)nonohazmatexpress.com> wrote in message news:e7PbSsURKHA.1236(a)TK2MSFTNGP05.phx.gbl... > 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 >
From: Ken Snell on 5 Oct 2009 06:02 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/ "Arvin Meyer [MVP]" <arvinm(a)mvps.invalid> wrote in message news:Ohklw9VRKHA.4692(a)TK2MSFTNGP06.phx.gbl... > 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" <mike(a)nonohazmatexpress.com> wrote in message > news:e7PbSsURKHA.1236(a)TK2MSFTNGP05.phx.gbl... >> 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 >> > >
From: Mike Revis on 6 Oct 2009 19:34 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]" <arvinm(a)mvps.invalid> wrote in message news:Ohklw9VRKHA.4692(a)TK2MSFTNGP06.phx.gbl... > 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" <mike(a)nonohazmatexpress.com> wrote in message > news:e7PbSsURKHA.1236(a)TK2MSFTNGP05.phx.gbl... >> 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 >> > >
From: Mike Revis on 6 Oct 2009 19:36
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" <kthsneisllis9(a)ncoomcastt.renaetl> wrote in message news:eHyw$LaRKHA.2092(a)TK2MSFTNGP04.phx.gbl... > 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/ > > > > "Arvin Meyer [MVP]" <arvinm(a)mvps.invalid> wrote in message > news:Ohklw9VRKHA.4692(a)TK2MSFTNGP06.phx.gbl... >> 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" <mike(a)nonohazmatexpress.com> wrote in message >> news:e7PbSsURKHA.1236(a)TK2MSFTNGP05.phx.gbl... >>> 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 >>> >> >> > > |