Wildcard Duplicate Record Warning

[Tezcatlipoca]

I want to impliment some kind of facility into my database that will flash up a warning if a user enters data into a certain field that is equal to or like data that exists in the same field in other records.

A search of these forums gives me a few hints about duplicate warning record messages where the duplicate data is exactly the same as previously entered data, but I want it to pull up similar records, thus taking into account spelling errors.

For example: Let's assume that the NAME field in my records already includes the name SMITH, on record 53. The user now adds a new record, and, in filling out the NAME field enters the name SMYTHE. I want a box to popup (either by code or macro) to inform the user that a record with a similar name exists, gives the potential duplicate record number (in this case 53), and prompts the user whether they are sure they want to create the new record. Even better, would be for a subform to open containing the duplicate record, so the user has a visual reference before deciding whether or not to proceed.

 

[The_Doc_Man]

Finding a conflicting SMITHE when the name is SMITH will be easy. That is done with a LIKE operator and wildcards. The match criterion would be

LIKE "*smith*"

The above criterion would twitch on "SMITHE", "SMITHSON", and "NAISMITH" as possible conflicts.

Finding a conflicting SMYTHE when the name is SMITH will be harder because you need some algorithm that will convert a name to its pronunciation. Such things exist but they ain't cheap and ain't easy to integrate into Access.

 

[sparklegrrl]

This is EXACTLY what I want to do inmy database but hav no idea how to make it check for it or make the form pop-up?

When you enter an item number I'd like it to check for duplicates then if it finds one pop up an screen that gives some details of the similar record and then asks the user if they want to continue.