creating a combo id for a primary key

[Mike Cranford]

I am wanting to create an ID for each record based on a combination of the
following:
First 2 letters of the lastname
First 2 letters of the firstname
then include a number or numeric sequence based on the order entered, such
as JOBO01
for lastname =Jones, firstname = Bob & 01 denoting the first entry with that
combination. Then if my next entry was Boris Johnson my ID would be JOBO02.
Is there a way for this ID to create itself automatically as I enter the
lastname firstname into a form or datasheet?

Best regards,
Mike

 

[John Vinson]

I am wanting to create an ID for each record based on a combination of the
following:
First 2 letters of the lastname
First 2 letters of the firstname
then include a number or numeric sequence based on the order entered, such
as JOBO01
for lastname =Jones, firstname = Bob & 01 denoting the first entry with that
combination. Then if my next entry was Boris Johnson my ID would be JOBO02.
Is there a way for this ID to create itself automatically as I enter the
lastname firstname into a form or datasheet?

This can be done. It's called an "Intelligent Key" - which is
unfortunately not a compliment!

It was a useful technique back in the 1950's when you needed to have
one sorting field and have it human readable. Since you can now very
easily search on the complete person's name, by firstname, lastname,
or other information, such a key is of very little use; it's hard to
maintain; it breaks if Mary Jones marries and becomes Mary Smith (is
she still MAJO001 or do you now change all the related records to
MASM008?).

Just use an autonumber and keep it hidden from the user for internally
linking the tables.

 

[Linda L.]

Define the ID field in the table as AutoNum.

 

[GVaught]

No. you can't do this at the table level. Primary Keys can't be based on
calculated fields. Your users would have to type this value combination in.
You can create a input mask that would ensure they enter the key as
LetterLetterLetterLetterNumNum. See help on InputMask for exact formatting.

Autonumber will not solve your number increment problem.