Find as we typed

[hfsitumo2001]

Part of my form is to enter customer's info based on Combo Box. My combo is working if we type the beginning part of the customer name. How can we make it even 3 characters in the middle of the name, can auto find the closest names?. If any sample of database, could you uploaded it here please?

Thank you

 

[theDBguy]

Check out this thread/post.

Solved - Search as you type with in Combobox / instant filter combobox itself

I am looking for code "want to filter combo-box (drop down data) by key word while typing". On a form I have three comboboxes. 1) for Articles 2) for Company 3) for Catalogues I was able to do this search filter for one combobox (Articles), but dont know how to change it for the other two...

www.access-programmers.co.uk

 

[GinaWhipp]

You will need to adjust to fit your Control names and Row Source data but this should do the trick...

Private Sub cboSearch_KeyUp(KeyCode As Integer, Shift As Integer)

    If Len(Me.cboSearch.Text) >= 2 Then
         Me.cboSearch.RowSource = "SELECT epEntityID, ecValue FROM qrySearch WHERE ecValue LIKE '*" & Me.cboSearch.Text & "*'"
         Me.cboSearch.Dropdown
    Else
         Me.cboSearch.RowSource = ""
    End If

End Sub

For After_Update of the Combo Box...

    If Me.cboSearch <> "" Then
        Me.Recordset.FindFirst "epEntityID = " & Nz(Me.cboSearch, 0)
    End If

Then for Not_In_list you can use (providing you don't want them to add new value to the Combo Box...

Private Sub cboSearch_NotInList(NewData As String, Response As Integer)
    
    MsgBox NewData & " is not in the list," & vbCrLf & _
        "please choose an item from the list." _
        , vbExclamation, "Not in List"
    Me.cboSearch.Undo
    Me.cboSearch.Requery
    Me.cboSearch.RowSource = "qrySearch"
    Response = acDataErrContinue

End Sub

 

[hfsitumo2001]

Thanks DB, I will try to adjust, but in the sample above which one is the name of Control names and Row Source data.

In my case the row source of the combo is this:
SELECT DISTINCT Customers.CustomerID, Customers.Customer FROM Customers ORDER BY Customers.Customer;

 

[hfsitumo2001]

You will need to adjust to fit your Control names and Row Source data but this should do the trick...

Private Sub cboSearch_KeyUp(KeyCode As Integer, Shift As Integer)

    If Len(Me.cboSearch.Text) >= 2 Then
         Me.cboSearch.RowSource = "SELECT epEntityID, ecValue FROM qrySearch WHERE ecValue LIKE '*" & Me.cboSearch.Text & "*'"
         Me.cboSearch.Dropdown
    Else
         Me.cboSearch.RowSource = ""
    End If

End Sub

For After_Update of the Combo Box...

    If Me.cboSearch <> "" Then
        Me.Recordset.FindFirst "epEntityID = " & Nz(Me.cboSearch, 0)
    End If

Then for Not_In_list you can use (providing you don't want them to add new value to the Combo Box...

Private Sub cboSearch_NotInList(NewData As String, Response As Integer)
   
    MsgBox NewData & " is not in the list," & vbCrLf & _
        "please choose an item from the list." _
        , vbExclamation, "Not in List"
    Me.cboSearch.Undo
    Me.cboSearch.Requery
    Me.cboSearch.RowSource = "qrySearch"
    Response = acDataErrContinue

End Sub

Hi Gina thank you for your codes, sorry my question to DBGUY should have been to you. I have tried your code but it didn't work. Do you think it is because the data property is set to Auto Expand to NO?, and my query search is with SELECT DISTINCT?

Thank you

 

[GinaWhipp]

Hmm, yep, forgot that, set it to *Yes*.

 

[MajP]

Here is an update of my popular FAYT class. Only takes one line of code to make this work. You need to import the class module. Then instantiate a FAYT variable. No other code or even procedures required.
1. Auto expands
2. Handles apostrophes
3. Handles international characters as demonstrated by the test case.
4. Can be configured to search anywhere in string or from beginning only
5. Can search a multi column combobox.



@Gasman FAYT V14

This is an example of the entire code to turn any combo into an FAYT.

Public faytProducts As New FindAsYouTypeCombo

Private Sub Form_Load()
  faytProducts.InitalizeFilterCombo Me.cmbProducts, "ProductName", anywhereinstring, True
End Sub

 

[hfsitumo2001]

MajP, the combo boxes in your sample are all unbounds. how can we make it work for the bound combo box. My form is a query consists of many tables, and customer table.

Thank you

 

[arnelgp]

here is a sample.

 

[MajP]

MajP, the combo boxes in your sample are all unbounds. how can we make it work for the bound combo box. My form is a query consists of many tables, and customer table

Is that a trick question? Use a bound control?

 

[hfsitumo2001]

Is that a trick question? Use a bound control?

No MajP, coz I tried also from other sources but it did not work, so I thought it is because of a bound control. I will try your latest sample.

 

[MajP]

Can you post your db or part of it?

 

[hfsitumo2001]

here is a sample.

Hi Arnel, what should I adjust on my form vba to the module you gave me

 

[arnelgp]

see the sample, i added code to the Change Event of the combobox (see the code).

 

[MajP]

FYI. If you plan to use an FAYT in a continuous form then you will have to use a trick so that when you filter you do not blank out the other instances of a combobox. This is not unique to a FAYT, but is how Access works whenever you filter a combobox in a continuous form.

 

[hfsitumo2001]

see the sample, i added code to the Change Event of the combobox (see the code).

Thank you Arnel, I will try it

 

[MajP]

Here is how you do an FAYT (or any cascading combo) in a continuous form. The trick is a textbox over top of the combox. See porduct and supplier.

 

[arnelgp]

it is the basic.
If you need a "more refined" way, use a Class.
copy the Class and Module to your db.
Check the Load event of the Form on the correct syntax of the class.

 

[hfsitumo2001]

Arnel, but it does not matter if my rowsource of my combobox is:
SELECT DISTINCT Customers.CustomerID, Customers.Customer FROM Customers ORDER BY Customers.Customer;

There is the wordk "DISTINCT", Actually this has worked for find as you type, but only the starting of the string, we want at any part of the string.

 

[hfsitumo2001]

Here is how you do an FAYT (or any cascading combo) in a continuous form. The trick is a textbox over top of the combox. See porduct and supplier.

Thank you MajP, I will study it