Results 1 to 9 of 9

Search combo box as I type inside it

  1. #1
    bilalo is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    37

    Search combo box as I type inside it


    I have a combobox the shows customers in the following format: CustomerName -- CustomerPhone
    I can search for any customer by typing in the combobox the first letters of his/her name.
    How can I do the same with CustomerPhone?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    7,571
    Put the search query in the txtBox_change event.
    you can't wait the 2 seconds to finish typing?

  3. #3
    bilalo is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    37
    Quote Originally Posted by ranman256 View Post
    Put the search query in the txtBox_change event.
    you can't wait the 2 seconds to finish typing?
    Well off course I can wait for the 2 seconds, but if I can do it while typing, it would be much better.

    While typing in a txtBox, the change event does not update the value of txtBox, it triggers the event but it uses the old value of the txtBox, the data is updated only after the txtBox has lost focus.

  4. #4
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,082
    While typing in a txtBox, the change event does not update the value of txtBox, it triggers the event but it uses the old value of the txtBox,
    the change event can also reference the .text property which is what is actually displayed in the textbox at that time. Note the text property can only accessed whilst the control has the focus.

    OP started talking about a combo, now seems to be talking about a textbox. For clarity which is it?

    can search for any customer by typing in the combobox the first letters of his/her name.
    How can I do the same with CustomerPhone?
    the typeahead function for a combo only works in the first displayed column. So the easy answer is to have a second combo.

    An alternative, not tested, is in the on change event if the first char is numeric, change the rowsource from 'SELECT ID, Name, Phone....' to SELECT ID Phone, Name....'.
    Or perhaps the code changes the column widths from 0;2;2; to 0;0;2
    No idea if either will work but you can try. don't forget to change back if first char is not numeric

    If this is for a search form then use a textbox and in the change event have something like

    me.filter="[fldName] like '" & me.txtSearch.text & "*' OR [fldPhone] like '" & me.txtSearch.text & "*'"
    me.filteron=true

  5. #5
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    1,913
    Dynamic Multi Search.zip
    Here's an example that I have found very helpful. It uses a text box and displays search results on each keystroke.

  6. #6
    bilalo is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    37
    I think it is not a practical solution to filter a combobox as I am typing inside it, because it will update the combobox value directly after I type the first character and it will not allow me to continue typing.
    I managed to solve it this way.
    I used another textbox to filter the combobox

    Private Sub textbox_Change()
    combobox.value = DLookup("CustomerInfo", "Customer", "CustomerInfo like'*" & textbox.Text & "*'")
    End Sub

    The .text property is what I was looking for.
    Thank you all for your replies

  7. #7
    bilalo is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    37
    Quote Originally Posted by davegri View Post
    Dynamic Multi Search.zip
    Here's an example that I have found very helpful. It uses a text box and displays search results on each keystroke.
    Nice example, unfortunately it uses a listbox and not a combobox

  8. #8
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    5,884
    I think it is not a practical solution to filter a combobox as I am typing inside it, because it will update the combobox value directly after I type the first character and it will not allow me to continue typing.
    I try not to use them as bound controls because it's too easy mess up data when they're bound. All a user has to do is play with the list and create nonsense records. As data inputs, they're best left as unbound and use code to update, or use them strictly as controls for providing criteria or filtering values, which is what I mainly use them for.
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.
    Sometimes I talk to myself - like when I need expert advice.

  9. #9
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    1,913
    Nice example, unfortunately it uses a listbox and not a combobox
    Well, the rowsource SQL string for the list box could be used for a combo box rowsource as well.

    Agree with Micron that combo boxes used to enter search criteria should not be bound. That's a sure way to inadvertently contaminate existing data.

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

Similar Threads

  1. Replies: 4
    Last Post: 01-31-2018, 10:37 AM
  2. Replies: 8
    Last Post: 11-20-2015, 10:08 AM
  3. Search as you type - Combo box
    By lateral in forum Programming
    Replies: 20
    Last Post: 01-29-2015, 12:16 AM
  4. Replies: 5
    Last Post: 07-28-2014, 04:05 PM
  5. search as you type in combo box
    By pratim09 in forum Forms
    Replies: 3
    Last Post: 08-05-2011, 07:46 AM

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