sum in form footer

[dean]

Hi guys!

I have this problem and I cant figure it out how many times I have tried it myself.

I have this subform, and it has 4 textbox: three that is bound and the one that is unbound form.

when I do calculations in the bound textbox, i can successfully arrive at the sum of textbox3 but when I sum the fourth unbound textbox, it give an error inside the textbox.

Does it mean I cannot make a summation of an UNBOUND textbox?

 

[MStef]

Look at "DemoSumUnbA2000.mdb" (attachment, zip).
Open Form1 and see.

 

[dean]

thanks for the reply MStef, but in the sample you gave me the two textbox in your detail section are all bound which makes it easy to make calculations in the textbox at your footer.

However, my situation involves an UNBOUND textbox in the detail section which gives an error in my calculations in the textbox in the footer section when I sum the unbound textbox in the detail section.

Do you have other sample or solution for that? TIA

 

[pbaldy]

You can't sum a control (textbox), you can only sum a field in the data. You can sum the calculation itself, so if you textbox contains:

=Price * Quantity

you could have

=Sum(Price * Quantity)

 

[dean]

thanks pbaldy. yes I know the data in the field are to sum and not the controls. but I cant still sum-up data in the unbound field from the detail section.

 

[Poppa Smurf]

You will need something like this
=[Forms]![Name of Main Form]![Name of your subform]![name of textbox

Suppose form1 is the name of your main form, sfrm_1 is the name of your subform qty_1, qty_2, qty_3, are the names of your text boxes not your field names. qty_4 is the name of your UNBOUND text box then the control source of your total field on your form footer would be
=[Forms]![form1]![sfrm_1]![qty_1]+[Forms]![form1]![sfrm_1]![qty_2]+[Forms]![form1]![sfrm_1]![qty_3]+[Forms]![form1]![sfrm_1]![qty_4]

Note: The value entered by the user in the UNBOUND textbox will be the same for each record regardless of the default view e.g. Single form Datasheet etc. But, if the unbound text box is a calculated value e.g. (qty_1*3) then this value will be different from the other records.

 

[pbaldy]

You say the textbox is unbound; what is its control source?

 

[dean]

the subform has the recordsource to a table

the three bounded textboxes in the detail section has a controlsource to one of the fields in the recordsource (table) and I have deliberately made one unbound textbox with no controlsource just for the purpose of making a calculations inside the unbound textbox (=a+b+c)

where textbox1 to textbox 3 is named a to c respectively. and textbox4 is named D.

so in the form footer the textbox5 has the controlsource =sum(D). but unfortunately, there is an error summing up D.

 

[pbaldy]

As I said, that won't work:

http://support.microsoft.com/kb/208850

Did you try what I suggested?

=Sum(a+b+c)

where a, b and c are your field names.

 

[dean]

In the detail section of the subform is bound textbox A, B, C and unbound textbox D

It is in this kind of format what I have done:


FORM HEADER
DETAIL SECTION
---------------------------------------------------------------
A B C D
[textboxA] [textboxB] [textboxC] [=textboxA+textboxB+textboxC]
---------------------------------------------------------------
FORM FOOTER [=sum(d)]




It is not this way:

FORM HEADER
DETAIL SECTION
---------------------------------------------------------------
A B C D
[textboxA] [textboxB] [textboxC] [=sum(textboxA+textboxB+textboxC)]
---------------------------------------------------------------
FORM FOOTER [=sum(d)]

 

[pbaldy]

I can't seem to get the point across. Can you post the db?

 

[Poppa Smurf]

In your sub form you have these textboxes [textboxA] [textboxB] [textboxC]. Is the record source for this form a table or query?

If it is a table, then change it to a query with your three textboxes, now add another field in query with the following textboxD:[textboxA]+ [textboxB]+[textboxC]. This will give you a total for the three textboxes.

On your subform change [textboxA] [textboxB] [textboxC] [=textboxA+textboxB+textboxC] to [textboxA] [textboxB] [textboxC] [textboxD]. textboxD is now D

Then in the form footer of your subform it should be =sum([textboxD]) not [=sum(d)] as D is not a field in your table.

If you need to show this total on your main form then use the format =[Forms]![Name of Main Form]![Name of your subform]![name of textbox] see my earlier post.

 

[dean]

i have thought of that poppa smurf but it involves several calculations and the calculations are done in vba code. that is I cant make it on query recordsource.

 

[Poppa Smurf]

What do you mean by

it involves several calculations and the calculations are done in vba code

As Paul suggested please post a copy of your database so that we can provide some assistance.