Validate data by returning similar values

[NRK]

I have a form that contains a combobox (holding customer names/ IDs). If the user enters a customer that does not exist or if they want to add a customer, I created a pop-up form that allows them to do this.

The form does validate the user's entry but only if it exactly matches a record from my Customer table. But, I found occasions where the user does not use the reference form (lists all existing records in Customer table). So, a user could create multiple similar entries for the same customer (i.e. Joe's Flowers, Joe's, Joe's Flower Shop).

So, I am trying to figure out if there is a way to run code that searches for similar values. I believe 'LIKE' is something that could be applicable but I can't remember how to use it. Ideally, I would have code that runs on BeforeUpdate checking for similar values. If similar values are found, then a warning message would indicate the similar values and prompt the user to make sure that their Customer creation is correct.

Is this possible? If not, is there some better solution that I am overlooking?

 

[Scoty]

NRK,
Are the ID's created Dynamically or are they entered by your user. And if they are, are they entered in a separate field or is it incorporated in the custer name? Plus if you could post your code I would be willing to take a quick look at it. Don't forget to post the event that triggers the code.

Scoty : "Learn from others' mistakes. You could not live long enough to make them all yourself."
-- Hyman George Rickover (1900-86),

 

[NRK]

Scoty,
The IDs are entered by the user. As they are saved as a table, I append "cust_" to every entry to distinguish these values from other tables. The ID value is entered in a combo box - when the user clicks 'Add', I change LimitToList from True to False.

Here is the code (I will post comments in green):
Private Sub cmdAdd_Click()
MsgBox "This will add a temporary table for the purposes of saving a filter that you have created.", vbInformation
cboCustomer.LimitToList = False
cboCustomer.SetFocus
‘pretty simple – just allows user to add value to combo box
End Sub


Private Sub cmdSelect_Click()
On Error GoTo Err_cmdSelect_Click
Dim strCust As String
Dim strForm As String

If IsNull(cboCustomer.Value) Then
MsgBox "You cannot save a filter if you have not specified a Customer file.", vbInformation
Exit Sub
Else
strCust = "cust_" & cboCustomer.Value
[Forms]![Netrak_CustFilter].[txtSaveTable] = strCust
‘append “cust_” so that user-created temp tables
‘are distinguishable from primary tables
End If

DoCmd.SetWarnings False
DoCmd.RunSQL ("SELECT tcID, RptID INTO " & Forms!Netrak_CustFilter!txtSaveTable & " FROM 1_tbltcMaster WHERE RptList = True ORDER BY RptID&quot
DoCmd.SetWarnings True

Exit_cmdSelect_Click:
DoCmd.Close acForm, Me.Form.Name
Exit Sub

Err_cmdSelect_Click:
'MsgBox Err.Description
Resume Exit_cmdSelect_Click

End Sub

Hope this gives you a better understanding of my process. Please let me know if you need anything else. Thanks for your help!

 

[NRK]

I figured my issue out...if anyone is interested in my final solution let me know.