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?
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.