Help: How to sum in the Form View (1 Viewer)

[phatus]

good day i created a database with the following information:
tblrecords
field1 Produnt
field2 Amount
field3 Discount
field4 Total

and i make a query of my table then create a from base on the query...

my problem is in the total field in the FORM is not working...
the total should automatically update when you put any amount in the Amount and in the Discount field..
for example you put 5 in amount and 2 in discount automatically the total will add a value of 3...

anyone can help me in this problem....

thank you...

 

[BobMcClellan]

did not download the file but try this....
in both textboxes put something like this in the OnChange Event.
tbTotal = tbAmount - tbDiscount

 

[skeeter23]

In the "After Update" event of both the amount and discount fields add this...

Me.Total.Value = Me.Amount - Me.Discount
DoCmd.RepaintObject

 

[phatus]

i tried your suggestion but it dont work... in my query i put sum in the total in Total fiel... when i put your code in the after update the filed amount and discount in the form is not editable and cant create new record.... any suggestion or pls try to check my sample dbase..

thank you very much....

 

[phatus]

i got it but my problem is it dont record in the query only in the form..

i put in the total source:

=[Amount]-[Discount]

it means only in form it dont update the total in the query... any ideas how to update it in the query? im going to change my sample database... you can see the total in form but in query theres no entry

thanks for helping...

 

[skeeter23]

Fixed.

The main problem though is that you did not have a control source set to the table. Since the control source was = [Amount] - [Discount] it was only displaying the result and not actually doing anything with it.

I switched the source to Total and added the VBA I suggested in the after update events.

 

[BobMcClellan]

Phatus,
as Skeeter posted.. The "After Update" event should handle this for you.
(I was moving to fast and mistakenly put OnCurrent )

Anyway... if your underlying recordset is a query, and from what you are saying, the textbox control on the form that displays the TOTAL is a calculated column from the query, then after updating either the Amount or the Discount,...
the AfterUpdate event should fire a me.Form.Requery.

so ...
the Amount & the Discount textboxes "After Update" events should simply have
Me.Form.Requery

hth,
..bob

 

[phatus]

its working now... thank you very much it help a lot

 

[Galaxiom]

Storing that Total field in a table is a normalization anomaly since it is a calculated value.

 

[skeeter23]

Storing that Total field in a table is a normalization anomaly since it is a calculated value.

Yes that is a true statement.

That is something to keep in mind phatus. That "total" really should not be stored but rather queried whenever you need it.

That being said really the way you had it setup in the first place was fine. However that wasn't your question though so I answered it the way I did. Perhaps I should have thought a bit more first as well

 

[phatus]

Originally Posted by Galaxiom
Storing that Total field in a table is a normalization anomaly since it is a calculated value.

ok what you mean by normalizationa anomaly? i locked the field of TOTAL in my Form its not editable...

skeeter23
That "total" really should not be stored but rather queried whenever you need it.

now it is saved in my query not only in my form... is this what you mean sir?

i used this method in my School Profile by getting the total of every enrolled students from elementary and secondary...

 

[skeeter23]

What we're referring to is that when you know that "total" is simply a calculation of other feilds it should not be stored, only displayed.

You know that the total is always derived from the equation of amount-discount so total is redundant. Total should not be a field in your table.

When the time comes that you need the sum totals you create a query that gives you the result purely from the equations like Sum([Amount]-[Discount]).

Does that help?

 

[phatus]

Total should not be a field in your table.

yes i understand but i thought when you created a query it is based on your table, all the fields in the query must also have a fields in the table.. if i will remove the total field in the table it will affect my total field in my query, am i right? i put total in my table for the reason that the total must be shown in my report

im sorry if i have many question im just starting learning Access and VBA... and this is my first project... i only learn on how to make my database thru internet and in this forum...

p.s if its ok sir can you modify my sample database, so that i can see what you mean...

 

[skeeter23]

Simply go back to the one where you had added =[Amount]-[Discount] in the form for the total box and then delete the total feild from the table and query.

 

[phatus]

Simply go back to the one where you had added =[Amount]-[Discount] in the form for the total box and then delete the total feild from the table and query.

sir i tried this, i deleted the total field in my table and query and in the form i change the source of total to =[amount]-[discount]... but my problem is in report view the total is gone...

in my report view consist of 4 column, col1: Product col2: Amount col3: discount and lastly col4: Total... thats when i have the total field in my Table and Query.

but now the total in my report is gone but i have a total in my form...

can you suggest on how can i make the Total appear in the report?

thank you....

 

[phatus]

i tried this correct me if im wrong...

i add text box in my report thru design mode.. and name it as total... then in the source i put

=[amount]-[discount]

then when i select the report view tadan... i have a total in my report now...

is it correct?

 

[skeeter23]

Yup that's it