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.mainCost = sum(forms!subForm!subCost)

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

I did try the =Sum(Forms!subForm!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!subForm!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_Subtotal 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_Extended_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_Extended_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 = unboundfieldname.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

[ajalwaysus]

OK, but first I have to ask:
Why are you allowing someone to edit a field that displays a subtotal, if that happens then the subtotal won't match what the actual total is in the subform?

-AJ

[bkberg05]

I use this to track orders. For most orders, it's not important to me or my business to track the details for those orders (the individual items) and sometimes the individual items may number into the hundreds and I don't have time to put them all in there. So usually I just put in the order and then start at the subtotal field and put the amount of the order prior to shipping and tax. But sometimes the detail is important to me and I do enter detail records. With the data in the detail records already, I figured why should I hand calculate the subtotal - I should just let the form do it for me. And that's where we're at.

[ajalwaysus]

So if I understand you correctly, sometimes you just want to skip the order by order entry and just enter an amount owed without details.
If so, you may want to consider adding a field that actually stores this value that is equal to the subtotal but can be overwritten. This way you will see the subtotal, and then have an actual total owed field that is separate to do with what you please.

Make Sense?
-AJ

[bkberg05]

That's the goal. I need to store the sub-total either way. Sometimes it's the sum from the subform. Sometimes it's entered directly.

[ajalwaysus]

But what I am saying is perhaps you will want to store both in any case, this way you have a record of what was calculated by the DB and you will also have what was manually entered by the person.

You can bound both fields to the table as separate fields in the table. (i.e. Calculated Total, Manual Total)

Then to set the value of the Calculated Total, instead of keeping the code I have in the subtotal field, assign the value on the VBA side, and lock this text box.
Then create the second text fields bound to Manual Total, and assign the subtotal to it as well, but leave it unlocked so that i can be changed if they wish.

Hope this makes sense, let me know if I am not.
-AJ

[bkberg05]

I'm good with this. I just can't figure out the part about assigning the value on the VBA side for the Calculated Total. I don't know how to make that happened and have tried a variety of things...

[ajalwaysus]

you just need to write code probably on the Form_Activate(). You need to write some code along the lines of:

Private Sub Form_Activate()
    Me.Order_Cost_Subtotal.Value = [enter the code to retrieve the value from the subform]
End If
 

I am here to help, but I also would like you to get the hang of this without me giving you the answer outright.

I can answer any questions you have to the best of my ability. Don't hesitate to ask.

-AJ

[bkberg05]

Why the Activate event? I've not used that, but it says this runs when the form opens and becomes the active form. Which means it won't run when I move from record to record.

Even if the activate worked when moving from the sub-form to the regular form, I don't want the value on the regular form to wait that long. I want to update it from while I'm still inside the subform.

Why can't this be done on the After Update event on the Order_Item_Cost field?

And I've tried (on the After Update event of the Order_Item_Cost field)

Me.Parent.Order_Cost_Subtotal.Value = sub_Order_Item_Extended_Subtotal (which is what I called the sum field in the footer of the subform)

I've also tried a bunch of other variations and the best I can get it to do is to change to zero, which is odd but seems like my syntax is correct.

[ajalwaysus]

for future reference you need to use the [code] Tags when posting code, the button looks like a "#".

Now try this:

Me.Parent.Order_Cost_Subtotal.Value = me.sub_Order_Item_Extended_Subtotal.value
Me.Parent.Order_Cost_Subtotal.Requery
 

-AJ

[bkberg05]

Private Sub Order_Item_Cost_AfterUpdate()
 
    Order_Item_Extended_Cost = Order_Item_Quantity * Order_Item_Cost
    Me.sum_on_sub_form.Requery
    Me.Parent.sum_on_main_form.Requery
 
End Sub
 
Private Sub Order_Item_Cost_LostFocus()
 
     Me.Parent.Order_Cost_Subtotal.Value = Me.sum_on_sub_form.Value
 
End Sub
 

This is as close as I've gotten it. I changed the sum field names so I could keep track of them better. The result here is that if you change the amount and hit tab, the cursor stays in the amount field and the two sum fields change (the one on the main form and the one on the subform). Then tab again and the subtotal field on the main form changes as I desired. I have no idea why the first tab doesn't leave the field.

Putting the

     Me.Parent.Order_Cost_Subtotal.Value = Me.sum_on_sub_form.Value
 

in the After Update instead of the Lost Focus does nothing. It just zeros out the field on the main form.

Adding the requery you suggested in the last post did nothing at all regardless of where the

     Me.Parent.Order_Cost_Subtotal.Value = Me.sum_on_sub_form.Value
 

was located.