Handle Duplicate Primary Key Field at Time of Entry Instead of at Form Update

[Bob Quintal]

Michael

The Primary Key of a table is used to ensure each row/record is
unique. How is it that you are allowing your users to "create"
primary keys?

A good programmer will allow the user to enter a natural primary key
into a record, rather than putzing around with a surrogate
autonumber key. Besides, if one uses an autonumbre as a key, one
still needs to enforce a unique index on certain fields that should
have been made the primary key, with the Original Poster's issue of
not being able to inform the user that he's entered a duplicate
until the user tries to save the record.

Another approach, if the users must, would be to use a combobox
that LISTS all the current primary keys. When the user begins
typing, the combobox tries to find one that starts the same way.
If the one the user enters is found, the user knows that one
exists and can enter another ... but I'm still not clear on why
you want users creating them!

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Note: This was originally posted at the queries group by mistake
......


Hi Folks - Currently, I am using the Error Event of my form to
capture duplicate key entry. However, this is not invoked until
the form updates. Is
there a way to check for a duplicate after the user tabs out of
the primary
key field? Thanks.

 

[Michael]

Note: This was originally posted at the queries group by mistake ......


Hi Folks - Currently, I am using the Error Event of my form to capture
duplicate key entry. However, this is not invoked until the form updates. Is
there a way to check for a duplicate after the user tabs out of the primary
key field? Thanks.

 

[Bob Quintal]

Note: This was originally posted at the queries group by mistake
......


Hi Folks - Currently, I am using the Error Event of my form to
capture duplicate key entry. However, this is not invoked until
the form updates. Is there a way to check for a duplicate after
the user tabs out of the primary key field? Thanks.

Use the Before Update event of the textbox for the key field to do a
Dlookup() of the key value in the table. Dlookup() will return a null
if not found, and you can allow the user to continue data entry. If it
does return the key, show a message, optionally undo the entered text,
and set cancel to true.

That leaves the cursor in the key field's textbox.

 

[Jeff Boyce]

Michael

The Primary Key of a table is used to ensure each row/record is unique. How
is it that you are allowing your users to "create" primary keys?

Another approach, if the users must, would be to use a combobox that LISTS
all the current primary keys. When the user begins typing, the combobox
tries to find one that starts the same way. If the one the user enters is
found, the user knows that one exists and can enter another ... but I'm
still not clear on why you want users creating them!

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Michael]

If you had a db that used a SSN for a primary key, wouldn't the user have to
enter the SSN into the system? And, wouldn't you want to prevent duplicates?
I'd like to check for the duplicates prior to form update. I want to check
for duplicates as soon as they enter the SSN into the field. Ideas?

Michael

Michael

The Primary Key of a table is used to ensure each row/record is unique.
How is it that you are allowing your users to "create" primary keys?

Another approach, if the users must, would be to use a combobox that LISTS
all the current primary keys. When the user begins typing, the combobox
tries to find one that starts the same way. If the one the user enters is
found, the user knows that one exists and can enter another ... but I'm
still not clear on why you want users creating them!

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

Michael

Brace yourself...

You've opened can of worms ... there are some folks who treat the "proper"
form of primary keys (natural vs. surrogate) as a religious matter.

You, of course, are free to use whatever you want as a Primary Key... but
it's helpful to know about the advantages and limitations of your choices.
.... and a bit more about "primary keys".

For instance, since a primary key is a unique identifier, you need to use a
value you can be sure is unique. In your situation, are you 100% certain
that there are no "duplicate" users of SSN? (hint: search on "identity
theft" on-line<G>).

Moreover, you'll need to HAVE a unqiue identifer for each row to use a value
as a primary key. Are you also 100% certain that every (I'm guessing
'person') in your table will have a (valid) SSN? (hint: even if you are
only working with 'persons' in the USA, there are a few here and there who
do not wish to be known to the Social Security Administration<G!>).

It's no skin off my nose if you choose a natural or a surrogate primary key,
as long as you can get your application to do what you need it to. Be
aware, however, that your "choice" may make you a "bad" person in some camps
<G!!!>

Best of luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

If you had a db that used a SSN for a primary key, wouldn't the user have
to enter the SSN into the system? And, wouldn't you want to prevent
duplicates? I'd like to check for the duplicates prior to form update. I
want to check for duplicates as soon as they enter the SSN into the field.
Ideas?

Michael

 

[Tony Toews [MVP]]

A good programmer will allow the user to enter a natural primary key
into a record, rather than putzing around with a surrogate
autonumber key. Besides, if one uses an autonumbre as a key, one
still needs to enforce a unique index on certain fields that should
have been made the primary key, with the Original Poster's issue of
not being able to inform the user that he's entered a duplicate
until the user tries to save the record.

That entirely depends on the data. If the data is in your control,
such as unit numbers on your fleet of equipment then it is possible to
have unique primary key. Even there though I use an autonumber
primary key as otherwise things start getting real ugly on child
tables.

What happens when a first, middle and last name and birth date are
identical. This has happened to a family member. Furthermore the
other person with the same name and birth date has a criminal record
and thus isn't allowed into the US. Causes my family member all kinds
of trouble.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Bob Quintal]

That entirely depends on the data. If the data is in your
control, such as unit numbers on your fleet of equipment then it
is possible to have unique primary key. Even there though I use
an autonumber primary key as otherwise things start getting real
ugly on child tables.

Yes, it depends on the data. ONLY if the data presents no genuine
primary key, such as your name example, should one resort to a
surrogate key, either an autonumber, or a derived sequence number.

As to getting ugly, I don't have that problem, even with a four
field primary key, as I have in several tables in some of my
databases.

..

What happens when a first, middle and last name and birth date are
identical. This has happened to a family member. Furthermore the
other person with the same name and birth date has a criminal
record and thus isn't allowed into the US. Causes my family
member all kinds of trouble.

Tony

 

[Tony Toews [MVP]]

Yes, it depends on the data. ONLY if the data presents no genuine
primary key, such as your name example, should one resort to a
surrogate key, either an autonumber, or a derived sequence number.

Now you're going to start a religious war. <smile> I use autonumbers
on all my tables. And I'm quite happy to do so. I do have, where
appropriate, non duplicate indexes on other field(s)

As to getting ugly, I don't have that problem, even with a four
field primary key, as I have in several tables in some of my
databases.

Among other things the various wizards don't do a good job of handling
multi field primary keys. Such as in a subform or subreport.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[rquintal]

Now you're going to start a religious war. <smile> I use autonumbers
on all my tables. And I'm quite happy to do so. I do have, where
appropriate, non duplicate indexes on other field(s)

No Tony, I'm agnostic as far as database design goes. I have enough
experience to know that the autonumber is not a panacea. Besides, it's
my opinion, and like everybody, entitled to one, using an autonumber
primary key is a violation of normalization rules as set forth by
Boyce and Codd when there is a valid natural key available in the
data, even if it's split across several fields. You are certainly
entitled to hold another view.

Among other things the various wizards don't do a good job of handling
multi field primary keys. Such as in a subform or subreport.

I don't seem to find the subform Wizard any worse than any other
Wizard that comes with Access. I tend not to use them except for
simple tasks, like putting a Close button on a form. And setting up a
subform using the properties is easier than using the wizard, in my
opinion.

Have a nice day.