Results 1 to 9 of 9

Making a multiple choice list searchable

  1. #1
    dwheatley is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    21

    Making a multiple choice list searchable

    Hi,

    I have a box on a form which allows me to select more than one option in it.

    I want my query to return results based on what options are selected.

    I tried using [Forms]![SearchF]![Category] in the criteria in the query design but my query just ignores any selections in this list and displays results regardless of their category.



    Any ideas? I haven't made a multiple choice box like this searchable in a query before so have probably missed something basic.

    Thanks.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    5,714
    if by 'box' you mean a listbox, the better way is to use a table. A 'picked' table where user dbl-clicks the option from a listbox,
    it runs a query to add it to the 'pick' table.
    then simply join the pick table to the data table so only THOSE items are searched.

    Click image for larger version. 

Name:	pick state-lbl.png 
Views:	21 
Size:	34.2 KB 
ID:	31557

  3. #3
    ssanfu is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    7,008
    To answer your question, you need to loop through the list box row source to see if an item is selected.
    The basic code look like
    Code:
        Dim valSelect As Variant
        Dim strValue As String ' just used for the demonstration
         
            For Each valSelect In Me.listboxName.ItemsSelected
                strValue = strValue & "'" & Me.listboxName.ItemData(valSelect) & "', "
            Next valSelect
         
            ' to remove trailing comma
            strValue = Left(strValue, Len(strValue)-2)
    Modify the code depending on what you are going to do with the results.


    Good luck with your project.........
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  4. #4
    dwheatley is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    21
    Steve - does that code need to be attached to the on-click event of my Run Query button on my form, or should it be inputted somewhere in the Query itself?

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    42,638
    It is VBA code so it definitely does not go in query.
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window, click Go Advanced below the Quick Reply window.
    If any suggestion resolves your issue, please use the Thread Tools dropdown above first post and mark as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  6. #6
    dwheatley is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    21
    I haven't used VBA for anything yet, so far I have been doing everything with SQL and in Query Design.

    I already have a functioning form in which users make selections from combo boxes, and then click a button 'Run Query', which displays results based on the options chosen from those combo boxes.

    I basically just want to add the possibility of a user selecting more than one option in a given field, in this instance I want the query to return results from all of the categories selected by the user in a list box.

    Is anyone able to offer some guidance based on this?

    Thanks again.

  7. #7
    Micron is online now VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    3,138
    How do we know for sure that it's a list box and not a mv field with a multi select combo? OP only said it was a box.

    I presume that regardless the only option is code since I can't play with ms combos.
    - "doesn't work" is no help.Error #s/text do. State what's happening.
    - Use code tags for code/sql; show where errors occur
    Make all suggested changes in copies of your database or to its objects

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    42,638
    Review http://allenbrowne.com/ser-62.html, the tutorial has a link to another about using multi-select listbox to build filter criteria.

    I never use multi-value field but the search techniques should be able to apply. Review https://support.office.com/en-us/art...624E1E323A#bm2
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window, click Go Advanced below the Quick Reply window.
    If any suggestion resolves your issue, please use the Thread Tools dropdown above first post and mark as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  9. #9
    dwheatley is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    21
    Thanks for all of your help everyone. I can see that it would require VBA for what I am trying to accomplish and I don't have the time to learn that at the moment. I have therefore decided to go back to a combo box where the user can search by one option, rather than telling the query to show results from several categories.

    June7 - those guides look useful for the future, thanks again!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 09-14-2017, 01:24 AM
  2. Replies: 0
    Last Post: 03-19-2017, 12:29 PM
  3. Replies: 5
    Last Post: 06-25-2014, 08:19 AM
  4. Associate Number with Each List Box Choice
    By beribimba in forum Access
    Replies: 8
    Last Post: 08-25-2011, 07:31 AM
  5. List box update from combo box choice
    By allykid in forum Forms
    Replies: 1
    Last Post: 03-08-2011, 09:06 PM

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