Posting the sum of entries in a form to a table

[DC27]

I have created a table to store monthly forecasts and a form for the user to enter the details

Details entered like this where the first line is the column header

Line Item, month1, month2, month3, Total
PC, £5000, £1500
Desks, £3000, £2000
Stationery, £50, £50, £50

As the user will be changing this data regularly, i've set up a basic form to make this a little easier.

What i'd like to be able to do is show the total in the form AND then updated back in the table

 

[PaulJR]

Providing the fields in the table are set to currency.

You can add a textbox on your form and type in the 'default value' (in properties > data) the following

=[month1]+[month2]+[month3]

 

[DC27]

ah, but i'm not using a query. I'm trying to store the data in a table by using a form.

 

[PaulJR]

Yes I just edited what I said to correct that.

 

[PaulJR]

Actually, I just tried something like this on my own database. Add the total textbox to your form as normal. Then add a command button that has the following code:

total=[month1]+[month2]+[month3]

This will update the total value in the textbox. When you save the record your table will be updated. You could combine the line of code above with a 'save record' command button.

 

[missinglinq]

Storing this type of calculated value is a very poor idea. John Vinson, MVP, explained why in this post:

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.