Eliminating Duplication Human Error

[JusticePC]

Hello,

What I am trying to do is to write code for my form such that if a person enters a name in the "Company" field, the database will automatically check all the records to see if that name (or something similar but not identical) exists. i.e. I put in Barnes & Noble, and the database finds Barnes & Noble, Inc. and therefore alerts me. I'm not sure how to do this. Any help would be appreciated.

 

[jatfill]

Hi,
I had something more advanced than this before (I can't seem to find it right now), but essentially you can evaluate the string in your text box, check & see how many values exist in your company with the same string & act from there:

Private Sub txtCompany_AfterUpdate()

Dim stCount As Integer

    'Wildcards can be added to allow for spelling & spacing correction; you can remove them if you only want an exact match
    stCount = DCount("[comapanyID]", "tblCompany", "[companyName] = '" & Me.txtCompany & "'")
        

If stCount > 0 Then
    
    'Add whatever code you need here; msgbox just demonstrates the function
    MsgBox "There are already " & stCount & " records with the company name you entered.", vbOKOnly, "Duplicates"
End If
End Sub

 

[JusticePC]

I will try this and see if it works... Thank you very much for your prompt reply.

 

[Pat Hartman]

Look up soundex using the forum's search engine. There is a reference to where you can obtain the VB code to implement soundex.

 

[JusticePC]

Hello...

I tried the code and I can't seem to get it to work quite properly. Can you give me a breakdown of what the variables in sCount and dCount are? Thank you!

As Always,
Justice

 

[JusticePC]

Hello,

Can anyone help with the above problem?

Justice

 

[chrismcbride]

DCount is a Domain Aggregate function that returns a long integer based on the parameters you pass to it.

stCount = DCount("[comapanyID]", "tblCompany", "[companyName] = '" & Me.txtCompany & "'")

This basically says count the number of CompanyID's from the Table called tblCompany WHERE the company name is the same as that held in my form control called txtCompany and place the returned number in my local variable stCount.
HTH
Chris