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

  • Thread starter Thread starter Martin \(Martin Lee\)
  • Start date Start date
M

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
 
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.
 
Hi Allen, your idea can't work.

And te DealerName field is text type.

Thanks anyway.

Martin
 
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 & ")"
 
Back
Top