CODEWITHSUNDEEP
ms-accessvba
Regis Santos
Regis Santos

Reputation: 3749

ListBox with Rowsource by multi selectin for other ListBox in VBA Access

I have two listbox: listbox1 and listbox2. How do I get that listbox2.rowsource = filter where filter is equal to a multiple selections from listbox1?enter image description here

Upvotes: 0

Views: 3202

Answers (1)

Fabio Pereira
Fabio Pereira

Reputation: 348

You can loop through the selected items in listbox1, and concatenate them into a SQL string to feed the rowsource on listbox2.

I couldn't see the image you posted, but I've written a snippet (just "air coding", may not work) that should give you an idea.

Private Sub listbox1_AfterUpdate()

Dim itm as Variant, sql as String

For Each itm In listbox1.ItemsSelected
    sql = sql & " OR field1 = '" & listbox1.ItemData(itm) & "'"
Next

sql = "SELECT field FROM table WHERE " & Mid(sql, 5)

listbox2.RowSourceType = "Table/Query"
listbox2.RowSource = sql

End Sub

You can also try this way:

Dim values as String, itm as variant

For Each itm In listbox1.ItemsSelected
    values = values & """" & listbox1.ItemData(itm) & """;"
Next

listbox2.RowSourceType = "Value List"
listbox2.RowSource = values

Upvotes: 1

Related Questions