Sum Field in Query

[K]

Good day.

I have a query that does calculations in a subform. I would like to also
have the total displayed as well. I have tried Dsum such as CostPCSum:
DSum("CostPC2","QryDataSub") It does sum up the total of CostPC2 however it
does not automatically change if the CosPC2 changes. Is there a way to do
this? Any help is appreciated without having to get into refreshing events on
fields?

Thanks for your time in advance. Kyle.

 

[Michel Walsh]

If the formula is set as control source for a control, use

Me.Recalc

in the after update event of the CosPC2 control.



Vanderghast, Access MVP

 

[K]

Thanks for the reply however it does not work. I am using the Dsum Function
in the underlying query of the subform. The field the does the sum of the
cost (totallying it) is called CostPCSum

 

[K]

I am trying to avoid using an event procedure because there are numoruos
fields that I would have to place the same event on as well becuase a change
will effect the grand total.

 

[Michel Walsh]

If you don't use recalc, you will need to requery,

Me.Requery

but then, you loose the bookmarks and the record displayed is now the first
one (not necessary the one that was displayed when you requeried). So a
Me.Recalc is probably the easiest way to go.


Unlike Excel where you can enabled the recalculation to be done
automatically (the default setting in Excel) or not, in Access, there is no
automatic recalculation and if it is required, it must be explicitly asked
for, with Me.Recalc, or, manually, with [F9] when the form has the focus.



Vanderghast, Access MVP

 

[K]

Okay, but it doesnt work? (Me.Recalc.) The CostSumPC is still the same unless
I close the form and re-open it again. I have the me.Recalc in the
Afterupdate event of the Subform.....Any other Ideas??

If you don't use recalc, you will need to requery,

Me.Requery

but then, you loose the bookmarks and the record displayed is now the first
one (not necessary the one that was displayed when you requeried). So a
Me.Recalc is probably the easiest way to go.


Unlike Excel where you can enabled the recalculation to be done
automatically (the default setting in Excel) or not, in Access, there is no
automatic recalculation and if it is required, it must be explicitly asked
for, with Me.Recalc, or, manually, with [F9] when the form has the focus.



Vanderghast, Access MVP

I am trying to avoid using an event procedure because there are numoruos
fields that I would have to place the same event on as well becuase a
change
will effect the grand total.

 

[Michel Walsh]

Recalc, or [F9], redo the computed expressions in the form but does NOT
touch at the recordset So you have to make a control source

= DSum( ... )

rather than doing it in the recordset.

The only way to redo the computations done in the recordset is with a
Requery (Me.Requery) or, as you said, closing then re-opening the recordset
(or the form).


Vanderghast, Access MVP

Okay, but it doesnt work? (Me.Recalc.) The CostSumPC is still the same
unless
I close the form and re-open it again. I have the me.Recalc in the
Afterupdate event of the Subform.....Any other Ideas??

If you don't use recalc, you will need to requery,

Me.Requery

but then, you loose the bookmarks and the record displayed is now the
first
one (not necessary the one that was displayed when you requeried). So a
Me.Recalc is probably the easiest way to go.


Unlike Excel where you can enabled the recalculation to be done
automatically (the default setting in Excel) or not, in Access, there is
no
automatic recalculation and if it is required, it must be explicitly
asked
for, with Me.Recalc, or, manually, with [F9] when the form has the focus.



Vanderghast, Access MVP

I am trying to avoid using an event procedure because there are numoruos
fields that I would have to place the same event on as well becuase a
change
will effect the grand total.

:

Thanks for the reply however it does not work. I am using the Dsum
Function
in the underlying query of the subform. The field the does the sum of
the
cost (totallying it) is called CostPCSum

:

If the formula is set as control source for a control, use

Me.Recalc

in the after update event of the CosPC2 control.



Vanderghast, Access MVP


Good day.

I have a query that does calculations in a subform. I would like
to
also
have the total displayed as well. I have tried Dsum such as
CostPCSum:
DSum("CostPC2","QryDataSub") It does sum up the total of CostPC2
however
it
does not automatically change if the CosPC2 changes. Is there a
way
to do
this? Any help is appreciated without having to get into
refreshing
events
on
fields?

Thanks for your time in advance. Kyle.