I have 2 unbound list boxes on my form.

The first list box is based off a query and lists the States.

I would like the second list box to be populated with cities based off of what states the user selects.

I can not get my code to work. Can anyone help?

Private Sub lstStates_AfterUpdate()

Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String

For Each varItem In Me!lstStates.ItemsSelected
strCriteria = strCriteria & ",'" & Me!lstStates.ItemData(varItem) & "'"
Next varItem

strCriteria = Right(strCriteria, Len(strCriteria) - 1)

strSQL = "SELECT * FROM tblStatesCites " & _
"WHERE tblStatesCites.States IN(" & strCriteria & ");"

lstCities.ListSource = Array(strSQL)

End Sub

Hi

try changing this line

lstCities.ListSource = Array(strSQL)

to

List boxes don't have a ListSource property: they have a RowSource property. As well, your Array statement is incorrect: you'd need to have the recordset returned by the SQL string in order for it to be doing anything.

Try:



You might consider putting an ORDER BY clause in your SQL statement to be more user-friendly. As well, it's generally considered a bad idea to use SELECT *, especially for the RowSource of a list box or combo box. Consider explicitly listing the fields of interest.

Thank you so much! And good idea about the ORDER. I will do that too.

Jodi