Reference Calculated Footer field

[RJF]

I have a form with a calculated total in the footer. This field is called
txt_Total_Units. The calculation for this total field is:
=Sum(Abs([Chk_Add] In (-1))*[SumOfchrg_units])
(If the checkbox is selected in the record, add that amount to the total.)

I need to reference this field in my code. Like this:

Units = [Forms]![frm_PVD]![frm_PVD_02].Form!txt_Total_Units

I may be wrong but I believe I can't use the field name in my code like
above, but instead need to reference the calculation itself.

I tried:

Units = Sum(Abs([Chk_Add] In (-1))*[SumOfchrg_units])

but when I hit return after typing the above I get the error: "Expected: )"
then when I compile I get "Syntax Error".

Can anyone shed some light on how the code is supposed to be written for
this. I am not very experienced with VB code.

Thank you,

 

[Marshall Barton]

I have a form with a calculated total in the footer. This field is called
txt_Total_Units. The calculation for this total field is:
=Sum(Abs([Chk_Add] In (-1))*[SumOfchrg_units])
(If the checkbox is selected in the record, add that amount to the total.)

I need to reference this field in my code. Like this:

Units = [Forms]![frm_PVD]![frm_PVD_02].Form!txt_Total_Units

I may be wrong but I believe I can't use the field name in my code like
above, but instead need to reference the calculation itself.

I tried:

Units = Sum(Abs([Chk_Add] In (-1))*[SumOfchrg_units])

but when I hit return after typing the above I get the error: "Expected: )"
then when I compile I get "Syntax Error".

You can not use Sum in VBA code. You can not use the IN
operator in VBA code either, but that's easy to get rid of
because it's completely unnecessary. You could use this in
the expression:
=Abs(Sum([Chk_Add] * [SumOfchrg_units]))

But, there is an issue when you use VBA code to get the
value of a calculated text box. Control expressions are
calculated in an asynchronous, low priority execution thread
so the calculation may not have been done when you try to
get its value.

The general solution is to not mix control calculations and
VBA code. Either do everyting with expressions or do it all
in VBA.

Since you can not use Sum in VBA, calculate th sum using
code something like:

With Me.RecordsetClone
.MoveFirst
Do Until .EOF
Units = Units + IIf(Me.Chk_Add, Me.SumOfchrg_units, 0)
,MoveNext
Loop
End With

 

[RJF]

Hi Marshall,

Thank you for such a quick response. I will try out your suggestion.

Thanks again,
--
RJF

I have a form with a calculated total in the footer. This field is called
txt_Total_Units. The calculation for this total field is:
=Sum(Abs([Chk_Add] In (-1))*[SumOfchrg_units])
(If the checkbox is selected in the record, add that amount to the total.)

I need to reference this field in my code. Like this:

Units = [Forms]![frm_PVD]![frm_PVD_02].Form!txt_Total_Units

I may be wrong but I believe I can't use the field name in my code like
above, but instead need to reference the calculation itself.

I tried:

Units = Sum(Abs([Chk_Add] In (-1))*[SumOfchrg_units])

but when I hit return after typing the above I get the error: "Expected: )"
then when I compile I get "Syntax Error".

You can not use Sum in VBA code. You can not use the IN
operator in VBA code either, but that's easy to get rid of
because it's completely unnecessary. You could use this in
the expression:
=Abs(Sum([Chk_Add] * [SumOfchrg_units]))

But, there is an issue when you use VBA code to get the
value of a calculated text box. Control expressions are
calculated in an asynchronous, low priority execution thread
so the calculation may not have been done when you try to
get its value.

The general solution is to not mix control calculations and
VBA code. Either do everyting with expressions or do it all
in VBA.

Since you can not use Sum in VBA, calculate th sum using
code something like:

With Me.RecordsetClone
.MoveFirst
Do Until .EOF
Units = Units + IIf(Me.Chk_Add, Me.SumOfchrg_units, 0)
,MoveNext
Loop
End With

 

[RJF]

Hi Marshall,

Thank you for the help. I don't quite have it working yet, but I believe
I'm getting close. I would never have gotten this far without your help
though.

Thanks again,
Rachel

--
RJF

I have a form with a calculated total in the footer. This field is called
txt_Total_Units. The calculation for this total field is:
=Sum(Abs([Chk_Add] In (-1))*[SumOfchrg_units])
(If the checkbox is selected in the record, add that amount to the total.)

I need to reference this field in my code. Like this:

Units = [Forms]![frm_PVD]![frm_PVD_02].Form!txt_Total_Units

I may be wrong but I believe I can't use the field name in my code like
above, but instead need to reference the calculation itself.

I tried:

Units = Sum(Abs([Chk_Add] In (-1))*[SumOfchrg_units])

but when I hit return after typing the above I get the error: "Expected: )"
then when I compile I get "Syntax Error".

You can not use Sum in VBA code. You can not use the IN
operator in VBA code either, but that's easy to get rid of
because it's completely unnecessary. You could use this in
the expression:
=Abs(Sum([Chk_Add] * [SumOfchrg_units]))

But, there is an issue when you use VBA code to get the
value of a calculated text box. Control expressions are
calculated in an asynchronous, low priority execution thread
so the calculation may not have been done when you try to
get its value.

The general solution is to not mix control calculations and
VBA code. Either do everyting with expressions or do it all
in VBA.

Since you can not use Sum in VBA, calculate th sum using
code something like:

With Me.RecordsetClone
.MoveFirst
Do Until .EOF
Units = Units + IIf(Me.Chk_Add, Me.SumOfchrg_units, 0)
,MoveNext
Loop
End With

 

[Marshall Barton]

Thank you for the help. I don't quite have it working yet, but I believe
I'm getting close. I would never have gotten this far without your help
though.

If you run into trouble, post back with what you have and
maybe someone will be able to help you figure out what to
do.