Use a text from a field as the default value of another field

[BlackBooks]

Hi there,

I promise, I'm not making redundant extra information in my db! Here's the background and objective:

I work for a company that makes things like shampoos, bodywash, conditioners etc.

Every project I work on has a unique Project ID which is a four digit number with the format of "PPPP".

For every project, I might try out anywhere from 1 to 100 different formulations with different ingredients and ratios of things in them. I like to assign each formulation I try out a unique Formulation ID as well, and this is based on the project number with a format like this: "PPPP-FF".

Now say I find a formulation I like and I want to make several batches of it to do some tests on. I like to assign each batch a unique Batch ID which is based on the Formulation ID, and has a format like this "PPPP-FF-BB".

I keep separate tables for Projects, Formulations and Batches.



I hope that's clear and you now have the context for why I would like to be able to have one field use the value of another field to as the default value. Specifically, what I would like to do is:

1) In my "add new formulation form" have the first part of the Formulation ID automatically populate with the "PPPP-" part of the Formulation ID after I select a project from a lookup list of projects. Then all I have to do is type in the "FF" part myself.

2) In my "add new batch form", I want to be able to select a project from a lookup list, and then be able to choose a Formulation ID based on a list of the formulations associated with that project (that's a whole other question in itself I know!).

3) In my "add new batch form" have the first part of the Batch ID automatically populate with the "PPPP-FF-" part of the Formulation ID after I select a Formulation ID from the lookup list. So that all I have to do is type in the "BB" part myself.

[Minty]

You should simply store the parts of those numbers as Foreign keys in the underlying table. You can then display them easily in your forms and reports.
Don't try and concatenate them into one string as you wouldn't be able to link them automatically.

So In your batch table you should have FK_ProjectID
And in your formulation table you should have FK_BatchID

You shouldn't store the project ID in the formulations table as it can and should always refer back to a batch which in turn refers to a Project.