locked
Multiple Select Cascading List Box RRS feed

  • Question

  • Hello, 

    I currently have a list box that when I select a value, it updates another list box to show relevant values (for example if I select a state, the other list box will update with cities only from the selected state.) 

    This works great, however I am looking for a way to select multi values from the first listbox.  (Select multiple states and have the cities from each state selected show up in the other list box)   When I turn on multi select for the first list box, the other list box no longer populates.   The query seems to only handle one value at a time and not multiple values. 

    Does anyone know how I can select multiple values or states from the first list box, have have the second list box show accordingly? 

    Thanks!

    Saturday, January 9, 2016 2:16 AM

Answers

  • Hi. That's correct! A multi-select listbox will *always* return a Null value. To grab all the selected items from the listbox, you'll have to use code to loop through its ItemsSelected collection. For example:

    Dim var As Variant
    
    For Each var in Me.ListboxName.ItemsSelected
       Debug.Print Me.ListboxName.ItemData(var)
    Next

    Hope that helps...

    Saturday, January 9, 2016 2:28 AM
  • Does anyone know how I can select multiple values or states from the first list box, have have the second list box show accordingly?
    You'll find something similar in MultiSelect.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    In this little demo file the bottom two buttons open forms in which a multi-select list box of employees populates a subform of projects.  In one case the subform is populated with projects to which any of the selected employees is assigned, in the other with projects to which all of the selected employees are assigned.  It would be a simple task to replace the subform with another list box, which would be requeried in the first list box's AfterUpdate event procedure, rather than Requerying the subform as at present.

    In the demo the subform's query (which would become the RowSource of your second list box) calls the InParam function, which together with the InToken function, simulates the use of the IN operator in a query which accepts a parameter, in this case a hidden control on the parent form which is populated with a comma separated list of the selected EmployeeID values.  The IN operator itself does not accept a parameter as its argument, hence the need for these functions.

    The link to the MSKB article where the two functions are published is given in the demo.  You'll find it also has a solution using the Instr function, but this is less reliable as it can easily return specious substring matches, which the use of the InParam and GetToken functions avoids.


    Ken Sheridan, Stafford, England

    Saturday, January 9, 2016 4:52 PM

All replies

  • Hi. That's correct! A multi-select listbox will *always* return a Null value. To grab all the selected items from the listbox, you'll have to use code to loop through its ItemsSelected collection. For example:

    Dim var As Variant
    
    For Each var in Me.ListboxName.ItemsSelected
       Debug.Print Me.ListboxName.ItemData(var)
    Next

    Hope that helps...

    Saturday, January 9, 2016 2:28 AM
  • Does anyone know how I can select multiple values or states from the first list box, have have the second list box show accordingly?
    You'll find something similar in MultiSelect.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    In this little demo file the bottom two buttons open forms in which a multi-select list box of employees populates a subform of projects.  In one case the subform is populated with projects to which any of the selected employees is assigned, in the other with projects to which all of the selected employees are assigned.  It would be a simple task to replace the subform with another list box, which would be requeried in the first list box's AfterUpdate event procedure, rather than Requerying the subform as at present.

    In the demo the subform's query (which would become the RowSource of your second list box) calls the InParam function, which together with the InToken function, simulates the use of the IN operator in a query which accepts a parameter, in this case a hidden control on the parent form which is populated with a comma separated list of the selected EmployeeID values.  The IN operator itself does not accept a parameter as its argument, hence the need for these functions.

    The link to the MSKB article where the two functions are published is given in the demo.  You'll find it also has a solution using the Instr function, but this is less reliable as it can easily return specious substring matches, which the use of the InParam and GetToken functions avoids.


    Ken Sheridan, Stafford, England

    Saturday, January 9, 2016 4:52 PM
  • Thank you!
    Sunday, January 10, 2016 12:59 AM
  • Thank you!
    Sunday, January 10, 2016 12:59 AM
  • You're welcome. Ken and I were happy to assist. Let us know how it goes...  
    Sunday, January 10, 2016 1:03 AM