Although I have not been able to test this out, it seems you should be able to use the "IN" keyword with the "ItemsSelected" property of the listbox.
SELECT * FROM <tableName> WHERE <fieldName> IN <formName>.ItemsSelected
Although I have not been able to test this out, it seems you should be able to use the "IN" keyword with the "ItemsSelected" property of the listbox.
SELECT * FROM <tableName> WHERE <fieldName> IN <formName>.ItemsSelected
I'd give someone $100 if that actually works for items in a multi select listbox ...
(well ok, a really nice smiley face anyway)
That would be pretty cool. And why wouldn't SQL be able to handle an array? So are you going to test it out for us?
That would be 'magic', since the default property of a listbox is its value property, corresponding to the bound column, and only corresponding to one row.. so in a multi select listbox it wouldn't make much sense...
The proof of the pudding is in the eating.
Yeah... it didn't work. Apparently properties of the listbox aren't really made available and the ItemsSelected is NOT an array. It's a collection of objects. I shoulda guessed that from the outset considering how the data is organized in a listbox.
I was able to make a little function that returns the values in comma delimited, but that's just a shortcut to what you guys have already posted here.
Yah that makes sense that the array would be the result of iterating the collection.
Edit I wonder if there is another property or method available?
I looked. There's a "Selected" but you have to pass in the column index value and the query didn't like that. "Selected(0)" just returns Run-time Error: ID-10T (Message: "Bad coder! Bad! Don't do that again!")
I tried this. I changed the listbox multi option from none to Simple. Then I selected the command button and changed the on click event. That's where I typed the code you gave me. Saved all and tried to run the form and it didnt work.Here is some code that loops through a multiselection listbox. The Debug.Print line sends the results to the Immediate Window. You can see the results in the immediate window. You can view the immediate window by using Ctrl+G on your keyboard.
You will need to replace the correct name for your listbox.
Code:Dim varSelection As Variant Dim lngID As Long 'Assumes the value in the first column is a number If Me.List0.ItemsSelected.Count = 0 Then Exit Sub Else For Each varSelection In Me.List0.ItemsSelected lngID = Me.List0.Column(0, varSelection) 'Insert code here to do something Debug.Print lngID Next varSelection End If
When I clicked on Debug, It highlighted this: lngID = Me.ListDistrict.Column(0, varSelection)
When I scroll my mouse over this line it says "IOngID=Me.List0.Column(0, varSelection) = False"
I am a total noob at coding :| Sorry
I am trying to do something like this but a Query instead of a report. I don't know what you mean by code to set up a SQL IN ()FWIW, I usually code to set up a SQL IN() clause from a multi select listbox, which I love doing and have a LOT of on my "do it yourself report builder" type of user interfaces.
Typically there are 3-5 or more multi select listbox where users must select one or more items, and then the command button builds up the sql from there.
Like:
dim x as long
dim strIn as String
for x=0 to me.ListboxName.ListCount-1
strIn=strIn & "'" & me.ListboxName.Itemdata(x) & "',"
next x
strIn=left(strIn,len(strIn)-1)
by that time, you have an IN() clause like this, if users selected RED, GREEN, BLUE from your listbox:
'RED','GREEN','BLUE'
So then if you have a saved query (let's say), that has everything EXCEPT the Where clause--which is a really great idea--then you can use something like:
report_ReportName.Recordsouce=CurrentDB.Querydefs( "name of saved query").Sql & " WHERE FIELDNAME IN(" & strIN & ")"
(make sure you do NOT allow Access to save a semicolon at the end of your query's SQL)
In general, keeping an empty (or meaningless, like Select "pizza") query in your database as a container to have your code simply dump SQL strings into...and manipulating the .SQL property of a DAO QueryDef object, is priceless and will open up almost limitless dynamic possibilities for you.
Private Sub Command14_Click()
Dim varSelection As Variant
Dim lngID As Long 'Assumes the value in the first column is a number
If Me.ListDistrict.ItemsSelected.Count = 0 Then
Exit Sub
Else
For Each varSelection In Me.ListDistrict.ItemsSelected
lngID = Me.ListDistrict.Column(0, varSelection)
'Insert code here to do something
Debug.Print lngID
Next varSelection
End If
End Sub
In my comment I kept it as List0 cuz I thought that's the example we were using
I entered this code as "on click" event for the command button that I have that is suppose to run the query
Make sure your ListBox Control has the correct properties. You will need the Multi Select property adjusted. Go ahead and use "Extended" for the Multi Select property in the "Other" tab in the property sheet.