set field format property based on value of another field

[danwand]

i have a table called ElectricSpecs, within this table (amongst other fields) are fields "Product Type" and "size",

I have another table called LookupProductType, which has two fields: "Product ID" and "ProductName"
----------------------------------------------------------
Field "Product Type" from Table "ElectricSpecs"
Datatype = Number.

This field is displayed in the datasheet view as a combo box, which displays a product type name but holds a numeric value in the ElectricSpecs Table... retrieved by defining a lookup field property in design view.

Field "Size" from Table "ElectricSpecs"
Datatype = Number.
This field will be used in the application as a search field to find an electricspec record that meets user defined criteria for a particular size range.
i.e User wants to find all ElectricSpecs that have a size range between 1 and 5.

----------------------------------------------------------

i need to add a format property for the "Size" field so that when displayed a measure of size is displayed for that field. This measure should be based on the "Product Type" field in the table "ElectricSpecs".

i.e. There are 4 different product types each of which has a different measure of size.
1. Product - Tranformer. Size Measure - KVA.
2. Product - Power Supply. Size Measure - VAdc.
3. Product - Reactor. Size Measure - VAR.
4. Product - Choke. Size Measure I2L.

So when a user enters a number in the "size" the number is displayed along with a size measure...
i.e. user enter 0.12 in size field, this then wold be displayed as "0.12KVA" or "0.12VAR" dependant on the choice of product type.

How do i do this?

----------------------------------------------------------

So to summise all records in table "ElectricSpecs" are of a specified product type, each record has a size field whose format property will depend on the type of product choosen for that record. So i need to add this format property for the size field dependant on the product type.

Any help would be greatly appreciated.

 

[GingerR]

the data entered into the table is just that--data.
usually you'd only worry about the size measure for showing on reports or forms or queries or something. tho it sounds dumb in reality, say that some day the size measure for transformers changes to MVA (think outside the box here--pretend we're talking about some other kind of data). what you would do in a properly-designed db is hold that measure in one table in one place, then if it changes, you only change it once. then whereever it's displayed on reports or charts or wherever, it's just automatically changed. doing it your way, you might have 2000 records in the table that need to be changed. not good.

instead, you can have a table that lists the four products, and a field with their size measure. then just reference this table in reports, charts, etc


is there some major reason you need to have the size right in the table like that?

what i mean is that the users should be entering data into a form. once they choose the product (Transformer, etc) then a text box can just appear to the right of the SIZE text box which shows the measure. it's just SHOWING it, not inputting it in the table again.

Transformer [size text box] KVA

later in reports and such, you can put

=Size & " " & measure

which will show your

12 KVA

know what i mean?

so it's just SHOWING that measure, not storing it over and over in your table.

 

[danwand]

Thanks for the advice, i was getting caught up in unnessecary recordset manipulations, i took your suggestions into account and worked around the problem by including a measure field in the products table and conditionally set its value on a after-udate event on the product type field in a form.
Thanks again.