Not allowing duplicate employee last name, first name

[Anne]

My database does have an EmployeeID, which is an autonumber and primary key,
social security number (no duplicated allowed), EmployeeLastName,
EmployeeFirstName, EmployeeMI.

The case happened now, that the user input a new employee, with the social
security number being out by one digit, but used the exact same last and
first name.

How can I set up in the table (or somewhere else?) that combination of
LastName, firstName and MI when it is a duplicate, it is not allowed.

 

[KARL DEWEY]

You can build an index using LastName, firstName and MI set to no duplicates.
I am now running 2007 but if I remember correctly other versions open in
design view, click on VIEW - Indexes. Skip down to a blank row and type in
a name for the combination index, select LastName, firstName and MI fields.
Set Unique to Yes. Set Ignore null to Yes as some may not have MI.

 

[John W. Vinson]

How can I set up in the table (or somewhere else?) that combination of
LastName, firstName and MI when it is a duplicate, it is not allowed.

I once worked with Dr. Lawrence David Wise, Ph.D., and his colleague Dr.
Lawrence David Wise, Ph.D. Larry was a tall, blond affable chemist; L. David
was a stocky, dark, taciturn biologist.

In other words, this is unwise. You could very well have two employees who
both happen to have the same name! At the most I'd WARN the user, not prohibit
the addition. Perhaps something like this in the Form's BeforeUpdate event:

Private Sub Form_BeforeUpdate(Cancel as Integer)
Dim rs As DAO.Recordset
Dim iAns As Integer
' get the form's recordsetclone (assuming it contains all employees)
Set rs = Me.RecordsetClone
rs.FindFirst "[LastName] = """ & Me!LastName & """ AND [FirstName] = """ _
& Me!FirstName & """" AND NZ(MI, ' ') = """ & NZ(Me!MI, ' ') & """")
If Not rs.NoMatch Then
iAns = MsgBox("This name already exists. Add it anyway?" _
& vbCrLf & "Click Yes to add a new record, "_
& vbCrLf & "Click No to abandon this record and go to the found one," _
& vbCrLf & "or Cancel to just cancel this addition:", vbYesNoCancel)
Select Case iAns
Case vbYes
' do nothing, just let the record be added
Case vbNo
Cancel = True
Me.Bookmark = rs.Bookmark ' jump to the found record
Case vbCancel
Cancel = True
Me.Undo ' erase the form
End Select
End If
Set rs = Nothing
End Sub

 

[Anne]

What a great solution.
It also lends itself to the question of how the user would know which of the
2 names to use if they are exactly alike?
Perhaps a 4th field for more info might need to be used which would have
some explanation of how to distinguish one from the other, like a department.

How can I set up in the table (or somewhere else?) that combination of
LastName, firstName and MI when it is a duplicate, it is not allowed.

I once worked with Dr. Lawrence David Wise, Ph.D., and his colleague Dr.
Lawrence David Wise, Ph.D. Larry was a tall, blond affable chemist; L. David
was a stocky, dark, taciturn biologist.

In other words, this is unwise. You could very well have two employees who
both happen to have the same name! At the most I'd WARN the user, not prohibit
the addition. Perhaps something like this in the Form's BeforeUpdate event:

Private Sub Form_BeforeUpdate(Cancel as Integer)
Dim rs As DAO.Recordset
Dim iAns As Integer
' get the form's recordsetclone (assuming it contains all employees)
Set rs = Me.RecordsetClone
rs.FindFirst "[LastName] = """ & Me!LastName & """ AND [FirstName] = """ _
& Me!FirstName & """" AND NZ(MI, ' ') = """ & NZ(Me!MI, ' ') & """")
If Not rs.NoMatch Then
iAns = MsgBox("This name already exists. Add it anyway?" _
& vbCrLf & "Click Yes to add a new record, "_
& vbCrLf & "Click No to abandon this record and go to the found one," _
& vbCrLf & "or Cancel to just cancel this addition:", vbYesNoCancel)
Select Case iAns
Case vbYes
' do nothing, just let the record be added
Case vbNo
Cancel = True
Me.Bookmark = rs.Bookmark ' jump to the found record
Case vbCancel
Cancel = True
Me.Undo ' erase the form
End Select
End If
Set rs = Nothing
End Sub

 

[John W. Vinson]

It also lends itself to the question of how the user would know which of the
2 names to use if they are exactly alike?
Perhaps a 4th field for more info might need to be used which would have
some explanation of how to distinguish one from the other, like a department.

Sure. It's pretty common to have a Combo Box storing a personID and displaying
"Lastname, firstname mi" concatenated - but also including another appropriate
disambiguator (nickname, department name, phone number, birthdate, etc.)