Sum in Subform Not Showing Up in Main Form

[MichelleBee]

Hi,

I am having an issue with the sums in my subforms being displayed in my main
form. The strange thing is, if my subform has more than 4 detail records to
sum, the sum will not be displayed in the control on the main form - if there
are 4 or less detail records then the main form control is fine. It almost
looks like a timing issue.

Is there a way to ensure that the subforms are entirely rendered before my
main form controls are displayed?

Thanks,
MichelleBee

 

[Graham Mandeno]

Hi Michelle

My crystal ball is off at the cleaners, so I can't see what method you are
using to display the subform total on the main form.

The usual method is to have a textbox in the subform's header or footer -
for example:
Name: txtTotalPayments
ControlSource: =Sum([PaymentAmount])

You can make this textbox invisible if you like.

Then, in your main form, another textbox with this ControlSource:
=[Name of subform control].[Form]![txtTotalPayments]

Is this what you are doing? If not, try it. If so, then I'm puzzled - it
should work with hundreds of records, not just four.

 

[MichelleBee]

Hi Graham,

I'm doing something similar to that already but you sparked another idea
(re:using header/footer, etc.) so I'm going to give it a shot.

Thanks for your help,
Michelle

Hi Michelle

My crystal ball is off at the cleaners, so I can't see what method you are
using to display the subform total on the main form.

The usual method is to have a textbox in the subform's header or footer -
for example:
Name: txtTotalPayments
ControlSource: =Sum([PaymentAmount])

You can make this textbox invisible if you like.

Then, in your main form, another textbox with this ControlSource:
=[Name of subform control].[Form]![txtTotalPayments]

Is this what you are doing? If not, try it. If so, then I'm puzzled - it
should work with hundreds of records, not just four.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi,

I am having an issue with the sums in my subforms being displayed in my
main
form. The strange thing is, if my subform has more than 4 detail records
to
sum, the sum will not be displayed in the control on the main form - if
there
are 4 or less detail records then the main form control is fine. It almost
looks like a timing issue.

Is there a way to ensure that the subforms are entirely rendered before my
main form controls are displayed?

Thanks,
MichelleBee

 

[MichelleBee]

Graham Mandeno wrote:

Name: txtTotalPayments
ControlSource: =Sum([PaymentAmount]) (<==That's exactly what I had coded)

I believe I originally had code as you suggested - set the control source to
that value on the subform (i.e.=[Name of subform
control].[Form]![txtTotalPayments]). However, if the txtTotalPayments is 0 in
the subform, I get a #error in the control on the main form.

I changed the ControlSource on the main form to Unbound and added code to
check if the txtTotalPayments IsNothing:

If IsNothing(Me.sfrmCashAdvances!hdnTotalCashAdvances) Then
Me.fldTotalCashAdvances = 0
Else
Me.fldTotalCashAdvances = Me.sfrmCashAdvances!hdnTotalCashAdvances
End If

With the code above, I have the problem with the main form control only
adding up the rows that are visible in the subform!

I am puzzled...ca anyone help?

Hi Michelle

My crystal ball is off at the cleaners, so I can't see what method you are
using to display the subform total on the main form.

The usual method is to have a textbox in the subform's header or footer -
for example:
Name: txtTotalPayments
ControlSource: =Sum([PaymentAmount])

You can make this textbox invisible if you like.

Then, in your main form, another textbox with this ControlSource:
=[Name of subform control].[Form]![txtTotalPayments]

Is this what you are doing? If not, try it. If so, then I'm puzzled - it
should work with hundreds of records, not just four.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi,

I am having an issue with the sums in my subforms being displayed in my
main
form. The strange thing is, if my subform has more than 4 detail records
to
sum, the sum will not be displayed in the control on the main form - if
there
are 4 or less detail records then the main form control is fine. It almost
looks like a timing issue.

Is there a way to ensure that the subforms are entirely rendered before my
main form controls are displayed?

Thanks,
MichelleBee

 

[Graham Mandeno]

Hi Michelle

IsNothing?? Did you mean IsNull? IsNothing does not exist.

You can say:
If SomeVariable Is Nothing Then ...
but that is meaningful only for object variables.

Are you sure that your textbox hdnTotalCashAdvances is in the header or
footer of the subform, not in the detail section?

When you say "the rows that are visible in the subform" do you mean all the
rows currently filtered in the subform, including the ones you have to
scroll to see, or do you mean only the rows in the current scroll window?

What is the ControlSource of hdnTotalCashAdvances? It should be:
=Sum([FieldName])
not
=Sum([ControlName])
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham Mandeno wrote:

Name: txtTotalPayments
ControlSource: =Sum([PaymentAmount]) (<==That's exactly what I had
coded)

I believe I originally had code as you suggested - set the control source
to
that value on the subform (i.e.=[Name of subform
control].[Form]![txtTotalPayments]). However, if the txtTotalPayments is 0
in
the subform, I get a #error in the control on the main form.

I changed the ControlSource on the main form to Unbound and added code to
check if the txtTotalPayments IsNothing:

If IsNothing(Me.sfrmCashAdvances!hdnTotalCashAdvances) Then
Me.fldTotalCashAdvances = 0
Else
Me.fldTotalCashAdvances = Me.sfrmCashAdvances!hdnTotalCashAdvances
End If

With the code above, I have the problem with the main form control only
adding up the rows that are visible in the subform!

I am puzzled...ca anyone help?

Hi Michelle

My crystal ball is off at the cleaners, so I can't see what method you
are
using to display the subform total on the main form.

The usual method is to have a textbox in the subform's header or footer -
for example:
Name: txtTotalPayments
ControlSource: =Sum([PaymentAmount])

You can make this textbox invisible if you like.

Then, in your main form, another textbox with this ControlSource:
=[Name of subform control].[Form]![txtTotalPayments]

Is this what you are doing? If not, try it. If so, then I'm puzzled -
it
should work with hundreds of records, not just four.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi,

I am having an issue with the sums in my subforms being displayed in my
main
form. The strange thing is, if my subform has more than 4 detail
records
to
sum, the sum will not be displayed in the control on the main form - if
there
are 4 or less detail records then the main form control is fine. It
almost
looks like a timing issue.

Is there a way to ensure that the subforms are entirely rendered before
my
main form controls are displayed?

Thanks,
MichelleBee