How do i generate a Grandtotal in a form??? 1

[guytheripper]

I need to generate a grand total in a form as well as a change given and a tendered amount.
Do i need to have these 3 fields in the tables from which they are coming from or are they added in the form individually??
If you could give me any information on these subject it will be greatly appreciated.

 

[rafe]

Guy,

you can put a textBox in your form's footer section with the controlSource set to:

=Sum([myDetailAmount])

the only catch here is that you can't sum a calculated control/field... that is if your detail lines have amount set to someting like:

= [Quantity] * [Rate]

then the total textBox's control source will need to be set to

= Sum([Quantity] * [Rate])

actually redoing the line calculation inside the Sum()

let me know if you need more help

 

[jgoodman00]

It depends on what you want to store really. For optimum efficiency, all the fields would be calculated on the fly. However, I would have thought that the grand total is something you would want to keep a record of. Assuming the amount tendered & change given are not of importance to record keeping the following should help: (these need to be entered in the control source section of the relevant controls)

Grand Total: =[controlname1] + [controlname2]+[...]
Amount Tendered = (Direct user input)
Change Given = [ctlAmountTendered]-[ctlGrandTotal]

You then need to run some sql in order to append the grand total into it's source table:

UPDATE TableName SET TableName.FieldName = [Forms]![frmName]![ControlName]
WHERE (((TableName.FieldName)=[Forms]![frmName]![Fieldname]));

In the where part of the statement, the fieldname corresponds to the primary key normally.

This sql can be run in a number of places, but it is probably best to place it in the form_current events, because it will then run on every record switch...


Hope this helps
James Goodman
j.goodman00@btinternet.com