update a form field by multiplying two other fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to update the "TotalCost" field by multipllying "ItemCost" by "Qty".
I would like to do this in a form. I entered an expression in the
ControlSource and it displays the correct number in the form but does not
write it into the table.
 
Joe,

You don't need the field in your table. Calculate it whenever you need it
either in a calculated control on a form, or in a query. Although
counterintuitive to new users, the reasons are:

- It's faster to calculate the value on-the-fly than look it up from the disk.
- It takes VBA code to save a calculation to a field, since you can set a
form control's ControlSource to a calculation expression OR to a field name,
but not both.
- If the factors involved in the calculation are changed outside the context
of your form, your stored calculation will be WRONG.

Using a relational database is a paradigm shift from a "flat file" approach
such as might be implemented in a spreadsheet, where you'd have a column for
every field you need in one place. Relational databases break up the data
into multiple tables, with a minimum of duplication--customer info is stored
ONCE in a customer table, order header information (custid, orderdate,
salesrepid, etc.) is stored ONCE in an Orders table. In related tables, all
you need to store is the primary key of another table (a foreign key in the
related table) to "Access" its fields by linking the tables in a query.

Hope that helps.
Sprinks
 
Don't write it into the field. You are seeing the result of the
calculation. In most cases there is no need to store it. If you are
storing the item cost and the quantity, that is all you need.
Note that a control source can either be an expression (such as a
calculation), or it can be a field in the underlying table, but not both.
An alternative to writing the expression in the text box is to use a query
as the form's record source, and to perform the calculation in the query.
To do this, at the top of an empty column in query design view:
TotalCost: [ItemCost] * [Qty]
Then you can bind a text box to TotalCost.
 
Thanx, I am creating a relational data base for a clinic in Haiti. I am a
retired Hardware eng/pastor doing mission work there. Your help is greatly
appreciated. I think I can handle it now.
 
Back
Top