Update Table issue?

[Oded Dror]

Hi there,

I'm using Access 2002 and WinXP Pro.

I created a table with 4 fields looks like this

fldID PK
fldQtyA Number
fldQtyB Number
fldQtyTotal Number

Then I created a form base on this table and
in fldTotalQty I created a function: =[fldQtyA]+[QtyB] in control source

Everything works fine when I'm entering in fldQtyA 10 and QtyB 20 the
fldTotalQty show the result of 30
and so on when I'm entering a second record QtyA 5 QtyB 5 the fldTotalQty
show 10 But....

When I'm saving the records and going to actual table the fldTotalQty is
empty even its show on the form the results

my question is how to make the TotalQty field to update the table after the
data was entered.

the same problem is when you create a ComboBox base on lookup table within
another text field =[CboName].Column.(1)

its doesn't update the table only the combo box has been updated but not the
lookup textbox?

Thanks,

Oded Dror
Email: (e-mail address removed)

 

[Larry Linson]

Two comments:

(1) Storing a value that you can calculate from other values in the same
record is redundant and a violation of relational design principles.

(2) You have your choice: a Control can either be Calculated, as yours is,
or can have as its Control Source a Field in the Form's Record Source so
that the contents will be stored.

If you _must_ store the calculated value, create a text box, bound to the
Field in the Record. In the AfterUpdate of the Text Boxes containing the
factors in the Calculation, that is, the Controls displaying QtyA and QtyB,
put VBA code to set the bound text box to the value of the Text Box
containing TotalQty.

But, the best advice I can give you is this: calculate that total when you
need to display or use it, don't store redundant data.

Larry Linson
Microsoft Access MVP