Duplicate Records

[Guest]

Is it possible to generate an error message when someone enters duplicate
information through a form field? I have a form with a part number field. I
would like the user to be notified if he/she tries to enter a part number
that is already in the target table.

Help is appreciated.

 

[Chris Reveille]

I got this from a previous post. This assumes you are
dealing with the primary key field.

In the BeforeUpdate event of the control try something like
the following:


if not isnull(dlookup("Field1","myTable","PK1=" & me.pk1)) then
msgbox "Record Already exists!"
cancel=true
endif

or

If Not IsNull(DLookup("SocialSecurityNumber", "client Info", _
"[socialsecuritynumber]=" & "'" &
Me.SocialSecurityNumber & "'")) _
Then
MsgBox "Record Already exists!"
' Cancel = True
Else: MsgBox "Looks Good"
End If

The Dlookup will return a null if there is no match so you
are looking
for a Not null condition to indicate a duplicate. If any of
your
fields are text you will have to wrap the value in Quotes
so that the
value is passed to the Dlookup as a literal string. There
are many
posts in this newsgroup that describe how to do this if you
aren't
sure.

Good Luck

Chris