Find as we typed (1 Viewer)

hfsitumo2001

Member
Local time
Today, 16:45
Joined
Jan 17, 2021
Messages
365
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

I’m here to help
Staff member
Local time
Today, 16:45
Joined
Oct 29, 2018
Messages
21,358
Check out this thread/post.

 

GinaWhipp

AWF VIP
Local time
Today, 19:45
Joined
Jun 21, 2011
Messages
5,901
You will need to adjust to fit your Control names and Row Source data but this should do the trick...
Code:
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...
Code:
    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...
Code:
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

Member
Local time
Today, 16:45
Joined
Jan 17, 2021
Messages
365
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

Member
Local time
Today, 16:45
Joined
Jan 17, 2021
Messages
365
You will need to adjust to fit your Control names and Row Source data but this should do the trick...
Code:
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...
Code:
    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...
Code:
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
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 19:45
Joined
May 21, 2018
Messages
8,463
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.


Code:
Public faytProducts As New FindAsYouTypeCombo

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

Attachments

  • MajP FAYT V19.accdb
    1.3 MB · Views: 11

hfsitumo2001

Member
Local time
Today, 16:45
Joined
Jan 17, 2021
Messages
365
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:45
Joined
May 7, 2009
Messages
19,169
here is a sample.
 

Attachments

  • sample combo filter as you type.accdb
    640 KB · Views: 312

MajP

You've got your good things, and you've got mine.
Local time
Today, 19:45
Joined
May 21, 2018
Messages
8,463
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

Member
Local time
Today, 16:45
Joined
Jan 17, 2021
Messages
365
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

You've got your good things, and you've got mine.
Local time
Today, 19:45
Joined
May 21, 2018
Messages
8,463
Can you post your db or part of it?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:45
Joined
May 7, 2009
Messages
19,169
see the sample, i added code to the Change Event of the combobox (see the code).
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 19:45
Joined
May 21, 2018
Messages
8,463
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.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 19:45
Joined
May 21, 2018
Messages
8,463
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.
 

Attachments

  • FAYT Bound.accdb
    1.2 MB · Views: 285

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:45
Joined
May 7, 2009
Messages
19,169
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.
 

Attachments

  • sample combo filter as you type.accdb
    1.1 MB · Views: 311

hfsitumo2001

Member
Local time
Today, 16:45
Joined
Jan 17, 2021
Messages
365
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.
 

Users who are viewing this thread

Top Bottom