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

[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_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.