Results 1 to 9 of 9
  1. #1
    dwheatley is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    22

    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 online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    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:	23 
Size:	34.2 KB 
ID:	31557

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    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.........

  4. #4
    dwheatley is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    22
    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 online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    It is VBA code so it definitely does not go in query.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    dwheatley is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    22
    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 offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    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
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    dwheatley is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    22
    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, 02:24 AM
  2. Replies: 0
    Last Post: 03-19-2017, 01:29 PM
  3. Replies: 5
    Last Post: 06-25-2014, 09:19 AM
  4. Associate Number with Each List Box Choice
    By beribimba in forum Access
    Replies: 8
    Last Post: 08-25-2011, 08:31 AM
  5. List box update from combo box choice
    By allykid in forum Forms
    Replies: 1
    Last Post: 03-08-2011, 10: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
  •  
Other Forums: Microsoft Office Forums