multi-select list box query

[hi there]

hi all,

i'm trying to set up a parameter query from a form that the user can select multiple choices from a list box. basically, it's a form that the user ctrl+clicks multiple selections from the list box and then clicks on a control button that runs the query using the selections chosen from the list box as parameters for the query. i did a little research and found out i wanted to change the "Multi Select" property for the list box to "Extended" then i entered the following reference into the field i want the query to search from:

[Forms]![Units Search form]![Units]

however, it doesn't work. i was thinking of using some kind of an "or" statement, but i'm not sure what to do because the user has up to 33 choices from the list box and i don't think i need to set up up to 32 "or" statements for a user wanting to select 32 of the 33 choices. so basically i'm confused. could someone please help me with this. BIG thanks for any help you all could provide.

THANKS!

 

[hi there]

i've just be searching the archives and found the following code posted by R. Hicks:

Dim varItem As Variant
Dim strWhere As String

For Each varItem In Me![YourListBoxName].ItemsSelected
strWhere = strWhere & "YourUniqueIDField =" _
& Chr(39) & Me![YourListBoxName].Column(0, varItem) & Chr(39) & " Or "
Next varItem

strWhere = Left(strWhere, Len(strWhere) - 4) 'Remove the last " Or "

DoCmd.OpenReport "YourReportName", , , strWhere

the post says to attach this code to the OnClick event on a command button. i did this but i got a "Run-time error 5" saying i had an "invalid procedure call or argument on the following line of the code:

strWhere = Left(strWhere, Len(strWhere) - 4) 'Remove the last " Or "

i'm very new to VBA and i'm not sure what the problem is. could someone explain this to me. this is the exact code i have entered:

Private Sub Run_Report_Click()

Dim varItem As Variant
Dim strWhere As String

For Each varItem In Me![Units].ItemsSelected
strWhere = strWhere & "Units =" _
& Chr(39) & Me![Units].Column(0, varItem) & Chr(39) & " Or "
Next varItem

strWhere = Left(strWhere, Len(strWhere) - 4) 'Remove the last " Or "_

DoCmd.OpenReport "Select_Unit_Query_Report", , , strWhere

End Sub

Thanks everyone