Improving Double entry prevention

[eseng]

Hi, this is the vba code that I am using to prevent double entry. It actually works well. Unfortunately, there is still one problem, take a look in my example:



Recorded entry: Jhon's Laundry Shop
New entry: Jhon's laundry Shop

In this case, Access accepts the NEW entry if I make changes (uppercase to lowercase) vise versa in the name of the entry. How can we improve the code to prevent this from happening?

Dim NewBusinessName As String
Dim stlinkcriteria As String


NewBusinessName = Me.BusinessName.Value
stlinkcriteria = "[BusinessName] = " & Chr(34) & NewBusinessName & Chr(34)
If Me.BusinessName = DLookup("[BusinessName]", "Sheet1", stlinkcriteria) Then
MsgBox "" & NewBusinessName & " is already in the Database." _
& vbCr & vbCr & "Data Entry Denied!!!", vbInformation, "DUPLICATE ENTRY"
Me.Undo 'undo the process and clear all fields

[moke123]

There are a few different things.
Convert the strings to all upper or lowercase for the comparison. (ie. Ucase() or LCase())
Use strComp() and do a case insensitive comparison.

You'll still have a problem with misspelled words ie. Jhon's and John's for instance.

I usually do several comparisons and then present the user with a list of matches to determine if its a duplicate or not. Usually a good idea to compare more than just the name as there maybe legit dupes.
For instance with people I compare the names and also DOB or address. I also check for minor misspellings with levenschtein distance.

[Micron]

Are you using Option Compare Binary in your modules? It's the only reason I can think of that would allow multiple variations of data just because they're not the same case. Why not just set the table field to not allow dupes? No idea if that would work if you're using Option Compare Binary though.