Duplicate records

  • Thread starter Thread starter JN
  • Start date Start date
J

JN

Hi. Is there a way for Access alerting you by way of pop up or something,
when you are entering data twice, i.e. new customer, to let you know that,
that name has already been entered before just in case you are duplicating a
record? If you have 4 thousand records, it would help the user entering the
data in 'form' to know before hand. So that they can check before
continuing that the customer may have the same name but different address,
in which case they can continue. I've done a query to search for duplicates
and found way too many.
Ta very much.
J
 
Hi JN,

My name is Amy Vargo. Thank you for using the Microsoft Access Newsgroups.

The best way to ensure that you are not entering duplicate data would be to
make the fields that should make the record unique and not allow it to be
duplicated the primary key. A primary key can be composed of one or more
fields and must be unique. For example, if the combination of an ID,
Firstname, and Lastname make a record unique, set those 3 fields as the
primary key.

I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Amy Vargo
Microsoft Access Engineer


This posting is provided 'AS IS' with no warranties, and confers no rights.
You assume all risk for your use. © 2001 Microsoft Corporation. All rights
reserved.
 
Hi JN:

Amy's suggestions are right... key and index fields are really wonderful in
keeping duplicates from being entered. There are other methods that I have
collected over the years, though, that appeared in these discussion groups
which I thought were neat.

1) In method 1 you would need to put into the BeforeInsert event on the text
control. It could use DLookUp to find, say, the entered SSN in a table,
something like

txtSSN_BeforeInsert(Cancel as Integer)
Cancel = FALSE
If Me!txtDupSSNOK Then
Exit Sub ' because you don't care if it's a dup
Else
If IsNull(DLookUp("SSN","YourTableName","SSN='" & Me!txtSSN & "'") Tjem
Exit Sub ' No dup found
End If
End If
Cancel=TRUE ' Cancel the insertion
Msgbox "This SSN already exists, please reenter"
End Sub

2) You can also use the UNDO function to cancel (and reverse) an event if
you put your code into the AfterInsert event.

3) Another rather sophisticated way is to capture the actual error code that
occurs if you use an indexed field and use the undo event. Here's a sample
of such code- an example I used to answer a question where the person wanted
the option of deleting the entry based on which has the lowest value in
another non-key field. You can alternatively check for the one with the
newest entry date, etc:


Option Compare Database
Dim dbs As Database
Dim rst As Recordset
Dim ii As Long, iii As Long
Dim SQLStmt As String
Option Explicit


Private Sub CheckIndex()

On Error GoTo CheckIndex_Error
' first you gotta get the values of the new record that you'll work with
later on
If Not IsNull(Me!TransID) And Not IsNull(Me!Value) Then
ii = Me!TransID
iii = Me!Value
DoCmd.RunCommand acCmdSaveRecord
End If

CheckIndex_Error:
' here we trap the error that there exist 2 similar records (I take it that
the [ID] field is a key field!)
If Err.Number = 3022 Then
MsgBox "Duplicate record. We'll delete the lower valued record." ' a
white lie....
Me.Undo
Set dbs = CurrentDb()
SQLStmt = "SELECT ExampleTable.* FROM ExampleTable WHERE
ExampleTable.[TransID]= ii;"
Set rst = dbs.OpenRecordset(SQLStmt, dbOpenDynaset)
With rst
If !Value < iii Then
!Value = iii
End If
.Close
End With
Else
MsgBox Err.Number & " " & Err.Description, , _
"Private Sub CheckIndex()"
End If
Exit Sub
End Sub

Regards,
Al
 
-----Original Message-----
Hi. Is there a way for Access alerting you by way of pop up or something,
when you are entering data twice, i.e. new customer, to let you know that,
that name has already been entered before just in case you are duplicating a
record? If you have 4 thousand records, it would help the user entering the
data in 'form' to know before hand. So that they can check before
continuing that the customer may have the same name but different address,
in which case they can continue. I've done a query to search for duplicates
and found way too many.
Ta very much.
J
Answer set the Customer Field properties to "No Duplicate"

.
Click to expand...
 
Back
Top