MSaccess VBA SOMETHING happens after oncurrent for calculated field

Question

I have a calculated field on a Form that isn't in the underlying table. For display purposes only. The field name is TotalMain The field's source is a calculated field in the footer of a subform. The subform is just a basic subform in tabular view. In the footer of that subform I have a sum field called TotalSub

Then a field on the main form has a source TotalMain=[subfrmData]![TotalSub]. The fields work perfectly and update perfectly as I navigate records on the main form.

I have a button that I want to enable if the [TotalMain] field is more than zero. Code in OnCurrent to enable the button works perfectly if I use a field that is in the underlying table. But [TotalMain] is just a field that dynamically changes/sums based on the data in the subform.

I put a test msgbox in the OnCurrent, and confirm that the field is blank at the time of OnCurrent. I proceeded to put a procedure in every.single.event. and nothing is triggered after that calculated field updates.

I also tried to put the code in OnCurrent of the subform (and then every single event of the subform). but I can't find ANY event that happens after the calculated field is updated.

What am I doing wrong? If there really is not a single event that occurs after that calculated field updates, is there a different way to go about this so I can get the calculated info to display AND for the button to enable when it's greater than zero?

Answer 1

Bind that textbox to a function that takes care of it using your current values as arguments:

=YourFunction([subfrmData]![TotalSub],[OtherField],[NameOfButton])

The function could be like this:

Private Function YourFunction( _
    TotalSubValue As Currency, _
    OtherValue As Currency, _
    YourButton As Button)

    Dim Value   As Currency
    Dim Enabled As Boolean

    ' Calculate value.
    Value = TotalSubValue * OtherValue  ' Your calculation
    
    ' Calculate button status.
    If TotalSubValue > 0 Then
        Enabled = True
    End If
    
    ' Set button.
    YourButton.Enabled = Enabled

    ' Return value.
    YourFunction = Value

End Function

You may have to expand this to handle possible Null values.