Subform Calculations: Totals

[ilikebirds]

In the following image I have two subforms filtering to the selection of Township Combo.


In the Subform on the bottom I would like TOTAL FUNDS to calculate information from other fields in the row and update when I select the UPDATE Phases button.

What is the best practice for this? I've been racking my head over how to get the data into that subform field and then saved.

Basically Total Funds = Fund1 + Fund2

[June7]

Don't understand. Don't see any fields named Fund1 and Fund2.

Why save calculated results that can easily be calculated in a query whenever needed?

Similar named fields indicates data is not 'normalized'. Will never have more than 3 phases?

[ilikebirds]

Correct no more than the alloted phases.
I have it so that TotalFunds updates, but I am trying to get all the records in the subform to update through a button on the parent form. But I can not get it to work.
Dim rs As DAO.Recordset
Set rs = Me.Billingsubform.Form.RecordsetClone

With rs
.MoveFirst
Do While Not .EOF
.Edit
!TotalFunds = Nz([Calc1], 0) + Nz([Calc2], 0) + Nz([calc3], 0) + Nz([calc4], 0) + Nz([calc5], 0)
.update
.MoveNext
Loop
End With
Set rs = Nothing

Billingsubform being the subform name

[June7]

Never done anything like this.

Where are Calc1, etc. coming from?

Again, why save the TotalFunds calculated results?

[ilikebirds]

That's a good question. I Think we should just query all of that information and bring it to the subform.

calc1 was coming from the subform in the "Form Footer" so it doesn't show.

However if it is done through query - what if the user has to manually adjust 'Total Funds'?

[June7]

Manually adjust a calculated value?

You have 5 textboxes in a footer that are summed and you want that value saved to every record?

I am going in circles here. Do not understand what you want to do.

Want to provide db for analysis? Follow instructions at bottom of my post.

[ilikebirds]

No your suggestion of doing a query was perfect. But now I'm having trouble with one of the query expressions:

Phase1: IIf(Eval([case terminated date] Is Null And [certificate of eligibility] Is Null),0,IIf(Eval([case terminated date]>0 And [certificate of eligibility]>0 And [case terminated date]>[Certificate of eligibility]),[phase].[phase1],0))

The first nested iif works fine, and the truepart of the second nested is fine, however the False part throws an ERROR. Any Idea?

[ilikebirds]

I figured out why I was getting errors. 1. The field was type currency - So I changed it to text
Phase1: IIf(Eval([case terminated date] Is Null And [certificate of eligibility] Is Null),0,IIf([certificate of eligibility]>0 And [case terminated date]>[Certificate of eligibility],[phase].[phase1],IIf([case terminated date] Is Null And [certificate of eligibility]>0,[Phase].[Phase1],"Term")))