Page 2 of 5 FirstFirst 12345 LastLast
Results 16 to 30 of 71

Multi Select Listbox parameter for Query

  1. #16
    Xipooo's Avatar
    Xipooo is offline Sr. Database Developer
    Windows 8 Access 2013
    Join Date
    Jan 2014
    Location
    Arizona
    Posts
    332
    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

  2. #17
    ipisors is offline Access Developer
    Windows XP Access 2007
    Join Date
    Sep 2013
    Posts
    119
    I'd give someone $100 if that actually works for items in a multi select listbox ...

    (well ok, a really nice smiley face anyway)

  3. #18
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,863
    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?

  4. #19
    ipisors is offline Access Developer
    Windows XP Access 2007
    Join Date
    Sep 2013
    Posts
    119
    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...

  5. #20
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,863
    The proof of the pudding is in the eating.

  6. #21
    Xipooo's Avatar
    Xipooo is offline Sr. Database Developer
    Windows 8 Access 2013
    Join Date
    Jan 2014
    Location
    Arizona
    Posts
    332
    Quote Originally Posted by ipisors View Post
    I'd give someone $100 if that actually works for items in a multi select listbox ...

    (well ok, a really nice smiley face anyway)
    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.

  7. #22
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,863
    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?

  8. #23
    Xipooo's Avatar
    Xipooo is offline Sr. Database Developer
    Windows 8 Access 2013
    Join Date
    Jan 2014
    Location
    Arizona
    Posts
    332
    Quote Originally Posted by ItsMe View Post
    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!")

  9. #24
    KBAR12 is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Mar 2014
    Posts
    37
    Quote Originally Posted by ItsMe View Post
    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
    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.
    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"

  10. #25
    KBAR12 is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Mar 2014
    Posts
    37
    I am a total noob at coding :| Sorry

  11. #26
    KBAR12 is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Mar 2014
    Posts
    37
    Quote Originally Posted by ipisors View Post
    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.
    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 ()

  12. #27
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,863
    Quote Originally Posted by KBAR12 View Post
    ...When I scroll my mouse over this line it says "IOngID=Me.List0.Column(0, varSelection) = False"...
    Post the code you are using. It seems you did not change the example code to match your combo name in all of the places necessary.

  13. #28
    KBAR12 is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Mar 2014
    Posts
    37
    Quote Originally Posted by ItsMe View Post
    Post the code you are using. It seems you did not change the example code to match your combo name in all of the places necessary.
    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

  14. #29
    KBAR12 is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Mar 2014
    Posts
    37
    I entered this code as "on click" event for the command button that I have that is suppose to run the query

  15. #30
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,863
    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.

Page 2 of 5 FirstFirst 12345 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Multi-Select Listbox
    By RayMilhon in forum Forms
    Replies: 5
    Last Post: 03-04-2014, 11:54 AM
  2. Search using a multi select listbox
    By noobaccess in forum Access
    Replies: 13
    Last Post: 12-04-2012, 07:06 AM
  3. Replies: 6
    Last Post: 11-02-2012, 12:48 PM
  4. multi select listbox
    By crowegreg in forum Forms
    Replies: 3
    Last Post: 07-28-2012, 01:48 PM
  5. Replies: 11
    Last Post: 09-22-2011, 01:13 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums