How can I avoid duplicate entries?

I've reviewed this over and over again and its not working for me or I'm not
understanding. Here is my problem:

I have a field title WBNNumber. This field can not contain duplicates
within the database. I've set the index field to No Duplicates; however, I
want to change the "generic" error message to a "user friendly" message for
my users.

Please give me guidance...I am very novice when it comes to code.

Thanks.

"Dirk Goldgar" wrote:

"Jane" wrote in message
news
How can I make sure that users do not enter a person more than once
on my database?

Is it possible to programe a message box to pop up and warn a user
that (for example) a person's family name has already been entered in
the database? And if so, how can I go about doing it?

Any help greatly apreciated.

It's certainly possible, so long as you can define what constitutes a
duplicate, or at least a probable duplicate. You can set a unique index
on a field or a combination of fields, which will raise an error if you
try to add a record that exactly duplicates the fields in the index.
However, you may need more flexibility. For example, it's unlikely you
would want to prohibit two people with the same family name from being
recorded in the database.

Here's some example code for a form's BeforeUpdate event that checks for
a duplicate name and offers the user the option of saving the record or
going to the apparent duplicate.

'----- start of example code -----
Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim varID As Variant

If Me.NewRecord Then

varID = DLookup("ID", "MyTable", _
"FamilyName = " & Chr(34) & Me!FamilyName & Chr(34))

If Not IsNull(varID) Then

If MsgBox( _
"A record was found for the same family name. " & _
"Do you want to cancel these changes and go to
that " & _
"record instead?", _
vbQuestion + vbYesNo, _
"Possible Duplicate") _
= vbYes _
Then
Cancel = True
Me.Undo
Me.Recordset.FindFirst "ID = " & varID
End If

End If

End If

End Sub
'----- end of example code -----

In this particular example, where we're only checking the family name,
it would probably be better to check in the AfterUpdate event of the
FamilyName text box, rather than waiting until a completed record is
about to be saved. But the above is a general example.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)