Linking a Field in a Form to a Calculating Field in a SubForm

[Big D]

Hello, I have a production scheduling database with a main form with a field that totals task cost by using:
=[Tasks subform]![SumOfCost] in a field which causes the field to update based on a value that's calculated in a subform for various tasks that are added to the subform. As I add tasks and their costs to the subform, the main form is supposed to accrue the costs in the field that I mentioned above.

It worked great when all I did was type in a dollar/cost value in the subform field for each task I added to the subform. The field in the main form updated and accrued costs flawlessly. However, the subform got complicated when boss wanted to add fields to the subform for other related costs such as materials costs. So I created an equation in the subform field to total the various costs from other fields in the subform and it works great on the subform. The subform field for each task calculates the cost correctly but now those total costs do not show up in the field in the main form. The subform equation is: =[Outside Services Cost]+[Material Cost]+[Billing Rates]*[Labor Hours]

Greatly appreciate any and all help in getting the field in the main form to update and accrue the costs being added to the subform tasks.

 

[mike60smart]

Hi

You need the following:-

=[SubformControlName].[Form]![YourTextBoxNameInSubformFooter]

 

[arnelgp]

my approach is VBA if it is ok for you.
paste this code in a New Stadard Module in VBA (Insert->Module):

Public Function fncCompute(f As Form) As Double
    On Error Resume Next
    fncCompute = 0#
    With f.RecordsetClone
        If Not (.BOF And .EOF) Then .MoveFirst
        While Not .EOF
            Debug.Print ![material cost].OldValue, ![material cost].Value
            fncCompute = fncCompute + _
                    Nz(![Outside Service Cost], 0) + _
                    Nz(![material cost], 0) + _
                    (Nz(![Billing Rates], 0) * Nz(![Labor Hours], 0))
                    
            .MoveNext
        Wend
    End With
    fncCompute = fncCompute + _
                Nz(f![Outside Service Cost], 0) - Nz(f![Outside Service Cost].OldValue, 0) + _
                Nz(f![material cost], 0) - Nz(f![material cost].OldValue, 0) + _
                (Nz(f![Billing Rates], 0) * Nz(f![Labor Hours], 0)) - _
                (Nz(f![Billing Rates].OldValue, 0) * Nz(f![Labor Hours].OldValue, 0))
        
    f.Parent!Text1.Value = fncCompute

End Function

you should replace Text1 in the function with the correct name of your textbox in the main form (the one that will received the Total Cost).

on design view of your form,
remove the Expression "=[Tasks subform]![SumOfCost]" from the textbox (blank control source).

add code to the Activate Event of the main form:

Private Sub Form_Activate()
    Call fncCompute(Me![Tasks subform].Form)
End Sub

click on [Outside Services Cost] textbox on the subform.
on it's Property Sheet->Event->After Update, type:

=fncCompute([Form])

type the same on the After Update event of the rest of the textboxes ([Material Cost], [Billing Rates] and [Labor Hours]).

 

[Big D]

Hi

You need the following:-

=[SubformControlName].[Form]![YourTextBoxNameInSubformFooter]

Thanks very much for your help. Can you please tell me where I can find the SubformControlName? Is this the title that shows at the top of the subform? The title is Task Details. Also, does [Form] stay as you've written in the expression or should Form be replaced with a form name?

Thanks Again,
Big D

 

[Big D]

my approach is VBA if it is ok for you.
paste this code in a New Stadard Module in VBA (Insert->Module):

Public Function fncCompute(f As Form) As Double
    On Error Resume Next
    fncCompute = 0#
    With f.RecordsetClone
        If Not (.BOF And .EOF) Then .MoveFirst
        While Not .EOF
            Debug.Print ![material cost].OldValue, ![material cost].Value
            fncCompute = fncCompute + _
                    Nz(![Outside Service Cost], 0) + _
                    Nz(![material cost], 0) + _
                    (Nz(![Billing Rates], 0) * Nz(![Labor Hours], 0))
                    
            .MoveNext
        Wend
    End With
    fncCompute = fncCompute + _
                Nz(f![Outside Service Cost], 0) - Nz(f![Outside Service Cost].OldValue, 0) + _
                Nz(f![material cost], 0) - Nz(f![material cost].OldValue, 0) + _
                (Nz(f![Billing Rates], 0) * Nz(f![Labor Hours], 0)) - _
                (Nz(f![Billing Rates].OldValue, 0) * Nz(f![Labor Hours].OldValue, 0))
        
    f.Parent!Text1.Value = fncCompute

End Function

you should replace Text1 in the function with the correct name of your textbox in the main form (the one that will received the Total Cost).

on design view of your form,
remove the Expression "=[Tasks subform]![SumOfCost]" from the textbox (blank control source).

add code to the Activate Event of the main form:

Private Sub Form_Activate()
    Call fncCompute(Me![Tasks subform].Form)
End Sub

click on [Outside Services Cost] textbox on the subform.
on it's Property Sheet->Event->After Update, type:

=fncCompute([Form])

type the same on the After Update event of the rest of the textboxes ([Material Cost], [Billing Rates] and [Labor Hours]).

Thanks very much for your help in solving my forms problem. Can you please tell me where to add the code in the 2nd box:

add code to the Activate Event of the main form:

Code:
Private Sub Form_Activate()
Call fncCompute(Me![Tasks subform].Form)
End Sub

Thanks Again,
Big D

 

[mike60smart]

Hi Big D

Yes to both questions

 

[Big D]

Hi Big D

Yes to both questions

Thank you again for confirming for me. Unfortunately, I couldn't get it to work. I got the follow errors:

Total Task Cost field in the footer came up with #Name? and the Balance field came up with #Type!

This is what I put in the Total Task Cost field on the main form:
=[TaskDetails].[Form]![TotalTaskCost]

Did I screw this expression up or put it in the wrong place?

Thanks Again,
Big D

 

[arnelgp]

you put it here while on design view of your form.

 

[June7]

SubformControlName would be name of subform container control that holds form. I always name container different from object it holds. If you didn't change defaults Access assigned, subform container might have a name like Child0 or it could have same name as form, depending how subform was created. Click on subform and look at properties sheet to see control name.

Either of these should work:
=[TaskDetails].[Form]![TotalTaskCost]
=[TaskDetails]![TotalTaskCost]

If you want to provide db for analysis, follow instructions at bottom of my post.

 

[arnelgp]

here is a sample db to play with

 

[Big D]

here is a sample db to play with

Hi arnelgp,

Thanks very much for sending over the database to practice on. Boy am I having a heck of time trying to sort this problem out. I thought about the problem a bit more and thought that it would be easier to make some screenshots of my database and add notes and a more clear description of what I am trying to link up. So I did this, converted the file to PDF and zipped it to shrink the file size so that it would upload here. Would you please take a look at it. I stepped through the 3 screenshots describing the key fields and what fields I need to link?

Thanks Again for all your help.

Big D

 

[mike60smart]

Hi Big D

The only way we are going to resolve this issue is if you can upload a zipped copy of the Database.

If you cannot upload to the forum then pm me with a copy.

 

[Big D]

Hi Big D

The only way we are going to resolve this issue is if you can upload a zipped copy of the Database.

If you cannot upload to the forum then pm me with a copy.

Hi mike60smart,

Yes, I believe you're absolutely right.

Thanks very much for all your help!!
Big D

 

[Big D]

Hi mike60smart,

Yes, I believe you're absolutely right.

Thanks very much for all your help!!
Big D

mike60smart,

I guess you can tell from the screenshots and my notes on them that what I really need is to be able to link the cost field in the subform to the calculated cost field in the Task Details form so that the subform field updates with the value in that's calculated in the Task Details form's cost field.

Thanks a bunch!!

Big D

 

[June7]

I cannot open zipx file. Hope someone else can help you.

 

[mike60smart]

Hi Big D

Not following what it is you need.

You have a Form that Opens to Show Project Details.

Project Details are Viewed on Tab 1

Tab 2 has a Subform to display all Tasks Associated with the Project.

Your Project Details Tab currently displays all Totals including the Sum Of Tasks from the Tasks Subform

Can you explain in more detail what it is you need to see?

 

[Big D]

I cannot open zipx file. Hope someone else can help you.

Can you please try this zipped copy. I used WinZip for this one.

Thanks very much!

Big D

 

[arnelgp]

here please try.

 

[June7]

I am not seeing expressions described in first post. Fields are in table but not used on form.

Do calcs in query that add fields then use that query as form RecordSource. Or even do simple arithmetic with Calculated field in table. Then use that field in Sum() aggregate calcs.

 

[mike60smart]

Hi Big D

Your Projects has the value 70 for Billing Rates

Your Tasks Subform has the values as follows:-

Material Cost = 100
Outside Services Cost = 100
Labor Hrs = 6

What value do you want to see displayed ?