[AccessD] Primary Key Violation

Gina,

Not to chastise, but it is important to distinguish between "primary key"
and "unique index".

1) A unique index is an index set on one or more columns (fields) to prevent
entering the same data in that field or fields.

2) A primary key is used to fill foreign keys in child tables, which creates
the "link" between parent / child tables (records).

Understanding this, then understand that Access automatically sets up a
unique index on any field / set of fields used as a PK, i.e. has the key
symbol next to it / them.  So it often APPEARS that the PK is a unique
index, but they are in fact two completely different things.

I am not going to go into the natural vs artificial key thing, there is
ample coverage in the archives.

It appears from another message from you where you discuss an autonumber
field, that you are actually talking about a unique index, not a PK.  The
autonumber appears to be your PK (and has a unique index on it as well we
assume), and then you somehow created a unique index on the set of fields
(last name / first name) such that identical data cannot be entered in these
fields taken as a whole.

Now, to your problem.  There is no way to test for the unique index being
violated (identical info) until the point where you  attempt to store the
data.  At that point you see the errors you indicate generated by JET.
Other people have already discussed trapping the errors which is the "after
the fact" way of doing things.  Personally, I don't like that method because
it implies you have already entered a TON of data (address, birthday, sexual
preferences, etc. ad nausium) and this sorely pisses off the user to have to
enter all that stuff only to be told that it was already in there when at
the very end they try to save it.

It is therefore preferable to take the info in the unique index and "look it
up" somehow after just that much has been entered, in effect bypassing
entering anything that doesn't absolutely have to be entered.

One list member discussed the idea of a combo to see if it exists already.
That works well and is widely used.  Another way is to have a search screen
where wild cards can be used, particularly if they are entering names from
handwritten lists where spelling is suspect.  This allows getting a "set" of
records based on what is already entered, and visually inspecting the set to
see if the one desired is in it etc.

So my answer to you is no NOT let them get to the point where they get the
error, but rather use some form of search and if not found, then enter the
data.

John W. Colby
www.ColbyConsulting.com

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Gina Hoopes
Sent: Monday, November 17, 2003 1:42 PM
To: AccessD at databaseadvisors.com
Subject: [AccessD] Primary Key Violation


I've got a seemingly stupid question, but I can't figure it out.  I've got a
form that collects a staff member's first and last name and uses that
combination as a primary key (unfortunately, no one can remember their
employee ID and we don't collect SSN or anything unique they could enter).
If they violate the exclusivity of that first/last name combination, they
get the convoluted error message that Access puts out which only serves to
confuse.  Is there a way for me to replace that message with my own and, if
so, how do I check for the violation and where would I put the error
message?  At the moment it comes up when they click the "done" button, which
seems like a good place.

Thanks,
Gina

p.s. Please don't chastise me for perceived primary key protocol violations.

_________________________________________________________________
Send a QuickGreet with MSN Messenger
http://www.msnmessenger-download.com/tracking/cdp_games

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com