Totals in a form

  • Thread starter Thread starter Nearly LOCO
  • Start date Start date
N

Nearly LOCO

I need help, this is driving me LOCO.
I have a form with three values in three boxes.
Quantity
Price
and
Total
The Total box is =Quantity * Price
This is the problem: I can see the total amount (Q*P)in
the Total Box in the form.
BUT WHEN I LOOK AT THE TABLE I ONLY CAN SEE THE VALUE OF
QUANTITY AND PRICE.
THE TOTAL COLUMN IS = $0.00.
Can any good Samaritan let me know why and how to solve
this problem?
I need to have the total in the table for future
calculations.
Thankyou in advance.
Nearly LOCO
 
Try and think of it like this....

If you can calculate the Total from Quantity and Price
then why do you need Total in the table at all?

Generally you do not include fields in a table that can be
derived from other fields.

If you want the Total for future reference, then create a
query using the table and include a calculated field to
work out the total.
 
I need help, this is driving me LOCO.
I have a form with three values in three boxes.
Quantity
Price
and
Total
The Total box is =Quantity * Price
Click to expand...

Ok, so it's calculating the total on the Form.
This is the problem: I can see the total amount (Q*P)in
the Total Box in the form.
BUT WHEN I LOOK AT THE TABLE I ONLY CAN SEE THE VALUE OF
QUANTITY AND PRICE.
THE TOTAL COLUMN IS = $0.00.
Click to expand...

Exactly. A textbox can have ONLY ONE control source property. This
might be a fieldname; it might instead be an expression. It can't be
both! Just having the name of the textbox equal to "Total" is
irrelevant (you could name it Veeblefrester, and Access wouldn't treat
it any differently).
Can any good Samaritan let me know why and how to solve
this problem?
I need to have the total in the table for future
calculations.
Click to expand...

Well... actually you probably don't.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

If the Price may change over time, and you want to store the Total *as
of the moment of the transaction*, then yes, you may indeed want to
violate this restriction. To do so, name your textbox something
*other* than Total (so Access will know which is the textbox and which
is the field) - let's say txtTotal; and put the following VBA code in
the Form's BeforeUpdate event (click ... and choose the Code Builder):

Private Sub Form_BeforeUpdate(Cancel as Integer)
Me!Total = Me!txtTotal
End Sub

to copy the calculated value into the table field.
 
Back
Top