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

[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_Subtot al (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.