No Data in Subform?

[MichelleBee]

Hello,

I am having great difficulties with a subform total being displayed on the
main form. I asked this same question yesterday but didn't give enough
information to help with answers.

So....I have a subform that sums a unit price as follows:

Name = ttlCashAdvances
ControlSource = sum([UnitPrice])

I tried referencing ttlCashAdvances in the ControlSource on the main form as
follows:

Name = mainCashAdvances
ControlSource = Me.sfrmCashAdvances!ttlCashAdvances

This works great as long as the subform has data. If the subform has no
data, I get a #error in mainCashAdvances.

I then thought to make mainCashAdvances an unbound control and handle it
through VBA. So....here is the code I used:

If IsNothing(Me.sfrmCashAdvances!TtlCashAdvances) Then
Me.mainCashAdvances = 0
Else
Me.mainCashAdvances = Me.sfrmCashAdvances!TtlCashAdvances
End If

When I use the above code, the sum only works on two conditions:

If I put a breakpoint in the code (which is really irritating to users)
If the rows in the subform are all visible without having to scroll (for example, if the number of detail records in subform is 4 or less and are visible without having to scroll, the sum will display, if 5 or more and scrolling is necessary, I get 0 in mainCashAdvances)

Can anyone help me to figure this one out? Is there a way to determine if
the subform control has data besides IsNothing?

I have tried about 27 workarounds both in VBA code and in ControlSource and
I am slowly becoming bald through pulling hair out in frustration. Believe
me, bald is not a good look for me. Please help!

MichelleBee

 

[perry]

Why don't you use a DSum() function as controlsource to the mainCashAdvances
textbox on yr parentform?
Have the DSum point to the ttlCashAdvances field in the corresponding table,
along
with an appropriate WHERE clause pointing to the ID field in your parent
form (I presume the master ID field of yr parent form) ??

Krgrds,
Perry

Hello,

I am having great difficulties with a subform total being displayed on the
main form. I asked this same question yesterday but didn't give enough
information to help with answers.

So....I have a subform that sums a unit price as follows:

Name = ttlCashAdvances
ControlSource = sum([UnitPrice])

I tried referencing ttlCashAdvances in the ControlSource on the main form
as
follows:

Name = mainCashAdvances
ControlSource = Me.sfrmCashAdvances!ttlCashAdvances

This works great as long as the subform has data. If the subform has no
data, I get a #error in mainCashAdvances.

I then thought to make mainCashAdvances an unbound control and handle it
through VBA. So....here is the code I used:

If IsNothing(Me.sfrmCashAdvances!TtlCashAdvances) Then
Me.mainCashAdvances = 0
Else
Me.mainCashAdvances = Me.sfrmCashAdvances!TtlCashAdvances
End If

When I use the above code, the sum only works on two conditions:

If I put a breakpoint in the code (which is really irritating to users)
If the rows in the subform are all visible without having to scroll (for
example, if the number of detail records in subform is 4 or less and are
visible without having to scroll, the sum will display, if 5 or more and
scrolling is necessary, I get 0 in mainCashAdvances)

Can anyone help me to figure this one out? Is there a way to determine if
the subform control has data besides IsNothing?

I have tried about 27 workarounds both in VBA code and in ControlSource
and
I am slowly becoming bald through pulling hair out in frustration. Believe
me, bald is not a good look for me. Please help!

MichelleBee