Trying to avoid repeats customer names

[Wind54Surfer]

I am trying to avoid entering names more than once, I know they will probably
still happen but right now is happening a lot.

The table is called "CustNew" and the fields that I think will help me
accomplish this are:
CustomerName
PostalCode
TimeStamp2 (shows date new record entered)

The form where the customers are entered is called "NewCustomer" and has 2
fields:
"CustomerName2" and "PostalCode"

Until now I have been using the following to warn of an existing name
(obviously not enough to stop duplicates)
------------------------------------------------------------------------------
Private Sub NewCustomer2_AfterUpdate()

'Dim all required variables:
On Error GoTo Err_Label
Dim strSql As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strQuote As String
strQuote = Chr$(34) ' initialize strQuote to the double quotation
'mark character
Set db = CurrentDb()
' Create a sql select statement that looks for another records
' where CustomerName AND PostalCode match what you have just typed in

If Me.NewRecord Then ' only check if we are adding records
' Construct the SELECT statement
strSql = "SELECT CustomerName" _
& " FROM CustNew " _
& "WHERE CustNew.CustomerName = " _
& strQuote & Me.NewCustomer2 & strQuote
--------------------------------------------------------------------------------
'This following line doesn't apply here
'& " AND PostalCode = " & strQuote & Me.PostalCode & strQuote & ";
--------------------------------------------------------------------------------
' Open a recordset based on the SELECT statement
Set rst = db.OpenRecordset(strSql, dbOpenDynaset)
' If this recordset has records in it then we found duplicates....
If rst.RecordCount > 0 Then
' we have duplicates
If MsgBox("If this customer is a REPEAT click CANCEL, If NEW
customer click OK" & vbCrLf & vbCrLf & "NOTE: even if the customer has moved
is still considered a REPEAT" & vbCrLf & " (include a note with contract to
make sure the Address is updated)", vbQuestion + vbOKCancel, "
Is this a REPEAT or a NEW customer?") = vbCancel Then

Me.Undo
DoCmd.Close
DoCmd.OpenForm "Choose Customer"
End If
End If
Else

'we do not have duplicates

End If
Exit_Label:
On Error Resume Next
rst.Close
db.Close
Set rst = Nothing
Set db = Nothing
Exit Sub
Err_Label:
MsgBox Err.des
Resume Exit_Label
End Sub
-----------------------------------------------------------------------
I want instead do it after entering "PostalCode" and comparing with
"TimeStamp2" to minimize the problem.

I tried several different approaches but my limited code and query
knowledge get on the way.

I will appreciate any help.

Thanks in advance,
Emilio
If is not clear please let me know.

 

[Aaron G]

Wind54Surfer,

Do you have any fields that are more unique that those you posted?

The best would be a social security number, but I find a home phone works
great as well. Birthdays are a great way to filter out a father & son who
have the same name and live in the same house.

If you don't collect that detailed information, do you collect spousal
names? You could first filter by last name, then first name, then spousal
names. John Smith and his son John Smith Jr may live in the same ZIP code,
but it's less likely they would have married a woman with the same name.

HTH

Aaron G
Philadelphia, PA

 

[Wind54Surfer]

Thanks Aaron, the phone number was the key.

Thanks again,
Emilio

 

[John Vinson]

Until now I have been using the following to warn of an existing name
(obviously not enough to stop duplicates)

Well... you don't want to totally prevent duplicates. Names are not
unique; I know of three men named Fred Brown, and for years two of
them (father and son) lived in the same house, not just the same
postal code!

I'd use the Form's BeforeUpdate event, which can be cancelled, rather
than any AfterUpdate event.

John W. Vinson[MVP]