Duplicate entry problem

[PeteEHP]

I am trying to submit data to a table and have access check fo
duplicates. my button is called cmdsearch. My field is surname. So whe
the user clciks the button it checks to see if there are duplicates, i
their are a message box appears if not then it submitts the record???
Any help no one has been able to help me??


somone gave me this script but dosent work fully it just shows messag
box and submits the record any way so dups are being submitted???


anystring = DLookup("[SurName]", "Id Card Order", "[Surname] = "
Chr$(34) & Surname & Chr$(34))
If anystring <> "" Then MsgBox "Person already Submitted"
Cancel = Tru

 

[John Vinson]

somone gave me this script but dosent work fully it just shows message
box and submits the record any way so dups are being submitted???


anystring = DLookup("[SurName]", "Id Card Order", "[Surname] = " &
Chr$(34) & Surname & Chr$(34))
If anystring <> "" Then MsgBox "Person already Submitted"
Cancel = True

Try instead:

If Not IsNull(DLookup("[SurName]", "Id Card Order", _
"[Surname] = " & Chr$(34) & Surname & Chr$(34)) Then
Cancel = True
Msgbox "Person already submitted"
End If

I presume (you don't say) that this is *a portion* of the [Event
Procedure] in a Form's BeforeUpdate event.

One concern here: If you have two people with the same surname (and I
know people right here in little Parma named Brad Brown, Jenny Brown,
Fred Brown, Fred Brown, and Fred Brown) your code will make it
IMPOSSIBLE to store information about the second one. This may not be
exactly what you want!