On Sat, 1 May 2010 14:35:01 -0700, 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.

Surely you have a unique EmployeeID!!??? You should be joining on that.

And you may not currently have the Lawrence David Wise problem but there's no
way to ensure that you won't next week.

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.

Su however, rather than a DLookUp I'll suggest using a recordset. This
assumes (perhaps incorrectly, it can be tweaked if this isn't the case) that
the Form is based on a query returning all the items in the employee table,
and that the primary key of the table is named EmployeeID.

Private Sub Form_BeforeUpdate(Cancel as Integer)
Dim rs As DAO.Recordset
Dim iAns As Integer
Dim strMsg As String
Set rs = Me.RecordsetClone
rs.FindFirst "[LastName] = """ & Me!txtLastName & """ AND [FirstName] = """ _
& Me!txtFirstName & """"
If rs.NoMatch Then
' all is well, no dup for this name, do nothing
Else
strMsg = "This name is already in; add it anyway?" & vbCrLf & _
"Click Yes to add it, No to open that employee's record," & _
"or Cancel to undo this entry:"
iAns = MsgBox(strMsg, vbYesNoCancel)
Select Case iAns
Case vbYes ' add it anyway
' do nothing, just let it be added
Case vbNo ' jump to the other record
Cancel = True ' don't update the table
Me.Bookmark = rs.Bookmark ' jump to the found record
Case vbCancel ' undo this addition
Cancel = True
Me.Undo
End Select
End If
Set rs = Nothing ' clean up after yourself
End Sub
--

John W. Vinson [MVP]