Calculated Fields between form and subform

[jnr927]

I have an Invoice Form with a Payments Subform. Here are the details:

frmInvoice
InvoiceID
Customer
ShipmentID
QtyShipped
UnitPrice

frmPaymentsSubform
PaymentID
InvoiceID
CheckNum
DateReceived
Amount

An invoice can be paid in multiple payments. (One-to-Many Relationship).

I have a calculated field that takes QtyShipped * UnitPrice, which gives me my total due. That works correctly. However, I want to have another calculated field that sums all of the payments (the Amount field). Then I want another field that takes the difference between the total due and the sum of all the payments, and gives me a Remaining Due calculated field.

Right now, my TotalPaid and TotalRemaining fields are marked with '#Error'. Is that because TotalPaid is a running sum? Every day I can go in and add a payment to an invoice, so the number would be constantly changing until it was paid in full.

Any suggestions would be great!

 

[RuralGuy]

Why don't you post the Control Source for TotalPaid and TotalRemaining!

Right now, my TotalPaid and TotalRemaining fields are marked with '#Error'.

 

[jnr927]

They were unbound fields I added into the form. The control source for TotalPaid is =Sum(Forms![frmPaymentsSubform]![Amount]) and my control source for TotalRemaining is =[TotalDue]-[TotalPaid]. Instead of the answers from those calculations displaying, I get #Error.

 

[Rich]

Put the Sum calculation in the subform footer, just refer to the textbox on the parent form

 

[jnr927]

Put the Sum calculation in the subform footer, just refer to the textbox on the parent form

Tried doing this, but nothing shows up because the subform is a Datasheet Form, so when it is viewed there is no header or footer.

 

[RuralGuy]

As Rich said, put a textbox (named txtMySum maybe!) on your SubForm with the Control Source set to =Sum([Amount]) It needs to be in the SubForm footer for the Sum() function to work properly.

The Control Source for TotalPaid changes to:

=NameOfSubFormControl.Form.txtMySum

You do *not* have to go back to the Forms collection to reference your SubForm. If in fact the name of your SubFormControl is "frmPaymentsSubform" then the reference would be:

=frmPaymentsSubform.Form.txtMySum

Keep in mind that the name of the SubFormControl on the main form does not have to be the same as the SubForm although I believe that is what it defaults to.

Here's a reference you should bookmark:

http://www.mvps.org/access/forms/frm0031.htm
Forms: Refer to Form and Subform properties and controls

 

[jnr927]

Thanks guys - it worked!

 

[RuralGuy]

You are very welcome from both of us and thanks for posting back with your success.

 

[jnr927]

Hey, I have one other problem with this.

Just to "refresh" you - I have a form with a subform:

frmInvoice
TotalDue
TotalPaid (References txtMySum in subform footer)
TotalRemaining (Calculates difference between TotalDue and TotalPaid)

frmPaymentsSubform
Amount (user entered)
txtMySum (In form footer that calculates the sum of all paid amounts: =Sum([Amount])

Everything works perfectly - except when $0 has been paid. Then TotalPaid is blank and TotalRemaining is '#Name'. How can I make a blank/null value as '0' instead of '#Name'?

I read a little about the Nz function, but am not sure how I would use it in this case.

Help is appreciated!

 

[RuralGuy]

I believe this should do it: =Sum(Nz([Amount],0))

 

[Rich]

=Iif(Forms![frmPaymentsSubform]![Amount],Forms![frmPaymentsSubform]![Amount],0)