Cascading List Boxes

[jeninOk]

Hi--
I have a form with a combo box that selects distinct from a lookup table,
called cboStates. When I select an item from it, a list box populates with a
list of values that match .
For example, if I choose Oklahoma, it shows all cities in Oklahoma in
lstCities. I then use that lstCities to filter a subform (the list is
multi-select).

I need to change the form so the States list is a Multi-select List also.
This way the user can select both Oklahoma and Texas and see a list of cities
in both states in lstCities from which to filter a subform.

How do I do this?
Thank you in advance

 

[jeninOk]

I should have added, the cities are not populating from a lookup, but rather
than from the cities available in a table of addresses WHERE the city = the
value in cboStates.

 

[Douglas J. Steele]

Assuming you rename the combo box to lstStates when you change it to a
multiselect list box (and that lstCities is being populated by the City
field in a table named Cities), something like the following untested air
code:

Private Sub lstStates_AfterUpdate()
Dim strStates As String
Dim varSelected As Variant

If Me.lstStates.ItemsSelected.Count > 0 Then
For Each varSelected In Me.lstStates.ItemsSelected
strStates = strStates & "'" & Me.lstStates.ItemData(varSelected) & "',
"
Next varSelected
strStates = Left(strStates, Len(strStates) - 2)
Me.lstCities.RowSource = "SELECT City " & _
"FROM Cities " & _
"WHERE State IN (" & strStates & ") " & _
"ORDER BY City"
End If

End Sub

 

[jeninOk]

Will test this today -- many thanks!!!!!!!

Assuming you rename the combo box to lstStates when you change it to a
multiselect list box (and that lstCities is being populated by the City
field in a table named Cities), something like the following untested air
code:

Private Sub lstStates_AfterUpdate()
Dim strStates As String
Dim varSelected As Variant

If Me.lstStates.ItemsSelected.Count > 0 Then
For Each varSelected In Me.lstStates.ItemsSelected
strStates = strStates & "'" & Me.lstStates.ItemData(varSelected) & "',
"
Next varSelected
strStates = Left(strStates, Len(strStates) - 2)
Me.lstCities.RowSource = "SELECT City " & _
"FROM Cities " & _
"WHERE State IN (" & strStates & ") " & _
"ORDER BY City"
End If

End Sub

 

[jeninOk]

You completely Rock!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! It's so dang simple,
but I've been really struggling with this!
Only note is that there is an extra " I removed just before the line with:
Next varSelected (for anyone else's benefit who may need this)

 

[Douglas J. Steele]

Actually, it wasn't an extra double quote. You were victim of line-wrap. The
line was supposed to end & "', ", but the closing double quote showed up as
a second line.

 

[jeninOk]

Thanks, Doug -- whew that could have been bad!!!!!