Passing multiple selections from a column to a combobox or list box

[tbayse]

Hello, I have a question about making multiple selection queries in Access. I am running windows XP and Access 2003. Up until this point I had a form where a user would make single selections from a combo box and the following combo box would filter based on the initial selection. I now would like to give the opportunity to make multiple selections and the following combo box would filter based on that. How do I go about declaring this variable and passing the selections onto the next column?

Thank you,

[ADezii]

Hello, I have a question about making multiple selection queries in Access. I am running windows XP and Access 2003. Up until this point I had a form where a user would make single selections from a combo box and the following combo box would filter based on the initial selection. I now would like to give the opportunity to make multiple selections and the following combo box would filter based on that. How do I go about declaring this variable and passing the selections onto the next column?

Thank you,

__1. You cannot make multiple selections from a Combo Box - you must use a List Box.
__2. I'll assume that it is a single column List Box, and that you want to make multiple selections from choices made within it.
__3. For demo purposes, we'll name the List Box lstTest.
__4. Set the Multi Select Property of the List Box to Extended. This will enable you to use both CTRL and SHIFT in your selections.
__5. To retrieve all Selected Items from lstTest and build your Criteria String:

Dim ctl As Control, varItm As Variant
 
Set ctl = Me![lstTest]
 
For Each varItm In ctl.ItemsSelected
  Debug.Print ctl.ItemData(varItm)
Next varItm

[tbayse]

Thank you for you reply and the code. I realized that combo boxes would no pass a multiple selection and have switched to a list box. In the code after I make a selection do I update the After Update event procedure with your code? Currently I have the code ...

Private Sub List81_AfterUpdate()
Me.Combo83 = Null
Me.Combo83.Requery
Me.Combo83 = Me.Combo83.ItemData(0)
End Sub

This code was used on combo boxes to pass single selections to the next box. Should I use a union of your code and this to perform the same? Thank you for your help.

[ADezii]

Thank you for you reply and the code. I realized that combo boxes would no pass a multiple selection and have switched to a list box. In the code after I make a selection do I update the After Update event procedure with your code? Currently I have the code ...

Private Sub List81_AfterUpdate()
Me.Combo83 = Null
Me.Combo83.Requery
Me.Combo83 = Me.Combo83.ItemData(0)
End Sub

This code was used on combo boxes to pass single selections to the next box. Should I use a union of your code and this to perform the same? Thank you for your help.

Dim ctl As Control, varItm As Variant
 
Set ctl = Me![lstTest]
 
Me![Combo83].RowSourceType = "Value List"   'necessary
Me![Combo83].RowSource = vbNullString       'Clear out
 
For Each varItm In ctl.ItemsSelected
   Me![Combo83].AddItem ctl.ItemData(varItm)
Next varItm

[tbayse]

Thank you again for you quick reply. Is there a way to post my access database so you can look at what I'm trying to do? Currently the code you have supplied have changed my row source for the combo box to something other then the query box was supposed to find. The original list box that I am trying to make multiple selections from had a field in it that was related to the following table. With a selection, or multiple selections the combo box that follows was to match that field and display items. For example the original list box was MODEL and the following combo box was ACTIVITY. This was a 1 to many relationship. With selection of a MODEL a MODEL ID was known, this was then matched through the query to ACTIVITY that had that model. I hope this makes sense. If not how can I make it clearer?

Thank you again, you have been a tremendous help so far.

Tim