Duplicate entries in a table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mbergman - 05 Jan 2004 17:21 GMT

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.

Reply to this Message

Immanuel Sibero - 05 Jan 2004 17:35 GMT

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

> How do you format a table to report/recognize duplicate
> entries without prohibiting you from entering them?
[quoted text clipped - 3 lines]
> still allows us to enter name twice becasue it is actually
> two different people.

Reply to this Message

Tim Ferguson - 05 Jan 2004 18:27 GMT

> 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

Reply to this Message

John Vinson - 05 Jan 2004 19:11 GMT

>How do you format a table to report/recognize duplicate
>entries without prohibiting you from entering them?
[quoted text clipped - 3 lines]
>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

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

Reply to this Message