No Data in Subform?   

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

date: Thu, 17 Jul 2008 14:35:01 -0700   author:   MichelleBee

Re: No Data in Subform?   

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

"MichelleBee"  schreef in bericht 
news:4782A277-0931-4BD1-834F-7FD6D7C7E096@microsoft.com...
> 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
>
>
>
>

date: Tue, 22 Jul 2008 23:09:43 +0200   author:   perry