Total subform items on main form

[jazminecat]

I have a form with three subforms. each of these subforms contains charges to an account. Not all records have charges on all subforms, though. So some records will have no charges at all, some will have charges from one, two, or all three. It varies.

I have a text field on the main form into which I want to sum the total of all charges for any given record. It is an unbound text box, and has this code in it's control source:

=[QryCommissary subform].Form!SumOfAmount+[QryBookingFees subform].Form!Amount+[QryDental subform1].Form!Amount

where QryCommissary subform is one form, QryBookingFees subform is the second form, and QryDental subform1 is the third form.

When I load the form I get #error on the text field, no matter what record I am on.

Any thoughts? I'm not averse to handling this field using vba instead, i'm just stuck on how.

thanks!

 

[jazminecat]

Edit: it does work if all the fields on the subforms contain something. It doesn't if there is an empty one, so if they don't have a charge in one of the categories, then I get the error. So how do I prevent that/ thanks.

 

[Remou]

You need an Nz, to avoid nulls.

 

[VicRauch]

If a field is empty, then it is Null. Nulls are not nice and don't play well with others.
do this to the last part of your formula.
=Nz([QryCommissary subform].Form!SumOfAmount)+Nz([QryBookingFees subform].Form!Amount)+Nz([QryDental subform1].Form!Amount)
That is putting Nz( ) around each field that you want in your formula
The Nz(...) turns Null into zero (0) or into a zero length string ("").

 

[jazminecat]

Thanks for the feedback. Ok, I changed it to

=Nz([QryCommissary subform].Form!SumOfAmount)+Nz([QryBookingFees subform].Form!Amount)+Nz([QryDental subform1].Form!Amount)

and now get #error all the time, regardless of which ones have values in them. Any thoughts?

 

[jazminecat]

Ok, I have done some more troubleshooting. All fields are formatted as currency. I have shortened the names of my subforms for readability and to eliminate spaces as well. If I have only two items in the series, it works when there is an amount in both fields. It does not work if one or both are blank. For example:

=Nz([SubCommissary].Form!Amount)+Nz([SubDental].Form!Amount)

works and shows a total if there is something in Commissary and someting in Dental, but shows #error if either are blank. Same for

=Nz([SubCommissary].Form!Amount)+Nz([SubBookingFees].Form!Amount)

so the problem is that on most records, one of the three will be blank, thus making it show as #error.

thanks for your help, hope someone has a solution to this conundrum.

 

[VicRauch]

My eye can not see any difference between what I sent you, and what you sent back, plus I just took your original formula and added the Nz(...) to each of the three parts, and can still NOT see any difference.

Would you copy the formula straight from your form and send that please?

If that is what you already did, then take out one of the Nz(...) at a time and test the formula to see when it works again. Take it out of the first part, then put it back in the first part and remove it from the second part, etc until it works.

Then let us know what worked by coping the formula from the form and pasting in your response.

Thanks,
Vic

 

[jazminecat]

Sorry Vic, I guess I wasn't clear. What I means was that I changed it exactly to what you suggested, and then I posted the results. I did copy that back from my form, but yes, it is in fact the code you suggested. My subsequent attempts have returned the results I listed in my last post - essentially that if any item listed in the calculation is empty, then I get the #error in my textbox on my main form. If I only list two items in my calculation, it doesn't matter if I have the Nz in it or not - if both subforms have something in them, then the calculation works; if one does not, it gives me the #error.

This code:
=Nz([SubBookingFees].[Form]![Amount])+Nz([SubCommissary].[Form]![TotalAmount])+Nz([SubDental].[Form]![Amount])

and this code

=[SubBookingFees].[Form]![Amount]+[SubCommissary].[Form]![TotalAmount]+[SubDental].[Form]![Amount]

behave the same. They will always return an error unless there is something on every subform. There are very few records with someting on all three.

This code:

=[SubBookingFees].[Form]![Amount]+[SubCommissary].[Form]![TotalAmount]

works fine as long as there is something on both subforms. Otherwise i get #error

this code:

=Nz([SubBookingFees].[Form]![Amount])+Na([SubCommissary].[Form]![TotalAmount])

works the same, that is as long as there is something on both forms, it's fine, if one is empty, i get #error.

I hope I have been more clear, and apologize for being vague before. Thanks y'all!! I appreciate your help!

 

[Remou]

From what you say, your subform are not returning any records, so perhaps code would be best to check this:

Private Sub Form_Current()
Me.Recalc
If Me.[SubBookingFees].[Form].[Amount].RecordsetClone.RecordCount = 0 Then
    intAdd = 0
Else
    intAdd = Me.[SubBookingFees].[Form].[Amount]
End If
<...>
End Sub

 

[VicRauch]

Whoa, this is strange, but we shall overcome.

I have set up a test form with two subforms and I haved added the two amounts from the sub forms. I have not gotten the #error yet.

What version of Windows are you using, and what version of Access are you using?

Plus, it will be good to see what results you get from Remou's suggestion.

BTW, here is my formula on my main form:
[tt]=nz([frmAssociation].[Form]![AmountGiven])+nz([frmContacts].[Form]![AmountPledged])[/tt]

 

[Pampers]

I found that populating a (un)bound textbox on a mainform from different subforms by the use of an expression/ referenced calculated field in an on currenct() is unreliable. Specially when it is coming from calculated totals from subforms and if it is over network. The calculations take fractions of seconds to show the result, while the oncurrent() executes directly on the current record.

To go around this problem i programmed little delays in the oncurent() before executing something like: =[QryCommissary subform].Form!SumOfAmount+[QryBookingFees subform].Form!Amount+[QryDental subform1].Form!Amount

Maybe better even is to create a (totals)query that calculates the totals and put the result in your mainform.

Here is some code to wait a fraction:

'Wait WAITSECONDS for the next line to execute
Dim StartTime
Dim WAITSECONDS As Integer
WAITSECONDS = 0.1

StartTime = Timer
While Timer < StartTime + WAITSECONDS
DoEvents
Wend
UpdateChargesImport

Pampers
Just let it go...

 

[Remou]

Hi Pampers
I think the
[tt]Me.Recalc[/tt]
at the beginning of my snippet takes care of the fractions of a second thing.

 

[Pampers]

I hope so. I tested it on an old database which derives columntotals from a sumform on to the mainform...but it al results in zero's on the mainform.

Pampers
Just let it go...

 

[jazminecat]

could the fact that the subforms are set to continuous forms, as each record on the main form could have multiple matching records in a subform - could this have something to do with it? I am using access 2000 on windows 2000

If I just put this code in the on current event of my main form it throws an error:

Private Sub Form_Current()


Me.Recalc
If [red]Me.[SubBookingFees].[Form].[Amount].RecordsetClone.RecordCount = 0 Then[/red]
    intAdd = 0
Else
    intAdd = Me.[SubBookingFees].[Form].[Amount]
End If
End Sub

RunTime error 438, object doesn't support this property or method, and the red line is highlighted.

I am troubleshooting this by just using two subforms. I have the following code:

=Nz([SubCommissary].Form!TotalAmount)+Nz([SubDental].Form!Amount)

if there is data in commissary and in dental, I get an amount in my text box.

if there is no data in either, the textbox says #error.
if there is data in one but not the other, the textbox says #error.

The subforms are populated by the following code, for dental:
SELECT QryDental.InmateNumber, QryDental.Amount
FROM QryDental;


It is pulling from a query, which pulls the dental info out of a table.

Is this the problem?

I'm not sure what else to tell y'all, I really appreciate all your help with this, but I'm thinking maybe I need to find a different approach?

 

[Remou]

The syntax seems to be a little here:
Me.[SubBookingFees].[Form].[Amount].RecordsetClone.RecordCount = 0 Then
Try:
[tt]Me.[SubBookingFees].[Form].RecordsetClone.RecordCount = 0 Then[/tt]
Sorry about that - cut and paste can be great, but only with care.

 

[jazminecat]

ok. I changed it to:

Private Sub Form_Current()


Me.Recalc
If Me.[SubBookingFees].[Form].RecordsetClone.RecordCount = 0 Then
    intAdd = 0
Else
    intAdd = Me.[SubBookingFees].[Form].[Amount]
End If
End Sub

and now it doesn't throw an error. it hasn't changed the behaviour though, I still get #error in the textbox.

 

[Remou]

What is the full code you are using at the moment? Just to sketch the way I imagined it:

Test for records in subform #1
If not found intAdd #1 = 0
Else intAdd #1 = Amount #1

Test for records in subform #2
If not found intAdd #2 = 0
Else intAdd #2 = Amount #2

Main form textbox = intAdd #1 + intAdd #2

 

[jazminecat]

I'm not using any code in the vba window except what you suggested. That's it.

 

[Remou]

Ok. The way I was imagining the set up is that you make the text boxes on your form unbound and update them using code. The snippet I suggested is, as it were, part 1. I will have to guess at the names of your subforms and controls, but here goes:

Private Sub Form_Current()
Dim intAddBF as Integer
Dim intAddCom as Integer
Dim intAddDent as Integer

Me.Recalc
'First subform
If Me.[SubBookingFees].[Form].[Amount].RecordsetClone.RecordCount = 0 Then
    intAddBF = 0
Else
    intAddBF = Me.[SubBookingFees].[Form].[Amount]
End If

'Second subform
If Me.[SubCommissary].[Form].[Amount].RecordsetClone.RecordCount = 0 Then
    intAddCom = 0
Else
    intAddCom = Me.[SubCommissary].[Form].[TotalAmount]
End If

'Third subform
If Me.[SubDental].[Form].[Amount].RecordsetClone.RecordCount = 0 Then
    intAddDent = 0
Else
    intAddDent = Me.[SubDental].[Form].[Amount]
End If

'Guessing the name of the textbox on the main form
Me.[TotalAmount] = intAddBF + intAddCom + intAddDent
End Sub

I have typed the above, so I hope I have got it right

 

[jazminecat]

hm. okay I have put in your suggested code and here's what I find. I will total the dental and booking just fine, when there is information in either of those fields. but...we run into another glitch with the commissary, or when there is more than one item in the dental or booking.

On the commissary subform, the field TotalAmount is in the footer, and is a total of whatever items are listed for that particular record on the main form. This code isn't grabbing that information from the footer.

I have found a different way to handle this using queries, and making the total another subform pulling from a query that toals the information for me. It just doesn't seem like what i want to do here is going to work. But if you know of a way to make the total see the subcommissary total in the footer of the sobcommissary form, i'm all ears. . . and thank you, a million times, for your help.