How can I make it easy to prevent Duplicate Contacts from being entered in a table

[JJordan]

I am creating a donor database that has a main "Contacts" table which contains all the basic contact information (First Name, Last Name, Company Name, Address, City, State, Zip Phone) with an Autonumber ContactID as the primary key field. The related table is Donations where we track all donations by any contact. A contact has to exist in the contact table to enter a donation in the donation table.

I have a form for entry of New Contacts, but a person can be enered twice if, for some reason I cannot understand, the data entry person is not familiar with all 2700 people already on the list. Is ther a way to make sure that a contact is really NEW and not a duplicate either through the Form or the Table. A true duplicate would have the same name AND address (we have people with the same name and different addresses)

Is there some way to cause a Before Updaate Procedure that compares FirstName, LastName and Address to the Contacts Table to see if one record contains all three and then use a message warning if they do to make sure this is nto a dupolicate entry ? (an If Then statement of some kind) ?

 

[Shep]

I see this problem constantly. Clients with which we have been doing business for many years are repeatedly duplicated by people who appear otherwise completely normal. Amazingly. my firm seems quite happy to hire them. The owner of the company is the worst offender. Go figure.

You can prevent this by becoming the owner of the company and, when duplication occurs, give that employee a written warning and a well-deserved day off without pay. When it happens again, fire the employee very publicly.

You can minimize this in any of dozens of ways. No, there is no way to make sure that a contact is really new because, even if you went as far as to make FirstName, LastName and Address members of a multiple primary key or a similar drastic step, someone will come along and spell one of those fields differently. And ruin your scheme. And prove once again that jobs at McDonald's exist for a good reason.

A listbox positioned strategically near the FirstName, LastName fields which has a simple query behind it and which annoys the user with a list of clients similar to the one they are entering can help a bit. Just let them key in the letter 'a'...hah.

How Neanderthal the user(s) is/are determines just how annoyingly restrictive the query becomes. I give the worst users at my office an extra character's leeway before activating the listbox, in case their fingers are just too thick to work the modern human type keyboards.

Movement helps, too. For example, in one of my forms the listbox is not visible. When the user keys in something dangerous; something which gets too close to the spelling of an extant client (like the letter 'a'), the listbox becomes visible and the form resizes, pushing the remaining fields down the form to accomodate the listbox. A label becomes visible as well, begging the user not to duplicate customers. You might even use the Timer to flash something at the user.

But, truly, all you can really do is beg. Constantly.

Shep

 

[bradcccs]

I feel the tension in this topic!

I once read, and was impressed by a statement of "Fornation's"

build a better system and they'll build a better idiot.

I try to avoid this problem by first requiring the user to enter the surname (etc) in to a list box which will search for existing entries. I then hope that the user will be lazy and select the obvious (and hopefully appropriate) match.

If the match (of surname, firstname etc) does not exist, then I use the "NotInList" event to display the relevant fields to allow for an addition of a new contact etc.

Not fool proof by any means, but you have to think slower to match some users

Brad.

 

[Shep]

Hear, hear.

Here's an example of how I populate a listbox. In this case, it's on the After Update event of a textbox for adding Commercial (as opposed to Individual) customers.

strCNameSel is a global variable.

As you can see, I take a really small chunk of what the user entered and use it as a filter to query the Customer table. The level of annoyance here is aimed specifically at the owner of the company, which matches his level of literacy. You might find that you are able to adjust the Mid statement to allow for more leeway. I am, unfortunately, not able to justify loosening the numbers.

Private Sub txtCompanyName_AfterUpdate()

DoCmd.Hourglass True
Dim varItem As Variant

If Not IsNull(Me.txtCompanyName) Then
        If Len(Me.txtCompanyName) > 2 Then
            strCNameSel = Mid(Me.txtCompanyName, 2, Len(Me.txtCompanyName) - 2)
        Else
            strCNameSel = Me.txtCompanyName
        End If
    Me.lstCustomer.RowSource = "SELECT qryCNameMain.CustomerID, qryCNameMain.CName FROM qrytblCustomer LEFT JOIN qryCNameMain ON qrytblCustomer.CustomerID = qryCNameMain.CustomerID WHERE (((qryCNameMain.CName) Like """ & "*" & """ & CNameSel() & """ & "*" & """) AND ((qrytblCustomer.Type)='Commercial')) ORDER BY qryCNameMain.CName WITH OWNERACCESS OPTION;"
Else
    GoTo Exit_txtCompanyName_AfterUpdate
End If

Me.txtCompanyName = StrConv(Me.txtCompanyName, vbProperCase)
strCompanyName = Me.txtCompanyName

If Not IsNull(Me.lstCustomer.ItemData(varItem)) Then
    DoCmd.MoveSize , , , 5040
    With Me
        .lstCustomer.Visible = True
        .lblCheck.Visible = True
        .lblAddress.Top = 2639.952
        .txtAddress1.Top = 2639.952
        .txtAddress2.Top = 2940.048
        .lblCity.Top = 3240.144
        .txtCity.Top = 3240.144
        .cboState.Top = 3240.144
        .lblZipCode.Top = 3240.144
        .txtZip.Top = 3240.144
        .lblPhone.Top = 3540.24
        .txtPhone.Top = 3540.24
        .lblExtDesc.Top = 3540.24
        .txtExtDesc.Top = 3540.24
        .lblUsesPOs.Top = 3840.336
        .chkUsesPOs.Top = 3870.288
        .cmdContinue.Top = 3375.072
        .cmdCancel.Top = 3735.072
        .lblNote.Top = 4140
    End With
Else
    DoCmd.MoveSize , , , 2819.952
    With Me
        .lstCustomer.Visible = False
        .lblCheck.Visible = False
        .lblAddress.Top = 420.048
        .txtAddress1.Top = 420.048
        .txtAddress2.Top = 720.144
        .lblCity.Top = 1020.24
        .txtCity.Top = 1020.24
        .cboState.Top = 1020.24
        .lblZipCode.Top = 1020.24
        .txtZip.Top = 1020.24
        .lblPhone.Top = 1320.336
        .txtPhone.Top = 1320.336
        .lblExtDesc.Top = 1320.336
        .txtExtDesc.Top = 1320.336
        .lblUsesPOs.Top = 1620
        .chkUsesPOs.Top = 1649.952
        .cmdContinue.Top = 1155.024
        .cmdCancel.Top = 1515.024
        .lblNote.Top = 1919.952
    End With
End If

Exit_txtCompanyName_AfterUpdate:
DoCmd.Hourglass False
Exit Sub

End Sub

My apologies for the really wide post. I rarely use line continuation.

Shep

 

[JJordan]

How about some help with this

EDIT: I was trying to figure out something and posted this while you guys were posting above -- I am not smart enough (or experienced enough) to write the code to do what you suggest. Below is what I was trying to do.

I have a query that starts with the Contacts table.

I add ContactID, firstname, lastname and address to the query and for the criteria on each, I put

ALike([forms]![New Contacts Form]![Last Name]) in the criteria for LastName

First Name for FirstName criteria etc.

Now when I run the Query, If first last and address match, it pulls up that person's contact ID

Cool, now what can I do with it. I would like to run the query on the before update (not visible) and if it returns any information, have a mesage pop us saying "This may be a duplicate, Update Anyway ?" yes/no -- on Yes, it proceeds, on No it kicks back and opens the query visible to check and see if it really is a duplicate.

Is there code that will check to see if the query returns any values or information and then cause that to run a message ?

Alternatively, I can write an Iif in the query for example that compares all three values (or just first and last name) like this

expr1=iif(lastname=[forms]![New Contacts Form]![Last Name] AND firstname=[forms]![New Contacts Form]![First Name], "Warning", "OK")

Now if the names match, expression1 is Warning, otherwise it is OK. IS there a way to use "warning" output vs. Ok (or make it Null) in Virtual Basic to run the Query and have something happen only if Expr1="warning" (I could not put "warning" as a criteria for Expression1 so it only pulls up the warnings -- when I did that it asked me to enter Warning parameter.

 

[Shep]

What you're trying to accomplish is fine but consider this - if your query finds a match, it IS a duplicate, and it is not logical to 'update anyway' in this case. This gives the knuckledraggers license to roam freely about the planet, which is a bad thing.

Also, you don't need to use the Like operator in the query, unless you are going to use wildcards with your form's criteria.

Example:
Like [Forms]![New Contacts Form]![Last Name] & "*"

If you wish the query to match what is in the form's textbox, just use the form field as the criteria:
[Forms]![New Contacts Form]![Last Name]

However, imparting to the user that they might be dangerously close to removing any doubts about their competency is difficult with this rather limited way of collecting the information needed to do so.

Hmm...what to do...

A simpler suggestion - instead of initially presenting your users with a form to enter new clients, why not present them with a logically sorted listbox or datasheet subform of existing donors which they may select from? Then, if the donor is not listed, present the user with a separate entry form.

In this way, at least you made the effort to show them the list of existing donors before they duplicate them (which they will do, regardless).

Oh, a further suggestion for you. Get rid of spaces in your form names and in your field names. It will make your job much more pleasant and will avoid pitfalls which you are certain to encounter if you persist.

There are probably simpler suggestions but I cannot think of anything elegantly simple at the moment which will have any greater effect than if the person entering the data simply used common sense.

Shep

 

[JJordan]

First, the Contacts table includes not just donors. It includes families we serve and people who get newsletters and professionals who refer families to us. I have a donation form that only lets the user select from the existing contacts and if not on the list, there is a button to pull up a form to enter new contacts.

What I am looking at is when we just want to enter new contacts unrealted to a donation. Just a new family or a new person that wants a newsletter or just a new prospective donor who has not donated yet. People can have the same first/last names and not be duplicates. I was suggesting to key off first and last name which MAY NOT be a duplicate entry even if they match, because adding the Address field to the issue causes problems because I think there is a lot more variation on how an address might get entered (you may type 26th St insead of 26th Street or STE instead of Suite and they could still be duplicates). the Like is so that if I enter Jeff Jordan instead of Jeffery Jordan, it still warns me. Admittedly it won't catch Bill instead of William, but what do yo do.

So the Query matches first to last name and if they are the same, expr3 says Warning. Otherwise Expr3 is Null.

Can you cause the Query to run before update and pull out the "warning" field (i.e. if that field is not null, then it is Warning) and cause that to trigger a message box in an Iff then statement. OR Is it possible to cause the If then to check a field in the query and see if it is Null ? Now I am just curious I guess becuase it sounds like what I am doing is all wrong any way.

 

[pcs]

you indicate that you have a phone number field in your contacts table.

remember a full phone number (555) 123-4567 is, in fact unique.

if you require the user to enter a full phone # for a new record, you can then inspect the contacts table for dupes and warn the user of the possibility that they are entering duplicate info. of course, it is possible that more than one contact could point to the same number. but, i think this technique would serve to decrease the number of dupes in your database.

hth,
al

 

[JJordan]

That is a good point except many companies have multiple contacts with the same phone and we do not have phone numbers for all our donor (I know we should) - but no one kept track until now -- that is why I am trying to do a database for them.

Thanks to all for your help (and the interesting commentaties) I give up. I tried to dream up code to cause the result I want, but it simply does not work.

Dim strWarning As String

strWarning = "(If(forms![enter new contacts]![Last name]=[contacts].[lastname] And forms![enter new contacts]![first name]=[contacts].[firstname]) Or ([contacts.][companyname]=forms![enter new contacts]![company name]),1,0))"

If strWarning = "1" Then
MsgBox ("This may be a duplicate")
End If
End

 

[Shep]

No, what you are doing is not wrong at all. It is just one of many ways of accomplishing what you want.

To answer your question though, yes. You can cause the query to pull up anything you like and then to display any fields you like. The most likely way to do this is with a Recordset.

Combining two fields for such a search is pretty easy, I suppose. It would be much more difficult to query separately each of the two fields with the Like operator and then combine that in some way to find a match.

Another thing...what if you have more than one match? The only way to let your user know would be to use a listbox or a rather busy msgbox, whose tiny font might annoy just enough to cause the user to simply ignore it.

Please keep in mind that bound forms are not my specialty. I normally use unbound forms for data entry. With this in mind, I submit this for your consideration:

In the AfterUpdate events of both your FirstName and LastName fields:

Dim dbs As Database
Dim rst As Recordset
Dim strContact As String
If Not IsNull(Me.txtLastName) And Not IsNull(Me.txtFirstName) Then
strContact = Me.txtLastName & Me.txtFirstName
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Contact")
rst.FindFirst "[LastName] & [FirstName] Like '*" & strContact & "*'"
With rst
If rst.NoMatch Then
.Close
GoTo Exit_txtFirstName_BeforeUpdate
Else
If MsgBox("A " & !FirstName & " " & !LastName & " already exists. Continue update?", 260) = vbYes Then
.Close
GoTo Exit_txtFirstName_BeforeUpdate
Else
Cancel = True
Me.Undo
.Close
End If
End If
End With
End If

Exit_txtFirstName_BeforeUpdate:
Set dbs = Nothing

You'll need to add spaces to the field names to try it out.

Shep

 

[RV]

Yeah, this is one of those issues which can't be anticipated.
It's got to do with a subject called business process control.
In practice, all you can do (if the organization itself is mature, efficient, effective like for instance WorldCom ) is to react.
React meaning checking afterwards for possible unwanted duplicates.

However, here's some code I use to prevent possible duplicate relations:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If NewRecord = True Then
If DCount("*", "QryRelation", "FamilyName = '" & Forms!Relation!FamilyName & "' AND Address = '" _
& Forms!Relation!Address & "' AND Zipcode = '" _
& Forms!Relation!Zipcode & "' AND City
= '" _& Forms!Relation!Place & "'") <> 0 Then
MsgBox "Relation already exists"
Me.Undo
End If
End If
End Sub

QryRelation = SELECT * From Relations

HTH,

RV

 

[JJordan]

When I try to run it I get an error "Compile Error - User Defined Type not defined and the DeBugger takes me to the Fisrt Line
Dim dbs as DataBase with the dbs as Database highlighted.
Ye Ha !

Also, on the spaces, the Contacts Table field for LastName and FirstName had no spaces. The spaces were for some reason in the FORM box names for the First name and Last name textboxes. I changed those to FirstName and LastName with no spaces too.

 

[Shep]

Ok, easily corrected.

Open any Form in Design view. Hit the code button, or go to the code behind any control on your form. The menu at the top of the screen changes. You will now see an option called Debug. This lets you know you're where you need to be.

Hit Tools, References.
Find Microsoft DAO 3.51 Object Library, select it and hit Ok.

If you find you don't have 3.51, just choose the latest version you see there.

Close all that and try the form again.

Shep

 

[JJordan]

I appreciate your help -- had the 3.6 so I added it and now I get past line one but get the same error message highlighting the txt.lastname

 

[Shep]

First check your references again. As a minimum, you should see these three:

Visual Basic For Applications
Microsoft Access 8.0 Object Library
Microsoft DAO 3.51 Object Library

Next, take a closer look at the line that produces the error.

txtLastName is an arbitrary name that I assigned my TextBox when creating the code. Either make sure your TextBox is named the same, or change the code to fit the name of your TextBox.

I suggest changing your TextBox name to fit the code, as there are several changes which would need to be made in the code to accomodate your current TextBox name.

I drive an hour and a half to get home, so I won't see this thread again for a couple hours. But I will take a look later tonight.

Good luck.

Shep

 

[JJordan]

I drive less but am leaving too. I changed the form textbox names and Now it gets down to
rst.FindFirst and gives the same message.

I cannot beleive you are working so hard to help me and I appreciate it.

 

[Shep]

I find it odd that you are seeing the very same message. If in fact you are, this again indicates a missing reference.

Recheck your references, first.

Also, look closely at the line containing rst.FindFirst:

rst.FindFirst "[LastName] & [FirstName] Like '*" & strContact & "*'"

The names in red are field names in your Contacts table. Make sure these names match your field names.

Also, my code opens a Recordset based on a "Contact" table. Add an 's' to the end of that table name in the code, if you haven't already. This is found one line above the line with rst.FindFirst in it.

You're quite welcome for the help.

RV, I like your technique.

 

[JJordan]

I have added the References. When I try to run it now, I get an error pop up box that says

"RunTime Error '3251': Operation is not supported for this type of object."

DeBug takes me to the line
rst.FindFirst "[LastName] & [FirstName] Like '*" & strContact & "*'"

I think there is something wrong with the openrecordset line or the one right after When I pass the Cursor over the fields in the DeBugger, the txtfirstname and txtlastname and the strContact contain the proper information from the form.

I also tried using () arounfd the "[lastname] ..... " on the rst.FindFirst with and without quotes and no go -- same message.

Here is the entire Code

Private Sub txtLastName_AfterUpdate()
Dim dbs As Database
Dim rst As Recordset
Dim strContact As String
If Not IsNull(Me.txtLastName) And Not IsNull(Me.txtFirstName) Then
strContact = Me.txtLastName & Me.txtFirstName
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Contacts")
rst.FindFirst "[LastName] & [FirstName] Like '*" & strContact & "*'"
With rst
If rst.NoMatch Then
.Close
GoTo Exit_txtFirstName_BeforeUpdate
Else
If MsgBox("A " & !FirstName & " " & !LastName & " already exists. Continue update?", 260) = vbYes Then
.Close
GoTo Exit_txtFirstName_BeforeUpdate
Else
Cancel = True
Me.Undo
.Close
End If
End If
End With
End If

 

[Shep]

Ok, you're almost there.

Change the OpenRecordset line to read like this:

Set rst = dbs.OpenRecordset("Contacts", 2)

For your study, here is the option '2' in it's full form:

Set rst = dbs.OpenRecordset("Contacts", dbOpenDynaset)

You can use either of the above, as they translate to the same thing.

To be quite honest, I am unsure why you would receive the message, as we are just filtering a Table type Recordset, but opening the Recordset as a dynaset-type should do the trick.
Maybe someone else can explain exactly why. I usually only run into this error when opening a Recordset based on a query, linked table, ODBC object or when I use the .AddNew, .Edit or .Update methods on a table-type Recordset.

Shep

 

[Rich]

I'm not sure but I thought you could only search on a single field not a combination of two