primary keys! (1 Viewer)

[badangel]

Hi, I have a problem with creating unique primary keys. I would like each entry to have a unique key made up of 3 letters and then numbers with the first 3 letters used depending on the type of entry it is. The type would first have been selected using a drop down menu I think then the numbers would then have to be autonumbered for each type. Does anyone have any idea how I would go about setting this?? Thanks in advance..

 

[emcf]

Badangel,

I had a similar problem a while ago - http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=37841&highlight=primary+key

What i did in the end was to leave the primary key as an autonumber and concatenate this primary key to another field that provided the reference i wanted. I reckon in your situation you should -

-Leave the primary key as a basic autonumber
-when you select from the drop down box, have a field that captures the first three letters of the type
-set up a field in a query that concatenates the three letter field with the primary key

Hope this helps a fellow jock!

 

[badangel]

Thanks, emcf. I'll try that.

 

[badangel]

Not sure how to create the field that captures the first three letters of the field that I want. Any clues would be greatly appreciated!

 

[emcf]

i'm not sure if this is the best way (well, i'm sure that there is a less ham fisted way to do it) but it seems to work for me.....

on the drop down box i put in some vb code which runs 'after update' -

if [your drop down box field name] = 1 then
[three letter field] = "xyz"
else
if [your drop down box field name] = 2 then
[three letter field] = "abc"
else
[three letter field] = "pqr"
endif
endif


where the 1 and 2 that [your drop down box field name] will equal are the primary key of the look up table

create the [three letter field] as a field on a form and then hopefully when you select one of the options in the drop down box the field should then be populated with the three letters you want.