Sum total for an unbound control

[graeme34 via AccessMonster.com]

Hi

Could anybody please help me, I have a Sales order entry form along with a
child sales detail form. I have now decided to try and have an unbound order
value control on the master form that is to be based on the sum of the total
line value (calculated control) from the child form. I have quite a bit of
code behind the form so I dont want to be changing the control source for the
master form (currently tblSales Order). Is there a way I can do it without
invoking a query??
Quite new to this, sorry if it is a basic question...

 

[strive4peace]

you can put this equation in your unbound control:

controlSource --> iif(subreport_controlname.report.hasdata,
subreport_controlname.report.controlname,0)

you can sum values from unbound controls like this:

create a variable in the code behind the report that will be
global -- so create it BEFORE any procedures... at the TOP
of the code module (after Option statements)

------------------------
dim gTotal as Currency 'or whatever data type
------------------------

then, in the Group header (and/or Report Header if you are
using Detail section), onPrint

------------------------
'initialize variable
gTotal =0
------------------------

then in the onPrint event of each Group (or Detail --
wherever control is) section...

------------------------
gTotal = gTotal + nz(me.controlname_with_value_to_add_up)

'NOTE
'controlname_with_value_to_add_up can be a calculated
control that gets its value from a subreport
------------------------

then, finally, on the onPrint event of the Report (and/or
Group) footer

------------------------
me.controlname_of_unbound_textbox_in_report_footer = gTotal
------------------------


Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com

 

[Marshall Barton]

Could anybody please help me, I have a Sales order entry form along with a
child sales detail form. I have now decided to try and have an unbound order
value control on the master form that is to be based on the sum of the total
line value (calculated control) from the child form. I have quite a bit of
code behind the form so I dont want to be changing the control source for the
master form (currently tblSales Order). Is there a way I can do it without
invoking a query??

Not so basic. The child form must calculate its own total.
Using the Sum function is by far the easiest way to do this,
but Sum only operates on fields in the subform's record
source table/query. This means you can sum the calculated
text box's value.

You didn't say what kind of calculation you're doing, but if
it's just an expression along the lines of =Qty * Price
then the subform's footer section can use a text box with
the expression =Sum(Qty * Price)

 

[graeme34 via AccessMonster.com]

Hi,

Thanks for your interest.
I attempted to try the second option as it looked a little less complex...or
at least I thought!
I have created a total order value text box in the subforms footer, but when
I open the form in form view, the subform footer text box (total order value)
is staying blank and the main form text box which I have tried to set the
control source to =[Forms]![subfrmSales]![TotalOrderValue] where
TotalOrderValue is the textbox on the subform, is showing...... #Name?

Any ideas where I'm going wrong??

The control source for the subform TotalOrderValue is =Sum([QuantityOrdered]*
[Price])

 

[Marshall Barton]

The main form text box's expression should be:
=[subfrmSales].Form![TotalOrderValue]
--
Marsh
MVP [MS Access]

I attempted to try the second option as it looked a little less complex...or
at least I thought!
I have created a total order value text box in the subforms footer, but when
I open the form in form view, the subform footer text box (total order value)
is staying blank and the main form text box which I have tried to set the
control source to =[Forms]![subfrmSales]![TotalOrderValue] where
TotalOrderValue is the textbox on the subform, is showing...... #Name?

The control source for the subform TotalOrderValue is =Sum([QuantityOrdered]*
[Price])

Could anybody please help me, I have a Sales order entry form along with a
child sales detail form. I have now decided to try and have an unbound order
value control on the master form that is to be based on the sum of the total
line value (calculated control) from the child form. I have quite a bit of
code behind the form so I dont want to be changing the control source for the
master form (currently tblSales Order). Is there a way I can do it without
invoking a query??
Quite new to this, sorry if it is a basic question...

 

[graeme34 via AccessMonster.com]

Thank you Marshall,
That seems to have done the trick.
Many Thanks
Graeme.

The main form text box's expression should be:
=[subfrmSales].Form![TotalOrderValue]

I attempted to try the second option as it looked a little less complex...or
at least I thought!

[quoted text clipped - 15 lines]