Duplicate entries in a table

[mbergman]

How do you format a table to report/recognize duplicate
entries without prohibiting you from entering them?

I.E. Entering two people in table named Bob Smith. We
want table to recognize that Bob Smith already exists, but
still allows us to enter name twice becasue it is actually
two different people.

 

[Immanuel Sibero]

Hi

Well, the two records of Bob Smith aren't really duplicates, are they?

What is stopping you from entering Bob Smith twice now? Are you using that
field (i.e. Name) as a unique identifier?
You need a unique identifier such as Social Security No. or Autonumber.

HTH,
Immanuel Sibero

 

[Tim Ferguson]

I.E. Entering two people in table named Bob Smith. We
want table to recognize that Bob Smith already exists, but
still allows us to enter name twice becasue it is actually
two different people.

Use the "find duplicates" query wizard.

Tim F

 

[John Vinson]

How do you format a table to report/recognize duplicate
entries without prohibiting you from entering them?

I.E. Entering two people in table named Bob Smith. We
want table to recognize that Bob Smith already exists, but
still allows us to enter name twice becasue it is actually
two different people.

You can use some VBA code in the BeforeUpdate events of the name
textboxes. Air code, untested:

Private Sub txtLastName_BeforeUpdate(Cancel as Integer)
Dim rs As DAO.Recordset
Dim iAns As Integer
' Check to see if the user entered a full name
If Me!txtFirstName & "" = "" _
OR Me!txtLastName & "" = "" Then Exit Sub
' Find the name in the Form's recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[LastName] = " & Chr(34) & Me!txtLastName & Chr(34) _
& " AND [FirstName] = " & Chr(34) & Me!txtFirstName & Chr(34)
If Not rs.NoMatch Then
iAns = MsgBox("This name already exists. Add it anyway?" & vbCrLf & _
"Select Yes to add, No to jump to existing record," & _
" Cancel to start this record over:", vbYesNoCancel)
Select Case iAns
Case vbYes
' do nothing, just go on
Case vbNo
Cancel = True
Me.Undo ' erase the current form entry
Me.Bookmark = rs.Bookmark ' jump to the found record
Case vbCancel
Cancel = True
Me.Undo
End Select
End If
Set rs = Nothing
End Sub