When a user wants to start a new order, a form opens that displays our customer table in alphabetical order and the user is prompted to type in any part of the customer’s name. For example, if the customer’s name is “The Smith Company” the user can type “Smith” and the program will find the first occurrence of “Smith”. If the user has located the right customer, he 2-clicks the field to start an order. The problem is that the first occurrence of “Smith” might not be “The Smith Company”. If the user thinks that “The Smith Company” is not in the table, he clicks the “Add New Customer” button and we wind up with duplicate records in the table. Is there a way to filter the form so it only displays the customers with “Smith” in their name? Or is there a better way to search the table? Currently, my code looks like this:
Private Sub Form_Open(Cancel As Integer)
TryAgain:
Dim varCust As String
varCust = ""
varCust = InputBox("Company Name:")
If varCust = "" Then
MsgBox ("You must type something.")
GoTo TryAgain
End If
DoCmd.FindRecord varCust, acAnywhere, , acSearchAll, , acCurrent
End Sub