Page 1 of 5 12345 LastLast
Results 1 to 15 of 71
  1. #1
    KBAR12 is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Mar 2014
    Posts
    37

    Multi Select Listbox parameter for Query

    Hey all,


    I have been trying to create a list box to select multiple options as a parameter for my query for days now. I have been trying a lot of samples codes out there but I am such a noobie at VBA coding that I just can not figure it out
    Could someone please help me out????

    Lets assume the following:
    Listbox name = List0
    Command button name = Command0
    Form this listbox and command button are in: Form0
    The query that I want to use this multi select list box as a parameter for: Query0

    Could you please tell me step by step on how to go ahead and do this???

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    I don't think a multi select listbox will work in a parameterized query. The multi elect value is an array. You will need to iterate the array to retrieve the data.

  3. #3
    KBAR12 is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Mar 2014
    Posts
    37
    Well I have a List box with options from a certain column form the main data table to pick... Forexample "1", "2", "3", and "4".
    So Its a point where if I select "1" all the data that has the "1" will be returned to me in a query....
    But I want to be able to select "1" and "2" and have all the data that has "1" and "2" returned...

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    The only way I know to retrieve data from a multi select listbox is to use VBA

  5. #5
    KBAR12 is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Mar 2014
    Posts
    37
    Could you please send me a samlpe with explanation???? I don't understand the samples out there as if now

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Sure. When I get to a computer I will post a sample.

  7. #7
    KBAR12 is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Mar 2014
    Posts
    37
    Thanks so much dude

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

  9. #9
    KBAR12 is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Mar 2014
    Posts
    37
    Sweet. I wont be able to test it out till Monday I will get back to you how it goes....
    just a quick question though
    what is the "0" int he parenthesis of Column(0, varselection)

  10. #10
    trevor40's Avatar
    trevor40 is offline Advanced db Manager
    Windows XP Access 2003
    Join Date
    Feb 2014
    Location
    Australia
    Posts
    402
    list boxes & combo boxes can have multiple columns, reference to them start at zero. you can return multiple selections that have multiple columns of data

  11. #11
    KBAR12 is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Mar 2014
    Posts
    37
    oh okay. So I keep it 0 in my code too ? Sounds easy
    Btw under 'insert code here to do something.... I have no clue what to insert

  12. #12
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    I don't know what code you should insert there either. Let's see what shows up in the immediate window from the first column of your listbox and then worry about the other stuff later.

  13. #13
    KBAR12 is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Mar 2014
    Posts
    37
    okay sounds good thanks so much! I will get back to you on Monday

  14. #14
    trevor40's Avatar
    trevor40 is offline Advanced db Manager
    Windows XP Access 2003
    Join Date
    Feb 2014
    Location
    Australia
    Posts
    402
    the query could use the data from the list box, but you will have to get all selected values and put them together to use in the query, prior to running the query.

    you can then have a field on your form that holds the string returned by the above code and have the were clause based on the text field.
    you would have to build the were clause adding each value as you move through the selected items in the list, when done you could then run the query. depending on what information is returned will depend on the structure of the string, IE date, text, number. EG (were "1" and "2" and "3") just an example!.

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

Page 1 of 5 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
  •  
Other Forums: Microsoft Office Forums