locked
Combobox search RRS feed

  • Question

  • Hi all,

    I have a combobox with several thousand questions listed. The user starts typing and the combobox will display the first record it finds that starts with that string.

    I need to search the combobox not only from the start of each string, but also if the words are contained in that string. For example, if the string in the combobox is "The fundamentals of fire fighting" and the user starts typing "The", the text is displayed, but if the user types "funda" the string should also be displayed.

    Is there a way to do this?

    Thanks

    Deon

    Thursday, October 12, 2017 9:49 AM

Answers

  • Hi Deon SA,

    you can try to refer code below.

    Private Sub Combo0_Change()
    Me.Combo0.RowSource = ""
    Dim strSQL As String
    strSQL = "SELECT Country from tblCountry1 where Country like '" & Me.Combo0.Text & "*'"
    Dim rs As Recordset
    Set rs = CurrentDb.OpenRecordset(strSQL)
    Do While Not rs.EOF
       Debug.Print rs("Country")
       Me.Combo0.AddItem (rs("Country").Value)
       rs.MoveNext
    Loop
    End Sub

    Output:

    in the code above you find that I just add the "*" at the end but here you also need to write a asterisk sign before text of combo box. so it find the string properly.

    you can also make changes in to query according to your need.

    Reference:

    Combo Box Auto Complete

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Monday, October 16, 2017 9:08 AM

All replies

  • For example, if the string in the combobox is "The fundamentals of fire fighting" and the user starts typing "The", the text is displayed, but if the user types "funda" the string should also be displayed.

    Is there a way to do this?

    Hi Deon,

    I do not know what the search mechanism is that is used in Comboboxes, but you could try to start the typing with an asterix:   *funda...

    In my applications I do not use Comboboxes, but use regular forms to select from. The user has far more possibilities to see the whole context of the record. By selecting a column, the input control is placed under that column, and the user can select from this column. Starting with an asterix makes that that the "search engine" changes to the "LIKE".

    Imb.

    Thursday, October 12, 2017 10:42 AM
  • Hi Deon SA,

    I'm afraid it would not be a good way to use a single ComboBox that has more than one hundred of items.

    Could you think about a few nested ComboBox, or grouped/categolized ComboBox?

    Or think about incremental search according to user's input/key stroke.

    Regards,


    Ashidacchi


    • Edited by Ashidacchi Thursday, October 12, 2017 11:31 AM
    Thursday, October 12, 2017 11:30 AM
  • Hi Deon SA,

    you can try to refer code below.

    Private Sub Combo0_Change()
    Me.Combo0.RowSource = ""
    Dim strSQL As String
    strSQL = "SELECT Country from tblCountry1 where Country like '" & Me.Combo0.Text & "*'"
    Dim rs As Recordset
    Set rs = CurrentDb.OpenRecordset(strSQL)
    Do While Not rs.EOF
       Debug.Print rs("Country")
       Me.Combo0.AddItem (rs("Country").Value)
       rs.MoveNext
    Loop
    End Sub

    Output:

    in the code above you find that I just add the "*" at the end but here you also need to write a asterisk sign before text of combo box. so it find the string properly.

    you can also make changes in to query according to your need.

    Reference:

    Combo Box Auto Complete

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Monday, October 16, 2017 9:08 AM