Deriving a field's value from 3 other fields

[CalibAndrew]

I'm working on a database intended to act like a search program you might use at a library, in order to organize a collection of audio/visual materials and books. The user can search for objects using a search form, which lets you search by keyword and/or specify criteria like "media", "status", etc. The results show up on a results form, where the user can print a report for one or all of the records. It's all going very well.

What I would like to do is give every object a unique "source number", which would be derived from 2 other fields and a 3-digit number the user puts in on the "Add New Object" form. It would look like this:

PPPPP ### MMM

"PPPPP" is a 3-5 letter code attached to the value of the "Project" field for that object. "Project" is a list box field that the user picks from when adding a new object to the database. Every project has a 3-5 letter code; for example, the project called "Fool's Gold" has the code "FGD".

"###" is the 3-digit number that the user types in.

"MMM" is similar to "PPPPP", and it is attached to a list box field called "Media".

What I would like to do is have the user put in all the information for a new object on a form, including "Project" and "Media". When these two are entered, the codes would show up in non-editable textboxes, like so:

(PPPPP)(---)(MMM)

(---) is editable, it's the "###" textbox where the user enters the 3-digit number. The values of PPPPP and MMM can only be changed by picking a different value in the list-box fields.

I have the 3 textboxes together like that so that the user will be able to see what the Source Number will look like.

When the user clicks the "Save" button on the form, saving the new object, I want the values of those 3 textboxes to combine in a new field, called "Source #". The user would then be able to type in the value for the Source # on the search form and get the record for that object. The Source # would also appear on the results form when a user searches for something.

My question is: how to combine these 3 values into a single field? I'm sure it's possible, but I'm pretty new to this sort of thing and I'm not sure how to go about it.

Thanks.

 

[Rolliee]

Calculated fields are bad news. YOu could use the early values as caption on a preceeding label and latter values as caption on a later label and do the same thing.

rollie@bwsys.net

 

[Rolliee]

Calculated fields are bad news. YOu could use the early values as caption on a preceeding label and latter values as caption on a latter label and do the same thing.

rollie@bwsys.net

 

[jdttek]

I have done a similar thing and have bben surprised how easy it is to simply create a new expression, joining the various fields with "&". A whole new field is created that captures the info from the others. Change a component and the expression field changes. Don't know if this addresses your need but maybe worth a try.

JDTTEK