VBA-- Open a Form with filter accoding to LISTBOX multiple choices

[Martin \(Martin Lee\)]

I have LISTBOX62 with several Dealer names in it for multiple choose( for
example Dealer1, Dealer2, Dealer3, ...)

I now want to multiple select the Dealer names in the LISTBOX62, and then
click a BUTTON32 to open a FORM38 with the filter (criteria) of the selected
Dealer names.

How to write this VBA? Thank you.


P.S. I have tried the following VBA, but can't work:

Dim varItem As Variant
Dim itemList As String
For Each varItem In List62.ItemsSelected
itemList = itemList & "'" & List62.ItemData(varItem) & "',"
Next varItem

If itemList <> "" Then
itemList = Left(itemList, Len(itemList) - 1)

DoCmd.OpenForm "FORM38"
Forms!FORM38.Filter = "DEALERNAME like '" & ItemList & "'"
Forms!FORM38.FilterOn = True

EndIf

 

[Allen Browne]

That might work if you change the 2nd last line to:
Forms!FORM38.Filter = "[DEALERNAME] IN (" & ItemList & ")"

If that doesn't work, open your table in deisgn view, and find what the Data
Type of the DealerName field is. If Number (typically because it is a combo
looking up another table), the code won't work unless you concatenate the
numbers (not the names), and omit the quotes.

 

[Martin \(Martin Lee\)]

Hi Allen, your idea can't work.

And te DealerName field is text type.

Thanks anyway.

Martin

 

[Allen Browne]

It the field is a Text data type, and the bound column of the list box is a
text data type, then the suggested line should work, i.e.
Forms!FORM38.Filter = "[DEALERNAME] IN (" & ItemList & ")"