Preventing duplicate names

[Bob Richardson]

I've set up FirstName and LastName as a single Key, and set the key to be
Unique.

When I enter a record with the same first and last names, the system accepts
the new record, but erases whatever I had as the firstname and sets that to
blank. If I enter another record with the same lastname, and no firstname,
the system accepts that with no error message, but doesn't actually add a
record to the DB.

I'd like to intercept these situations, print my own error message, and then
return to the form so the user can change the name. How would I do that?

 

[John Vinson]

I've set up FirstName and LastName as a single Key, and set the key to be
Unique.

So you'll NEVER have two people in the database who just happen to
have the same first and last names?

I know three people named Fred Brown, right here in little Parma; and
I used to work with Dr. Lawrence David Wise, Ph.D., and his colleague,
Dr. Lawrence David Wise, Ph.D. Larry was tall, blond and affable; L.
David was stocky, dark and taciturn.

This is a bad design, and does not reflect reality. In the real world,
names are NOT unique. Your database should reflect the real world!

When I enter a record with the same first and last names, the system accepts
the new record, but erases whatever I had as the firstname and sets that to
blank. If I enter another record with the same lastname, and no firstname,
the system accepts that with no error message, but doesn't actually add a
record to the DB.

This is not normal behavior. It sounds like you're somehow suppressing
the error message about Duplicate Key, or that you have some
already-existing VBA code.

I'd like to intercept these situations, print my own error message, and then
return to the form so the user can change the name. How would I do that?

If you really, really want to do it wrong, use the Form's BeforeUpdate
event; use DLookUp to search for records with the name to find that
person (or the other person who happens to have the same name), and
cancel the addition if it finds one.

John W. Vinson[MVP]

 

[Bob Richardson]

I'm very willing to learn the right way...do you have a link to the
technique?

 

[Bob Richardson]

I don't think I've been clear about what I'd like to do. My primary key is
an autonumber key. This is necessary, among other reasons, because people
change names, and a long name is a cumbersome key to drag around a lot of
files.

That said, users of the DB think of names as the main key. When two people
have the same name, changes to the name can be made to make them unique.
Instead of two John Smith's, you can have one John R Smith, and one John S
Smith. There are various ways to make the names unique. People with common
names often have nicknames that they've used throughout their lives to cope
with their "problem." John "Fuzzy" Smith.

What I'm trying to do is force the person entering data to some up with
unique names, if that's necessary. Thus, I have made Lastname/Firstname a
unique key. When the user tries to exit the form, I want to print my own
error message and put the user back into the form. How would I do that? Use
the Form's OnExit? When/where is the duplicate key error raised?

 

[Larry Daugherty]

Hi Bob,

Your explanation helps.

Your best link to this kind of issue is to keep a dialogue going with
John Vinson until you understand the issues and his suggested
solution(s). He KNOWS these things!

You seem to be trying to force your users into miss-representing the
real world so that your application will return correct results. In
our business, it's a given that it's more important to get things
right than to get them quickly or even easily. We strive to do all of
those things but data integrity comes way ahead of what ever may be in
second place.

You may have to work with your users to promote mutual understanding
of the issues and the best resolution for each one. Your users and
their assumed level of knowledge about what they're doing as they use
your application is very important. However, the responsibility for
bullet proofing the application is your. That's why you surfaced the
issue here Do they KNOW that the data they want to enter is about
someone whose name should already be in the database?

Your users must be made aware that names are not necessarily unique
and that some speed may have to be sacrificed to assure that they're
entering data against the correct PERSON's record. To begin, I'd get
rid of that UNIQUE constraint on the full name. It forces your
application to create data that's different from the real world. If
your list of names isn't huge, you can use an autoexpanding combobox
to bring up the name just entered and to make the fact that there is
more than one name like that in your database evident. On the lookup
I'd include some other fairly unique piece of information to help
resolve which person is the right one. For larger lists, the
autoexpand feature may slow you too much. You could also use the
NotInList event of the combobox to alert your user that the name they
typed doesn't exist in the database and ask them if they'd like to add
the name...

There are lots of books about Access and some of them are very good.
I particularly recommend "The Access [YourVersion] Developer's
Handbook" by Ken Getz et al from Sybex. You should only need Volume
1, the Desktop edition. There is about half a page of references to
specific performance issues in the index. There is a lot of
information in Microsoft's Knowledge Base but you have to seek it out
one problem at a time.

HTH

 

[Bob Richardson]

You are right that it's my job to keep the DB bulletproof (monkey's on a
keyboard proof). I can live with removing the unique requirement on name -
but how do I notify the user that someone with the same name is already in
the DB. I presume I'll want to have a message box in the form's AfterUpdate
event, showing name, and perhaps address of the "clone" (at least the first
one if there are more than two with the same name) - but how can I get that
info? I'm still struggling with a way to determine if the name of the
current record matches the name (First and last) of someone else in the DB.

It looks like the last Getz book was on 2002, and I have Access 2003, but
that shouldn't be a problem. Thanks for the tip, I'll pick up a copy.

Hi Bob,

Your explanation helps.

Your best link to this kind of issue is to keep a dialogue going with
John Vinson until you understand the issues and his suggested
solution(s). He KNOWS these things!

You seem to be trying to force your users into miss-representing the
real world so that your application will return correct results. In
our business, it's a given that it's more important to get things
right than to get them quickly or even easily. We strive to do all of
those things but data integrity comes way ahead of what ever may be in
second place.

You may have to work with your users to promote mutual understanding
of the issues and the best resolution for each one. Your users and
their assumed level of knowledge about what they're doing as they use
your application is very important. However, the responsibility for
bullet proofing the application is your. That's why you surfaced the
issue here Do they KNOW that the data they want to enter is about
someone whose name should already be in the database?

Your users must be made aware that names are not necessarily unique
and that some speed may have to be sacrificed to assure that they're
entering data against the correct PERSON's record. To begin, I'd get
rid of that UNIQUE constraint on the full name. It forces your
application to create data that's different from the real world. If
your list of names isn't huge, you can use an autoexpanding combobox
to bring up the name just entered and to make the fact that there is
more than one name like that in your database evident. On the lookup
I'd include some other fairly unique piece of information to help
resolve which person is the right one. For larger lists, the
autoexpand feature may slow you too much. You could also use the
NotInList event of the combobox to alert your user that the name they
typed doesn't exist in the database and ask them if they'd like to add
the name...

There are lots of books about Access and some of them are very good.
I particularly recommend "The Access [YourVersion] Developer's
Handbook" by Ken Getz et al from Sybex. You should only need Volume
1, the Desktop edition. There is about half a page of references to
specific performance issues in the index. There is a lot of
information in Microsoft's Knowledge Base but you have to seek it out
one problem at a time.

HTH
--
-Larry-
--
p.s. Just looked at your web site. It looks nice and useful. It's
been over 30 years since I last visited Washington I understand that
Whidby Island is beautiful.

I don't think I've been clear about what I'd like to do. My primary key is
an autonumber key. This is necessary, among other reasons, because people
change names, and a long name is a cumbersome key to drag around a lot of
files.

That said, users of the DB think of names as the main key. When two people
have the same name, changes to the name can be made to make them unique.
Instead of two John Smith's, you can have one John R Smith, and one John S
Smith. There are various ways to make the names unique. People with common
names often have nicknames that they've used throughout their lives to cope
with their "problem." John "Fuzzy" Smith.

What I'm trying to do is force the person entering data to some up with
unique names, if that's necessary. Thus, I have made Lastname/Firstname a
unique key. When the user tries to exit the form, I want to print my own
error message and put the user back into the form. How would I do that? Use
the Form's OnExit? When/where is the duplicate key error raised?

 

[John Vinson]

You are right that it's my job to keep the DB bulletproof (monkey's on a
keyboard proof). I can live with removing the unique requirement on name -
but how do I notify the user that someone with the same name is already in
the DB. I presume I'll want to have a message box in the form's AfterUpdate
event, showing name, and perhaps address of the "clone" (at least the first
one if there are more than two with the same name) - but how can I get that
info? I'm still struggling with a way to determine if the name of the
current record matches the name (First and last) of someone else in the DB.

Sorry for being a while getting back into this thread (and thanks for
the kind words, Larry... <BLUSH!>)

The Form's BeforeUpdate event is preferable, inasmuch as it can be
cancelled. You might try something like this: it uses a Recordset to
determine if a name exists already, and gives the user the option of
adding the new name anyhow, canceling and trying again, or jumping to
the found record instead of adding.

Private Sub Form_BeforeUpdate(Cancel as Integer)
Dim rs As DAO.Recordset
Dim iAns as Integer
Set rs = Me.RecordsetClone ' open the Form's recordset
rs.FindFirst "[LastName] = " & Chr(34) & Me!txtLastname & Chr(34) _
& " AND [FirstName] = " & Chr(34) & Me!txtFirstname & Chr(34)
' Chr(34) is the " character to delimit text strings
If Not rs.NoMatch Then ' Was there a match found?
iAns = Msgbox("This name is already in the database." _
& "Do you want to add it anyway?" _
& "Click Yes to do so, No to erase it and reenter, " _
& "Cancel to cancel this add and jump to the found name:", _
vbYesNoCancel)
Select Case iAns
Case vbYes
Cancel = False
Case vbNo
Cancel = True
Me!txtFirstName.Undo 'erase the entries
Me!txtLastName.Undo
Me!txtFirstName.SetFocus
Case vbCancel
Me.Undo ' erase the whole form
Me.Bookmark = rs.Bookmark ' jump to found record
End Select
End If
End Sub

John W. Vinson[MVP]

 

[Bob Richardson]

Thanks for the great code Jay. This is definitely a better way to go. Larry
was right.

You are right that it's my job to keep the DB bulletproof (monkey's on a
keyboard proof). I can live with removing the unique requirement on name -
but how do I notify the user that someone with the same name is already in
the DB. I presume I'll want to have a message box in the form's
AfterUpdate
event, showing name, and perhaps address of the "clone" (at least the
first
one if there are more than two with the same name) - but how can I get
that
info? I'm still struggling with a way to determine if the name of the
current record matches the name (First and last) of someone else in the
DB.

Sorry for being a while getting back into this thread (and thanks for
the kind words, Larry... <BLUSH!>)

The Form's BeforeUpdate event is preferable, inasmuch as it can be
cancelled. You might try something like this: it uses a Recordset to
determine if a name exists already, and gives the user the option of
adding the new name anyhow, canceling and trying again, or jumping to
the found record instead of adding.

Private Sub Form_BeforeUpdate(Cancel as Integer)
Dim rs As DAO.Recordset
Dim iAns as Integer
Set rs = Me.RecordsetClone ' open the Form's recordset
rs.FindFirst "[LastName] = " & Chr(34) & Me!txtLastname & Chr(34) _
& " AND [FirstName] = " & Chr(34) & Me!txtFirstname & Chr(34)
' Chr(34) is the " character to delimit text strings
If Not rs.NoMatch Then ' Was there a match found?
iAns = Msgbox("This name is already in the database." _
& "Do you want to add it anyway?" _
& "Click Yes to do so, No to erase it and reenter, " _
& "Cancel to cancel this add and jump to the found name:", _
vbYesNoCancel)
Select Case iAns
Case vbYes
Cancel = False
Case vbNo
Cancel = True
Me!txtFirstName.Undo 'erase the entries
Me!txtLastName.Undo
Me!txtFirstName.SetFocus
Case vbCancel
Me.Undo ' erase the whole form
Me.Bookmark = rs.Bookmark ' jump to found record
End Select
End If
End Sub

John W. Vinson[MVP]