populating a field using the sum of a field from a subform

[bkberg05]

Hi - I have a form called mainForm that has a subform called subForm. The subform has a numeric field called subCost. There are many subForm records for every mainForm record. On the main form, there is a field called mainCost.

Whenever someone makes a change to the subCost field, I want to populate the mainCost field with the sum of the subCost values. The mainForm and the subForm are joined by mainID = subID.

Here is my lame attempt at writing this myself (on the After Update event of the subCost field).

mainCost = "(SELECT Sum([subCost]) From subForm WHERE [subForm.subID] = [mainForm.mainID)"

Any help you can provide would be greatly appreciated.

Thanks in advance,
Bill

[ajalwaysus]

You are on the right track, try just setting the control source of the MainCost in design view.

=SUM(forms!SubForm!SubCost)

Then try just re-querying the MainCost after update.

Private Sub subCost_AfterUpdate
      me.parent.maincost.requery
End Sub
 

[bkberg05]

Hi and thanks. I don't think I can do it this way since the user needs to still be able to edit the mainCost field. Sometimes there won't even be a record on the subForm and the mainCost will have to be updated. Also it's record source needs to remain tied to an underlying table as I want to store data there.

[ajalwaysus]

You can change the control source on a need by need basis in the VBA code.

-AJ

[bkberg05]

Hi - I don't know how to do that. Is there a way I can set it to that value on the After Update of the subCost field. I tried:

Me.Parent.mainC ost = sum(forms!subFo rm!subCost)

But it doesn't seem to like the sum command.

I did try the =Sum(Forms!subF orm!subCost) as the control source for the mainCost field to see if I could make it work and it just displayed an error in the field.

[ajalwaysus]

Try this..

Private Sub SubCost_AfterUpdate
    Me.Parent.mainCost.ControlSource = "=sum([forms]![subForm]![subCost])"
End Sub
 

When assigning a control source, you pass it the exact value you want, as you want it to appear in the properties. That is why we pass the full text above.

Let me know if this works.
-AJ

[bkberg05]

The field displays an #Error. Same thing that happened when I put that exact language into the control source. It strips out the brackets so all that's left is the =sum(forms!subF orm!subCost!). I tried putting in manually with and without the brackets. I also used the expression builder to get the syntax right and it was the same thing. It gave me =Sum([subForm].Form![subCost]). Same #Error that way.

I'm sure there's more than way to do this and even if I can get the Control Source to make the calculation, I still have to deal with storing the value of this field into the mainCost field and allowing the user to be able to change it.

Why can't we (on the After Update action of the subCost field) execute a query which makes the calculation and then stores the value into the mainCost field. Then I don't have to deal with storing the value from an unbound field and dealing with the user updating the field.

Thanks again for your help.

[ajalwaysus]

You said you wanted them to be able to update that field when there were no subCost(s). Perhaps, if you could, attach the DB in a zip file - stripped of all sensitive data - so that we can get to the heart of this issue, because I cannot understand why this did not work.

-AJ

[bkberg05]

Thanks and I've done that. We'll have to switch to the real field and form names.

Open the 'Order' form. The Subtotal field is the one I'm trying to update. It currently has the =sum(...) as the Control Source. It should be populating the field called Order_Cost_Subt otal in the Order table. The subform is called sub_Order_Items . The data behind the form is tied to the Order_Items table. I'm trying to total the amounts in the Order_Item_Exte nded_Cost field.

I've been putting the code for this (many variations as I've tried different things) on the AfterUpdate action for the Order_Item_Cost field on the subform. This same AfterUpdate action also updates the Order_Item_Exte nded_Cost field as this field is currently not enabled (it being calculated).

There is one record in here that I've been testing with. I change the Order_Item_Cost field and see if it works.

Thanks again for looking at this.

[ajalwaysus]

Sorry for the delay, I don't know why but it seems you cannot create a sum function on a main form that feeds from the subform. So I cheated a little and created a sum field on the subform and then related the subtotal field on the main form to pull the value of the sum on the subform. Hope I didn't lose you, in case i did I reattached your DB with my enhancement. You could also hide the field on your subform, and the reference will still work.

Let me know if you need anymore help,
-AJ