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

[bkberg05]

Hi and thanks! It displays fine when you first go to a record, but when you're adding or updating a record, the sum fields don't also update. Do you know how to make them update. I tried doing a .refresh for the field but it didn't like that.

Sorry to drag this out, but appreciate your patience.

Thanks

[ajalwaysus]

Try ".Requry" on both the sub-form and main-form field. Let me know if that works.

No apology needed, the goal is to get you a solution.

-AJ

[bkberg05]

No luck. The requery doesn't give an error, but nothing happens. I just did it to the field on the subform. Any other suggestions?

[ajalwaysus]

Update your VBA Order_Item_Cost _AfterUpdate() code with this..

Private Sub Order_Item_Cost_AfterUpdate()

    Order_Item_Extended_Cost = Order_Item_Quantity * Order_Item_Cost
    Me.Order_Cost_Subtotal.Requery
    Me.Parent.Order_Cost_Subtotal.Requery

End Sub

Let me know if this works, it worked for me, so if it doesn't you need to spell out exactly what you are doing.

-AJ

[bkberg05]

Hi - I wasn't using the me. in front of the field. So now that works. Now I need to get that information into a bound field on the main form which someone can then edit if they need to. I tried setting the value of the bound field as fieldname.value = unboundfieldnam e.value (tried it both for the unbound field on the main and the subform). I did this right after the requery command. The data in the bound field changed to zero for some reason (both when I used the value of the unbound field on the form and the subform).I also tried it on the after update and change action of both the unbound fields and that didn't work either.

Feels like we're getting close, but may need some more of your assistance...

Thanks