Company Name Duplicates

[Evan McCutchen]

Hello,

At my company, our users have trouble with entering information twice -
Companies we do business with, for example. This makes it a nightmare to
relate things such as POs, Invoices, etc, to a company because therer are
duplicate entries in our companies table.

Is there a way that i can code something that would search for companies
with similar names to prevent duplicate entries? I have seen something
before using the BeforeUpdate event on the form, but i dont know how exactly
to go about doing it.

Thanks!
Evan McCutchen
evan AT radiologyonesource DOT com

 

[Cheryl Fischer]

Evan,

The simplest method would be to make one slight change in the design of your
Companies table:

1. Open the table in design view
2. Click on the field containing Company Name
3. In the lower left corner, look for the "Indexed" property and set it to
'Yes, no duplicates'

If your users attempt to enter a name that is already in the Companies
table, they will receive an error message.

To make the process smoother, on forms where entering a Company Name is
required, give your users a combo box based on the names found in the
Companies table. You can set the Limit to List property of the combo box
to 'Yes' to assure that the users will select only from the names in the
list.

In addition, if you do not already have one, you should have a form for
"Maintaining Customer Data". This will provide the means to set up New
Companies.

hth,

 

[Evan McCutchen]

Cheryl, Thanks for your reply!

I actually set the Index property to "Yes - no duplicates" for a little
while and all seemed well until a user tried to insert a different company
with the same name (i.e. - there are two different companies named
"Blueridge Medical"). That's why i was looking for something that would
query out similar records before adding the record to the form and give the
user an option of whether to procede with the record save or not. I've seen
it done in a database before, but i can't find the code for it. Does anyone
else have any reccomendations? Thanks for your help Cheryl!

Evan McCutchen
evan AT radiologyonesource DOT com

 

[Alphonse Giambrone]

A 'soundex' search would help. Search this and other newsgroups for it. That
would also cover the instances where someone mis-spells the name of an
existing company.

 

[PC Datasheet]

Evan,

In the BeforeUpdate event, you need to look for duplicates and if one or more
duplicates are found, you need to pop-up a form listing the duplicates where the
user has enough information on the form to make a decision if the company he is
about to enter is already in the database. The popup form also needs to ask the
user if he wants to enter the company he just typed in into the database.

 

[Evan McCutchen]

How would I go about doing that? I'm sorry, I dont mean to keep asking, but
i dont understand how to have the code compare what's in the text box to the
other records in the table. Could you please give me a sample or a quick
walk through?

Thanks alot!
Evan McCutchen
evan at radiologyonesource dot com

 

[PC Datasheet]

To look for duplicates, you would first create a recordset of your data entry
form using:
Dim Rst As DAO.Recordset
Set Rst = Me.RecordsetClone
Rst.Findfirst "[CompanyName] = '" & Me!NameOfCompanyTextbox & "'"

Then to test if there is a duplicate and open the popup form:
If Rst.NoMatch = False Then
<<Open popup form>>
End If

You now need to look in the Help file for RecordsetClone, FindFirst and NoMatch.

If you want help setting this all up, I can help you on a fee paid basis. My
fees are very reasonable.

Steve
PC Datasheet