Pre-defining part of an autonumber primary key

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

sduffield2 - 02 May 2005 18:21 GMT

Is there a way for Access to set the primary key automatically based on the
first letter in a certain field a record (i.e., Last_Name)? I've been doing
it manually (A.001, A.002...) and would like to input new records without
having to look back and see what the ID for the last record entered was.

Thanks ahead of time for your input!
Scot

Reply to this Message

John Vinson - 02 May 2005 19:41 GMT

>Is there a way for Access to set the primary key automatically based on the
>first letter in a certain field a record (i.e., Last_Name)? I've been doing
[quoted text clipped - 3 lines]
>Thanks ahead of time for your input!
>Scot

This is called an "Intelligent Key" - and unfortunately that's not a
compliment. Storing two pieces of information in one field is not good
design. Think about the problems when a person's name changes; do you
change K-312 to W-312 when Miss Keith becomes Mrs. Williams? Do you
then cascade those changes to other tables? What about to all of the
sheets of paper, PostIt notes, emails and human memories containing
K-312?

It's usually better to keep the concepts of Primary Key and variable
data separate. You can use "real" data as a primary key *IF* that data
meets three criteria: it should be unique, stable, and (preferably)
short. Names fail on all three counts; initials are neither unique nor
stable so having a primary key which depends on an initial is not good
design.

The actual answer to your question is "yes, with some code" - the
AfterUpdate event of the Last_Name field could contain code to do
this. But it would be complex, hard to maintain, and - as I say - a
bad design. Post back if you want to do so anyway.

John W. Vinson[MVP]

Reply to this Message