I can't believe that you don't have a dup FN+LN. It's only a matter of time
before that gets you into trouble, then what will the new John Smith use for
an ID? You should switch to an alpha ID like first 3 of last name and last 4
of ssn (SMI9876), or similar to really reduce the chance of conflicts.
Anyway, this is for a string Turn-In Key instead of the names:

Private Sub turn_in_key_BeforeUpdate(Cancel As Integer)
Dim stKey As String
'check to see if this is a duplicate value before updating
stKey = Nz(DLookup("turn_in_key", "dbo_turn_in1", "turn_in_key='" &
Me.turn_in_key & "'"), 0)
Debug.Print stKey
If stKey 0 Then
' this item has already been selected
MsgBox "This Turn-In Number already exists."
Cancel = True
Me.Undo
End If
End Sub


"Steve Stad" wrote:

John/Jeff.

The database has multiple users from 15 depts entering Employee data for a
large organization ( 1000 recs). The employees move from one dept to
another so we want to ensure two different users can not enter the same
person in the db from their different dept forms. I checked for dups in the
Master table. There are dup last names and dup first names but NO dup
Lastname and Firstname combined. I am not sure if or what field to set to
'no dups or unique' in the composite index set up to prevent dup first/last
name, mid init combined.
Could you be so kind to provide sample VBA code for the BeforeUpdate event
of the Form to *check* for duplicates using DLookUp; and warn the user and
give them the opportunity to compare the two records and cancel or change
their addition.

"John W. Vinson" wrote:

On Fri, 30 Apr 2010 15:13:02 -0700, Steve Stad
wrote:

I have 3 fields for Name. LastNm, FirstNm, and Middle Initial. How do I
ensure the full name, i.e., "LastNm, FirstNm, MiddleInitial" combined are
unique and not duplicated in the table?

Why would you want to put in this unreasonable restriction?

I once worked with Dr. Lawrence David Wise and with Dr. Lawrence David Wise.
Larry was a tall blond affable chemist, L. David was a stocky dark taciturn
biologist. But the company did not insist that one of them change his name to
accommodate a flawed database design.

At the most you should have VBA code in the BeforeUpdate event of the Form
used to enter data, to *check* for duplicates using DLookUp; it should warn
the user and give them the opportunity to compare the two records and cancel
their addition.

I thought I could index but not sure
where/what to check for 'Unique' (e.g., on all three fields?) or if I need to
use a primary key.

If you use the Indexes tool in table design view, you can enter three fields
(or ten if you wish) in the right column, with a distinctive index name in the
left column. There's a Unique checkbox.

I got a msg saying 'changes were not successful because
the create duplicate values in index, primary key, or relationship.'

Sounds like you already have duplicate names; run a Totals query, grouping by
the three fieldnames, and Counting the primary key (or any non null field);
use a criterion of 1 to find which names are duplicated.

But you should certainly NOT prohibit duplicate names. Duplicate names are
valid in the real world, and your database should model the real world!
--

John W. Vinson [MVP]
.