Creating the Primary Key

  • Thread starter Thread starter Jeff
  • Start date Start date
J

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
 
Jeff said:
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.
Click to expand...


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")
 
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
Click to expand...

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.
 
Jeff said:
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.
Click to expand...

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.
 
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
 
Jeff said:
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
Click to expand...


Just rememebr to IGNORE my idea and use Barton's
 
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."
Click to expand...

:-{(

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.
Click to expand...

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.
 
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
 
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.
 
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.
Click to expand...

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?
Click to expand...

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?
Click to expand...

There's no code in Marshall's example that would raise such a message. What is
your actual code? What message do you get?
 
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?
Click to expand...

Well, for a little time after Ahmad converts, he can't be al-Hadji,
since only those who've made the pilgrimage to Mecca can call
themselves that.

(not that it has any bearing whatsoever on your quite valid point,
of course...)
 
If Me![Key] & "" = "" Then
Click to expand...

Michael Kaplan always recommended testing empty strings with Len()=0
and IsNull() is, I believe, faster than concatenating the Null with
an empty string. And in VBA, it's better to use vbNullString and not
"" because the memory for the constant vbNullString is already
allocated.

None of these things matter for a single line of code, but in loops
they could make a significant difference. Because of that, I think
it's good to develop the habit of using the most efficient methods:

If IsNull(Me!Key) Then

If Len(strVariable) = 0 Then

If strVariable = (Me!Key & vbNullString) Then

Again, not a difference for a single line of code, but good habits
to develop so that you code all your loops efficiently.
 
Well, for a little time after Ahmad converts, he can't be al-Hadji,
since only those who've made the pilgrimage to Mecca can call
themselves that.

(not that it has any bearing whatsoever on your quite valid point,
of course...)
Click to expand...

<g> My assumption was that he kept his former name until after the Pilgrimage
and changed it in honor of the event. Most Moslem converts keep their English
names, IME.
 
None of these things matter for a single line of code, but in loops
they could make a significant difference. Because of that, I think
it's good to develop the habit of using the most efficient methods:

If IsNull(Me!Key) Then

If Len(strVariable) = 0 Then

If strVariable = (Me!Key & vbNullString) Then

Again, not a difference for a single line of code, but good habits
to develop so that you code all your loops efficiently.
Click to expand...

Thanks, David; noted.
 
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
 
Back
Top