What event should I use?

[JCShort]

I'm looking for suggestions for what event I should use to trigger the following procedure:

Private Sub Net_Calc()
[Net_Remaining] = [Over_Under_Audit_Findings] + Nz([Cash_Flow].Form![Transaction_Subtotal], 0)

End Sub

[Over_Under_Audit_Findings] & [Net_Remaining] are on my main form. [Transaction_Subtotal] is on a subform, which is attached to the main form.

[Net_Remaining] is bound textbox and I would like to keep it bound so that I can retain the realtime updates without running a query. I've tested the logic of the calculations through an unbound textbox and the calculations work fine. This leads me to believe that I'm not using the proper event to trigger the calculations.

I've tried [Net_Remaining] BeforeUpdate & AfterUpdate without success. I've tried to use LostFocus on the field in the Subform that should trigger the event with moderate success. The problem that I have in the subform is that tabbing out of the field doesn't trigger the event. Only left-clicking in the field, then left-clicking in another field would actually trigger the event.

Thanks in advance for your suggestions!

 

[Banana]

I'm not 100% sure if I understand the problem, so I'll try and phrase it then suggest a solution.

You basically are doing calculations that depends on values in form and subform, and you want it updated for every time a value change or we move to a new record.

If that is what you want, then you actually need to put it in multiple events. It would be easier to write a sub and just call it from various events.

We will need the following events:

OnCurrent event: So it updates everytime we navigate records.
AfterUpdate event of the main form's textbox that is part of the calculation
AfterUpdate event of the subform's textbot that is part of calculation.


That should be a good starter. To make a generic sub so all event can call it, just write thus:

Public Sub MyGenericFunctionNameGoesHere()

'Your calculation code goes here

End Sub

You can either put it in parent form's module (Just add it to the bottom of the module) or a generic module if you want it available for other forms.

To call that sub from event, you need one line like thus:

Private Sub Form_Current()

MyGenericFunctionNameGoesHere

End Sub

Note that if you have the generic sub in your form's module, you need to alter the syntax for your subform's controls:

Private Sub MySubformControl_AfterUpdate()

Me.Parent.MyGenericFunctionNameGoesHere

End Sub

I hope that helps. If I've misunderstood, let me know.

 

[JCShort]

Thanks, Banana. The concept of what you wrote makes perfect sense to me, but I wasn't able to get it to work.

I think something else is rotten in Denmark, though. I tried another workaround that should absolutely work, but the data isn't storing in my table. Here's what I'm trying to do:

User inputs an initial $ value in TextboxA. TextboxB in the subform is used to record payments/refunds against the initial value in TextboxA. Of course, there could be multiple payments, so I have an unbound calculation field in the footer of subform to sum(TextboxB). I need to store the net remaining $ value in TextboxC on the main form. The problem is that TextboxC is not used by the user.

The "sure fire" workaround that I tried was to create an unbound TextboxD on the main form
=[TextboxA] + nz(Subform![TexboxB],0)

That part works like a charm. Then I set the Subform OnEnter & OnExit, along with the TextboxA LostFocus to trigger the following:
TextboxC = TextboxD

When any of the 3 events are triggered, the calculation works great & TextboxC is updated with the value in TextboxD. Here's were Denmark comes in. When I go to another record and then return to this record, TextboxC is back to the original value. Somehow, I'm not retaining the updated value in my table.

I've reviewed the control source and it's accurate. Any ideas?