Access Custom Primary Keys -

ei'd create custom primary keys in access database.

the database going multi-user, need method ensures each key unique when multiple users trying add new records same tables.

the reason need create custom primary keys because database starts off audit trail goes in another, external system have no control over.

this other system allow use of single 12-character length user-defined field pass data of our choice through.

i'd use user-defined field record 12-character code has various abbreviations can extrapolate later (e.g. first 2 characters relate department in our organisation, next 3 characters relate product , on...)

from reading i've done far, custom keys in access seems of minefield.

for purposes though, can kind of see @ least compromise in combining access' autonumber field build primary key want.

here's thinking:

the parts of code want extrapolate later can built our users, example, if department human resources, first 2 characters "hr".

then lets let autonumber in access run field in same table in "hr" entry populated... third field automatically concatenate 2 in same table (not query)...? i.e. this:

| department | autonumber | customprimarykey | |    hr      |     1      |        hr1       | 

if that's can done on event in vba, great (show me code! :))

the second part whether can autonumber concatenate leading zeros ensuring "unique number" part of custom primary key between 99999 , 00001, i.e. occupying same 5 character space this:

| department | autonumber | customprimarykey | |    hr      |     1      |     hr00001      | |    hr      |     2      |     hr00002      | 

it highly unlikely need more 100,000 entries.

i hope possible , safe!

i'd rather leave comment answer don't think you're totally clear on need, i'll try answer best possible. also, i'm not going "show code!" suggest teaches nothing.

in first question of automatically concatenating third field, it's question of how fields being populated.

if it's through form input, can concatenate of component fields key field during update events of controls component fields being populated. in vba can reference members of record accessing form's recordset.

if you're populating field through file import have import specs, perform import excluding key field, open recordset of table imported , iterate through recordset. can learn ado recordsets here. again, i'm not going write code because don't know need for.

if you're populating field through own parser don't have explain how this.

to second question, can right align number in string using format() function. example format(2,"00000") yield "00002" , format(210,"0000") yield "0210". can make number of 0s in want align variable using string() function. example format(2054,string(12-len("hr"),"0")) give "0000002054"

one additional note leave on it's never idea "it highly unlikely would..." , not prepare it. murphy's law pain in b. should consider handing conditions exceed limit key can handle.