Prevent duplicate records to be entered

[My-Yen]

Hello,

Any suggestions a way (or function) that I can use to
prevent entering of duplicate records.

I have a contact database with the typical fields (e.g.
first name, last name, address, organization etc.). The
only unique field I have is 'autonumber'. How can I
prevent someone from entering the same record. Any
examples I can use?

Thanks in advance!
MyQ

 

[Andrew Smith]

Add an index on the field(s) you want to keep unique, and set "unique" to
yes.

 

[My-Yen]

Hello,

But I have no unique fields, the only unique field is
the 'contact_id' which has no meaning, plus it's datatype
is "autonumber". :-(

Some of the contacts in the database has the same last
name, many work at the same organization and have the same
telephone number. Therefore, lastname, organization or
tel # can not be unique.

Any other suggestions?
MyQ

 

[Andrew Smith]

Yes, I understand what you want to do. You need to create an index that
involves more than one field - ie all the fields that taken together must be
unique.

 

[My-Yen]

So after indexing some of the fields (ie. first_name,
Last_name and organization). What shall I do?

thanks,
MyQ

 

[Andrew Smith]

Make ONE index that includes SEVERAL fields. Set the unique property of this
index to "yes", and the job's done.

The only other thing you might want to do is to intercept the standard error
message that happens when someone creates a duplicate entry, and replace it
with something more user friendly. You will need to use VBA code in the data
entry form for this, but you might be happy with the standard error message.

To set up the index, open the table in design view. Click on the indexes
icon on the tool bar. Type in the name of the index in an empty Index Name
field, and change Unique to "Yes" in index properties. Then select several
Field Names in the next column - one under the other, leaving the Index Name
field empty for the second and subsequent fields. You can add up to 10
fields in this way.

 

[Guest]

Great! It works...thanks!
But is there a way of viewing what the duplicate record
is? A button that someone could click on to go to that
previously entered record.

Or, how can I set up the lastname and organization fields
to have a pull down list automatically generated when
someone is typing in the data. For instance, if someone
types in "Sm" it would go to the Smith or the closest
lastname on the alphabetically list. And when they do
chose the lastname on the list, it would go straight to
the record. Does that make sense?

thanks!
MyQ

 

[Andrew Smith]

To answer your second question - combo boxes work just like that. So rather
than a text box for the name, use a combo box and have its row source set up
to list all the names that are already in your database.

On the duplicate record issue - rather than have a button, use the Error
event of the form that you are using to intercept the error about duplicate
records, and then display the "duplicate" record when this happens (it won't
actually be a duplicate as you can no longer get duplicates!) There are
probably several ways to do this, but this one will work provided the
recordset of the form you are using includes the duplicate record. If you
have opened the form at a single record then you will need to change the
code a bit.

The sort of code you need is as shown below. This code assumes that you have
two fields (FirstName and LastName) that form the unique index, and that the
form has controls called txtFirstName and txtLastName bound to these fields.
You will need to change this to match your own field and control names, and
will probably need more fields than just thest two.

Private Sub Form_Error(DataErr As Integer, Response As Integer)

Dim strWhere As String
Dim rst As DAO.Recordset

On Error GoTo HandleErrors

Select Case DataErr
Case 3022 'Duplicate record
If MsgBox("This record already exists. Click OK to go to the
existing record, or Cancel to change your entry", vbOKCancel, "Duplicate
record") = vbOK Then
strWhere = "FirstName ='" & Me.txtFirstName & "' AND LastName
='" & Me.LastName & "'"
Me.Undo
Set rst = Me.RecordsetClone
With rst
.FindFirst strWhere
Me.Bookmark = rst.Bookmark
.Close
End With
Response = acDataErrContinue
Else
Response = acDataErrContinue
End If
Case Else
End Select

ExitHere:
Exit Sub

HandleErrors:
Select Case Err.Number
Case Else
MsgBox Err.Description & " (" & Err.Number & ")"
End Select
Resume ExitHere