Sum() with an empty subform

[hokiewalrus]

I have a form that opens with 2 empty subforms (items and payments) that later get filled in. Each subform keeps a running total of the items they contain, then the parent form holds the grand total of both.

No matter what I do, I can't get the subform totals to show up as 0 when the subforms are empty.

I thought this would work:

=Nz(Sum([Total]),0)

But no dice. I've also tried variations of IIF and HASDATA but I can't get the subform total field to be anything but blank when there is no data in the subforms.

I think I may actually be losing hair over this.

 

[John Big Booty]

You can not sum fields that are calculated on a continuous form. What you need to do is to use a query to populate your sub forms and do the calculation with in the query, you will then be able to get the sum of that filed.

 

[pbaldy]

I put a total textbox in the subform and then use this to account for empty:

http://www.mvps.org/access/forms/frm0022.htm

 

[boblarson]

You can not sum fields that are calculated on a continuous form.

That's not true, sorry.

But, the problem SHOULD be where the NZ is being applied.

=Sum(Nz([Total],0))

 

[John Big Booty]

Sorry to doubt you Bob, but here's a form that says otherwise.

Perhaps I'm missing something?

 

[hokiewalrus]

I put a total textbox in the subform and then use this to account for empty:

http://www.mvps.org/access/forms/frm0022.htm

I tried using that function with IsNumeric and an IIF statement, but to no avail.

I even made an unbound text box =Sum([Total]) and then an IsNull test on that box to give me the 0, but that also didn't work.

Here is a picture of the form, as you can see both Total Items and Total Payments are blank, leading to my #Error

 

[missinglinq]

You can not sum fields that are calculated...

Well, yes and no, depending on how you look at it! I think maybe we're talking at cross purposes here.

If Total is a calculated control, say

Total = UnitPrice * NumberOfUnits

as John said, you can't use

=Sum([Total])

But you don't need to use a query. What you have to do is to Sum the Expression used for the calculation, i.e.

= Sum([UnitPrice] * [NumberOfUnits])

 

[John Big Booty]

Thanks for the clarification missinglinq.

 

[pbaldy]

Can you post a sample db? I've used that little function many times.

 

[rapsr59]

Hi,

This works.

In the current event of the sub form(s) place the following code.

Private Sub Form_Current()
    If Me.RecordsetClone.RecordCount = 0 Then
 
     [COLOR=green]'txtSum is the control on the footer of the sub form[/COLOR]
[COLOR=green]   'that sums the individual amount(s) entered into [/COLOR]
[COLOR=green]   'the datasheet sub form controls that are to be summed up[/COLOR]
 
[COLOR=green]   'Remove the control source from the sum control[/COLOR]
        Me.txtSum.ControlSource = ""
     [COLOR=green]'Then set the value of txtsum to 0 so the parent[/COLOR]
[COLOR=green]   'form can add zero to it's Grand Total[/COLOR]
        Me.txtSum = 0
 
    Else
     [COLOR=green]'Now that we have records change [/COLOR][COLOR=green]txtSum[/COLOR]
[COLOR=green]   'control source so the values will be summed up[/COLOR]        
        Me.txtSum.ControlSource = "=Sum([Amount])"
    End If
End Sub

I just tried this and it works. The text control on the Main Form is set to:
=[Items].SubForm!txtSum + [Payment].SubFrom!txtSumPayments (or what ever names you assigned to your controls)

Hope this is what you are looking for.


Richard

PS: Sorry, but I don't know the name of your controls so I build my own and use the above

 

[boblarson]

Thanks for the clarification missinglinq.

I just got home and saw this. Yes, you can't refer to a text box, you need to refer to the field (and if a calculation you need to recreate the calculation in the sum).

So, for your example:

=Sum([h]*[d]*[w])

 

[John Big Booty]

Thanks Bob.

 

[hokiewalrus]

Hi,

This works.

In the current event of the sub form(s) place the following code.

Private Sub Form_Current()
    If Me.RecordsetClone.RecordCount = 0 Then
 
     [COLOR=green]'txtSum is the control on the footer of the sub form[/COLOR]
[COLOR=green]   'that sums the individual amount(s) entered into [/COLOR]
[COLOR=green]   'the datasheet sub form controls that are to be summed up[/COLOR]
 
[COLOR=green]   'Remove the control source from the sum control[/COLOR]
        Me.txtSum.ControlSource = ""
     [COLOR=green]'Then set the value of txtsum to 0 so the parent[/COLOR]
[COLOR=green]   'form can add zero to it's Grand Total[/COLOR]
        Me.txtSum = 0
 
    Else
     [COLOR=green]'Now that we have records change [/COLOR][COLOR=green]txtSum[/COLOR]
[COLOR=green]   'control source so the values will be summed up[/COLOR]        
        Me.txtSum.ControlSource = "=Sum([Amount])"
    End If
End Sub

I just tried this and it works. The text control on the Main Form is set to:
=[Items].SubForm!txtSum + [Payment].SubFrom!txtSumPayments (or what ever names you assigned to your controls)

Hope this is what you are looking for.


Richard

PS: Sorry, but I don't know the name of your controls so I build my own and use the above

This basically works, although for some crazy reason the 0 in the first form doesn't actually show up, although the Grand Total now does the calculations correctly.

I've attached a screenshot and a copy of the DB, thanks for everyone's help so far, I feel like the answer is just around the next corner!

 

[rapsr59]

You can use the same method on the Total Items subform that I previously posted on the Total Payments sub form:

Private Sub Form_Current()
 
    'txtCount is the control on the footer of the sub form
    'that counts the individual items(s) entered into
    'the datasheet sub form controls that are to be counted
 
    'Change txtCount to the name of the control on the
    'footer of the sub form that counts the quantity of
    'items and change...
    '[QuantiyItems] field name to the name of the field
    'that contains the quantity of items
 
    If Me.RecordsetClone.RecordCount = 0 Then
        'Remove the control source from the count control
        Me.txtCount.ControlSource = ""
        'Then set the value of txtCount to 0 so a 0 displays
        Me.txtCount = 0
 
    Else
        'Now that we have records change txtCount
        'control source to =Count([QuantityItems]) so the
        'count of records can be calculated
        Me.txtCount.ControlSource = "=Count([QuantityItems])"
    End If
 
End Sub

Hope this helps!


Richard

 

[hokiewalrus]

You can use the same method on the Total Items subform that I previously posted on the Total Payments sub form:
Richard

That's the weird part, in the database and screenshot that I posted, I am using the method for the items as well as the payments, yet it's giving me different results.

In fact, as far as I can tell both totals fields are set up exactly the same. It's all very strange, and rather frustrating.