Creating primary key values from field values?

[garnf1]

Just starting out:
I enter new Jobs (orders for services) which have various sub-jobs attached. I want to build the primary key value for each subjob from several codes taken from other tables. e.g if a customer called Eric wants manualNo 12 in Chinese the key for the job would be ER12C.

Any clues to starting? I only have the Northwind to guide me.

 

[Guus2005]

One possible solution:
Create three fields one for ER, one for 12 and one for C
Second possible solution
Create table to store the relation ER12C and a primary key.
Third possible solution
Use ER12C as the primary key
...

 

[garnf1]

Thanks & Eureka

I used a Macro & SetValue to add the three fields from the various fields on other forms, on entry to the Key value I wanted calculating.

I think my question was probably rather low level for everyone out there, but any tips are helpful, thanks.

 

[Colin@Toyota]

Why do you want/need the primary key to be composed of elements from three different fields?

 

[ajetrumpet]

Why do you want/need the primary key to be composed of elements from three different fields?

I was gonna ask the same thing Colin.

Garn, here's an addition to the suggestions that are already out there...

From the info. you gave originally, it seems like maybe you could set standards for the different fields' data?? (e.g. PK = first 2 letters of NAME, product no, first letter of the product language)

Even if that is too strict, you could still set it up that way to be more flexible. If I were gonna do it by establishing criteria like that, I would write a Form Event Sub using String Functions to extract and concat the different parts of the 3 other field values to get the PK value (on a new record entry).

 

[garnf1]

Reply - why I need it

Hello Colin & Aje,

It's to fit in with a system of codes that have already been used visually (by people) for some time on documents etc. to identify jobs & services. So when people enter new jobs (orders) they no longer have to work out and enter the keys themselves, but have it done for them when they select the job descriptions from 'wordy' combo boxes. (Simple beginnings for me with Access I'm afraid)

I like the idea of the Form Event Sub and might use that later. At the moment I have put it in the Enter Event for its field so people see it created when they tab in.

(See my next question - I'm starting on that today)
Thanks

 

[Colin@Toyota]

So, if I understand correctly, it is simply a code for visual recognition by the users? If this is the case could it not just be displayed in a text box or label at the top of the form or report (whatever output they will be using), and then use an AutoNumber for unique IDs in the background?

 

[ajetrumpet]

Are you talking to me Colin??

 

[Colin@Toyota]

Haha! Nope, not you Aje... That question was directed to Garnf1.


But you can answer if you want!

 

[ajetrumpet]

Well heck, I don't know!! You're so vague all the time...kind of like that last question that took 30 or so posts to answer...

 

[Colin@Toyota]

OOooooOOOooo
ZING!!

Besides, I thought it was a valid question... Even if it wasn't really directed at anybody in particular. I was wondering if it was simply for visual identification that might just be printed in the corner of a report, or if it might be used as a historical reference once the job/service is completed, and someone wants to change something within the Access record.

I may not know the answers to the questions, but maybe I can ask some that will clarify it for the people on this forum who DO have the answers!

 

[ajetrumpet]

yeah Ha Ha, very funny!!

I guess we'll leave this one to Garn...

 

[garnf1]

Hello Colin & Aje,
Yes its used for visual ID on prints & verbally etc...
I could do as you suggest Colin, but I would still want it to be automatically created on job entry, and used thereafter for people to lookup in tables etc. so it might as well be put straight in as the primary key. I think I've managed it now by just adding the strings on an Event on entry to the field - a simple requirement after all when the penny drops.

It really helps when you're just beginning though to chew it over with others.
Thanks for your help.