Name Exits Form

  • Thread starter Thread starter RGM
  • Start date Start date
R

RGM

I'm creating a special contact list. When a new contact is added I want the
form to check to that the FirstName and LastName does not already exist
before entry can contiue. This should be simple but it's not working for
me. Thanks for any help.
 
I'm creating a special contact list. When a new contact is added I want the
form to check to that the FirstName and LastName does not already exist
before entry can contiue. This should be simple but it's not working for
me. Thanks for any help.
Click to expand...

Note that names are NOT unique: when I was a postdoc there was a Professor
John W. Vinson at the same college. He got one of my paychecks, I got one of
his income tax bills, but we weren't the same person.

I'd use a Form for data entry, and use the form's BeforeUpdate event to check.
You could use code like

Private Sub Form_BeforeUpdate(Cancel as Integer)
Dim DupID As Variant
Dim iAns As Integer
Dim rs As DAO.Recordset
DupID = DLookUp("PersonID", "[LastName] = """ & Me!txtLastName _
& """ AND FirstName = """ & Me!txtFirstName & """")
If Not IsNull(DupID) Then
iAns = MsgBox "A record with this name already exists! Add anyway?" _
& vbCrLf & "Click Yes to add this record anyway, " _
& vbCrLf & "No to jump to the existing record," _
& vbCrLf & "Cancel to quit:", vbYesNoCancel
Select Case iAns
Case vbYes
' do nothing, just add the record
Case vbNo
Cancel = True
Me.Undo ' erase the current entry
Set rs = Me.RecordsetClone
rs.FindFirst "ID = " & DupID
Me.Bookmark = rs.Bookmark
Case vbCancel
Cancel = True
Me.Undo
End Select
End If
End Sub
 
You do realize, don't you, that forcing your user to enter a name before
telling them if the name exists might be considered user-unfriendly?

An alternate approach would be to provide a combobox that lists all existing
contacts (and provides enough additional data to distinguish among the folks
who share names -- see John V's response). The user could begin typing the
name, have the combobox show names that start like that, then shift over
into handling a NotInList event, allowing the user to enter a new contact.

A bit more work for you, a lot less work for your users. And that includes
YOU as a user!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Back
Top