Avoid Duplicate Entries (1 Viewer)

[access7]

Good afternoon

I am hoping someone may be able to give me some advice on the following...

I would like to put something in place that will warn a user if a Company Name already exists in the database (to avoid duplicate records which would cause no end of problems).

I have put the following code in place and it is working however I would like to expand upon it... at present the procedure will only pick up duplicates if the user has entered the EXACT same information - Ideally I need to be able to search for similar names to ensure they are not the same company... i.e. perhaps the user used different capitalisation / spaces / spelling etc the first time they created the Company's record. I need to ensure that they can never recreate the same company.

Private Sub CheckDupName()
Dim strDupName As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset
Set rsc = Me.RecordsetClone
strDupName = Me.txtCompany.Value
stLinkCriteria = "[Company]=" & "'" & strDupName & "'"
'Check Company table for duplicate names
If DCount("Company", "tbl_Company", _
stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning - A Company with the name '" & strDupName & "' has already been created. Please check before you continue.", vbExclamation, "Duplicate Information?"
End If

Set rsc = Nothing

End Sub

Many Thanks in advance for any help on this... in the meantime I will keep on looking and researching for any advice

 

[spikepl]

http://www.access-programmers.co.uk/forums/showthread.php?p=1207172#post1207172

 

[jdraw]

Never? Are you planning on human intervention?
I don't think there is an algorithm to ensure never.
I have worked on Company systems, Names/Addresses etc. and I would be real cautious of never. I'm probably reading more into that than was intended.

If you have an authoritative table of Company Details and a Unique identifier, and you use the identifier for all other linkages(Contacts, Addresses, Products, Services... ) to the Company, you have a good start.

You can have routines that take all components of Company Names, remove the Noise (the, and, from, etc whatever might be in your list) words; you can add misspellings and link with the identifier. You can search for Zip/Postal + Phone( + City + street+++), you can assign some weights and only suggest a match if the result has weight >x. (Much like the SIMIL number mentioned in spikepl's post).
You can include other routines Soundex, Levenstein, Fuzzy Matching..... but these all require code and processing time.

You can get close, but never ????