How to find a record by typing the first letters

Topic

New Reply

How to find a record by typing
the first letters

I have a table called Customers and a form called frmCustomers and i find
a customer on the form through its ID number for examplle
Dim strCustomerID As String
Dim strBookmark As String
strBookmark = Me.Bookmark
strCustomerID = InputBox(“Please enter Customer number “)
Me.RecordsetClone.FindFirst “CustomerID = ” & strCustomerID
If Me.RecordsetClone.NoMatch Then
MsgBox “CustomerID ” & strCustomerID & ” Not Found!!”
Me.Bookmark = strBookmark
Else
Me.Bookmark = Me.RecordsetClone.Bookmark
End If
However i want to find the customer through writing the first letters of its CompanyName and choose the right customer from a dropped list of the CompanyNames with the closest letters.Is it posible?

Reply | Quote

Replies

You can’t do it with an inputbox, but the matching feature is built into Access comboboxes. Put an unbound combobox on your form. Set its limit to list and autoexpand properties to true. Make its rowsource a saved query that will return a list of CustomerIds. In the AfterUpdate event of the combobox, put your code to find the matching record, except use something like this:

Me.RecordsetClone.FindFirst "CustomerID = " & cboCustomerID

so that you’re telling it to find the first that matches the value in the combobox.

Reply | Quote

You may already have everything working, but check out this post for another great solution.

Reply | Quote

You can also do what Charlotte recommends, but in Row Source, put this SQL:

SELECT DISTINCTROW tblCustomer.strCustomerID, tblCustomer.strCompanyName FROM tblYourTable ORDER BY tblCustomer.strCompanyName;

The dropdown list will let you type in the first few letters and will go to the first Company Name that matches the letters in the list. Then just click on the Customer Name you need and it will open that record.

Reply | Quote

Using a combo box with the limittolist set true could be a way to go. However if you have a large number of entries then be prepared for performance problems.
Could the company name be duplicated? (Company XYZ in town A and town .
Would you want to provide the search based on either ID or Company name? If so you could use 2 combo boxs to help the user select the customer by responding to the box the user performs a selection in.

Reply | Quote