Creating the Primary Key

[Jeff]

When adding a new record in a table via a form, I'd like to be able to
automatically generate a primary key id.

The current format of the ids is first initial, last initial,
sequential number starting from 001 that is not used.

So Jeff Marks would be JM001, John Miller would become JM002.

Is there a way to do this using the text manipulation strings? I could
manage first and last initial, but the unused 3 digit number baffles
me.

Thanks

Jeff

 

[Marshall Barton]

When adding a new record in a table via a form, I'd like to be able to
automatically generate a primary key id.

The current format of the ids is first initial, last initial,
sequential number starting from 001 that is not used.

So Jeff Marks would be JM001, John Miller would become JM002.

Is there a way to do this using the text manipulation strings? I could
manage first and last initial, but the unused 3 digit number baffles
me.

Assuming you have fields for firstname and lastname, use the
form's BeforeUpdate event:

Me.pkfield = Left(Me.firstname,1) & Left(lastname,1) _
& Format(Nz(DMax("Val(Mid(pkfield,3)", "thetable", _
"Left(firstname,1)='" & Left(Me.firstname,1) & "' And " _
& "Left(lastname,1)='" & Left(Me.lastname,1) & "' ") _
, 0)+1, "000")

 

[John W. Vinson]

When adding a new record in a table via a form, I'd like to be able to
automatically generate a primary key id.

The current format of the ids is first initial, last initial,
sequential number starting from 001 that is not used.

So Jeff Marks would be JM001, John Miller would become JM002.

Is there a way to do this using the text manipulation strings? I could
manage first and last initial, but the unused 3 digit number baffles
me.

Thanks

Jeff

This can be done but... it's generally a Bad Idea. This is called an
"Intelligent Key" because it attempts to store data in the key itself; this
violates not one but two basic tenets of relational design. For one, the field
is not "atomic" (it contains two disparate chunks of data); worse, it makes
the primary key dependent on other fields in the record.

What will you do if JM004 (Janet Miller) marries and becomes Janet Zambrowski?
Or Bill Evans, BE002, converts to Islam and becomes Ahmad al-Hadjii? Reset
their ID in this table and all the foreign key instances, or leave the (now
incorrect) initials?

And what benefit do you get from it? Someone would still have to look at a
form to see who KA003 is, unless it's someone they know both by name and ID;
for safety they should look it up even if they do know, otherwise Karen
Anderson might get the reprimand letter that should go to Kevin Arthur!

This kind of key might have made sense when you needed one text identifier for
all purposes, but in a database you can easily display name, hire date, etc.
on demand.

 

[Mike Painter]

When adding a new record in a table via a form, I'd like to be able to
automatically generate a primary key id.

The current format of the ids is first initial, last initial,
sequential number starting from 001 that is not used.

So Jeff Marks would be JM001, John Miller would become JM002.

Is there a way to do this using the text manipulation strings? I could
manage first and last initial, but the unused 3 digit number baffles
me.

It can be done but why bother?
Such keys were useful at a time when computers were slow or paper was used
but are of litttle value now.

Add a date field and use a combo to select teh name sorted by the date.


A two part key wold be easier since sorting on FLnnn will be done
alphnumericaly.

MyNewKey = left(Firstname,1) & left(lastname,1)
Then a query to see if there is something 'where SomeKey Like MyNewKey &
"*"'
If there are no results
Append the record with SomeKey = MyNewKey & "001"

if not use dLast in a query that selects all the values for those initials
and that should give you the highest value.

 

[Jeff]

I inherited an ACC2003 database for which I've been asked to make some
modifications. Generating the key is one of them. They are already
using this approach, and while I agree that it's generally a waste of
time and effort, the users of the database want it. Unfortunately,
it's one of those cases of "we've always done it this way."

I appreciate the help in identifying how to do this.

Thanks

jeff

 

[Mike Painter]

I inherited an ACC2003 database for which I've been asked to make some
modifications. Generating the key is one of them. They are already
using this approach, and while I agree that it's generally a waste of
time and effort, the users of the database want it. Unfortunately,
it's one of those cases of "we've always done it this way."

I appreciate the help in identifying how to do this.

Thanks

Just rememebr to IGNORE my idea and use Barton's

 

[John W. Vinson]

I inherited an ACC2003 database for which I've been asked to make some
modifications. Generating the key is one of them. They are already
using this approach, and while I agree that it's generally a waste of
time and effort, the users of the database want it. Unfortunately,
it's one of those cases of "we've always done it this way."

:-{(

Alas, all too often the case. "The way we've always done it" is not
necessarily the best way, but it may be the only acceptable way.

I appreciate the help in identifying how to do this.

Marshall's code looks perfect. The only downside is that the composite ID will
not be created until the user leaves the new record; it'll show when they come
back to it but there'll be no immediate feedback.

 

[Jeff]

Just a couple of questions.

1) The field names contain a blank -- they are First Name, Last Name,
Member ID for the primary key, and Member Data for the table name. How
do I handle this in the code? (I'd love to change these but they
appear in too many places for that to happen.

2) I'm assuming that I should be checking to see if the key is null on
the form before calculating the new key?

3) I added the code to the BeforeUpdate event. When I went to add a
new record (using the >* key), I got a message about calculating the
key. Should that happen before I add any data?

Thanks..

Jeff

 

[Jeff Boyce]

Remember that what the users see and what you are doing "behind the curtain"
do not have to be identical. You could follow the excellent advice you've
received about not stuffing all those facts into a single field, store each
fact in a separate field, and use a query to 'concatenate' the fields
together into something the user would see and think is the "key".

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[John W. Vinson]

Just a couple of questions.

1) The field names contain a blank -- they are First Name, Last Name,
Member ID for the primary key, and Member Data for the table name. How
do I handle this in the code? (I'd love to change these but they
appear in too many places for that to happen.

Enclose the fieldnames in square brackets. E.g. instead of

Me!lastname

use

Me![Last Name]

2) I'm assuming that I should be checking to see if the key is null on
the form before calculating the new key?

Well? Should you? What should you do if it's NOT null? Do you want to replace
the "key" if a name changes? What other tables will be affected if you do? Do
you want to leave the old key, or what?

You can certainly put an IF statement in the code:

If Me![Key] & "" = "" Then
<take action for a blank Key field>
Else

3) I added the code to the BeforeUpdate event. When I went to add a
new record (using the >* key), I got a message about calculating the
key. Should that happen before I add any data?

There's no code in Marshall's example that would raise such a message. What is
your actual code? What message do you get?

 

[Jeff]

Well, I've learned more than I expected about Islam

The current system that I am working with does nothing if the name
changes, so Bob stays Bob and women keep their last name in the key.

I'm getting a compile error when I use this code. I thought everything
was the same as the example which was given, but apparently not.


Private Sub Last_Name_BeforeUpdate(Cancel As Integer)
If IsNull(Me![Member ID]) Then
   Me.[Member ID] = Left(Me.[First Name],1) & Left([Last Name],1) _
        & Format(Nz(DMax("Val(Mid([Member ID],3)", "Member Data", _
        "Left([First Name],1)='" & Left(Me.[First Name],1) & "' And "
_
        & "Left([Last Name],1)='" & Left(Me.[Last Name],1) & "' ") _
        , 0)+1, "000")

End If
End Sub

Thanks

Jeff