Results 1 to 7 of 7
  1. #1
    MaxQTime is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Location
    Kelowna, B.C.
    Posts
    42

    Multi Field Search in List Box

    HI Everyone, I"m not quite sure how to do a multi field search with a single search box and have the results in a list box. I would think the it would probably have something to do with using an "or" query, but I'm not quite sure how to set that up with the query builder.



    I already have a single field search with a list box for the results using a query with Like "*" & [Form]![SearchForm]![SearchTxt] & "*". I would like to know how to get the list to auto requery as you type in the search box but I haven't figured it out yet.

    Also thank you to PBaldy for putting me on the right track with VBA to be able to set the list box up to allow someone to double click the item to open a form to that entry.

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    multi field
    Hopefully you mean multiple fields and not one field with multiple values?? What is a "single search box" - 1 textbox that you want to enter search data in? How would you get a different value (one for each field) into this 1 textbox? While possible, certainly not a good idea.
    maybe this will put you on the right track...
    http://allenbrowne.com/ser-62.html
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    MaxQTime is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Location
    Kelowna, B.C.
    Posts
    42
    Sorry for not being clear. I would like to have one text box for a user to enter search criteria, however I would like to display the results in a list box. For example if the user enters "John" in the text box I would like list box to show results with John as part of the first name or last name, ie "John Smith" or Mike Johnson".

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Hate to sound like I'm waffling, but do you want to handle most of this in code, or are you a macro person? I don't do macros but could probably get you started.

    The listbox ought to have "table/query" as its row source type, and either a query or sql statement as the row source. Here's where the path takes a turn. If query, your query can reference your form text box where in query design you'd see LIKE "*" & Forms!frmYourFormName.txtYourTextboxName & "*" in the name field. There may be a macro parameter that you can enter the same but I don't know. Something has to trigger the requery of the listbox, such as a button click event that has Me.lstListboxName.Requery in it. Again, if a macro, you can't use Me. - you'd need the form reference. Regardless, you'd get the query working first but the form would have to be open and a value in your textbox. You have to leave the textbox after entering the value or your query won't see a "committed" value.

    Handling it all in code is another matter.

  5. #5
    MaxQTime is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Location
    Kelowna, B.C.
    Posts
    42
    I would love to learn how to do it all in code. I have the first part already working as a search for a single field, eg "Company Name". My guess to get this to work would be to set up an or query in the query builder, however I'm not quite clear how that would work over multiple fields. If I put the Like "*" & form!formname!txtboxname & "*" in each of the criteria area for the fields in the query would it work? I guess I should do some testing on that.

    In terms of updating the list box as you type, I've tried putting the ListBox.Requery as a KeyUp event for the text box, but it doesn't seem to work.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    A find-as-you-type functionality might be quite complicated. See Allen Browne's article http://allenbrowne.com/AppFindAsUType.html. It filters a form but suppose could modify to set listbox RowSource.

    Note use of textbox Change event and Text property.
    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.

  7. #7
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    As for learning, that's something that has to be left up to you if it's not something quick and dirty here. There are TONS of tutorials, videos etc. out there. A couple of comments you've made makes it sound like you never looked at the link in post 2. There's someone who has gone out of their way to provide a free sample that you can download, play with and learn. Heck, if you break it, you can get another one for the same price you pay for the first one! This and the other links I spoke of is how you learn on your own. If it's any incentive, when I started, I didn't know how to present a message box. I think I've come a long way on my own, but am not even close to the end of the road. Free tip - thoroughly research anything before using it and don't take the first thing you read as gospel. If you did that, you'd probably think multi value fields and lookup fields in tables are the cat's meow.

    Key events are not useful for your current task. I wouldn't dream of trying to use one for a search function

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

Similar Threads

  1. Multi Select List Box on a Search Form
    By Vetgeorge in forum Forms
    Replies: 1
    Last Post: 09-28-2013, 08:11 PM
  2. Replies: 3
    Last Post: 09-02-2013, 04:33 PM
  3. Replies: 3
    Last Post: 03-10-2012, 06:15 PM
  4. 2 multi select list boxes on one search form
    By woodey2002 in forum Forms
    Replies: 2
    Last Post: 11-05-2010, 12:44 PM
  5. Replies: 1
    Last Post: 02-25-2009, 07:29 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