I have an unbound calculated field on the parent form
containing multiple subform field sums. On each current
record, I need to populate a bound field on my parent form
with the unbound data.
My problem is that the calculations run AFTER any command
I use to transfer the data. Is there a common (or
otherwise) method I can use to permit the calculations to
run first, then trigger my transfer?
Thanks.
I think I understand... the difficulty is that the data is not updated to the table and so the calculation is not reflecting
the potentially new data?
I have thrown this together quick, I think it solves your problem. The idea is to subtract the old value (stored in the
control) of the currently selected (editing) record from the Sum amount and add the new value.
Public Function FlexSum(SumAmount As Variant, ctl As Control) As Long
Dim CurrentAmount As Variant, OldAmount As Variant, CurrentSum As Variant
CurrentAmount = 0
OldAmount = 0
CurrentSum = 0
If IsNumeric(SumAmount) Then CurrentSum = SumAmount
If IsNumeric(ctl.OldValue) Then OldAmount = ctl.OldValue
If Screen.ActiveControl Is ctl Then
If IsNumeric(ctl.Text) Then CurrentAmount = ctl.Text
Else
If IsNumeric(ctl) Then CurrentAmount = ctl
End If
FlexSum = CurrentSum - OldAmount + CurrentAmount
End Function
I created it to be versatile so it's best to put it in a standard module so that it could be re-used throughout your project.
To use it, make sure the field(s) you're calculating on are named differently than the controls that they are bound to. Then
alter the ControlSource of the control which sums the values, replacing
SUM([value])
with
FlexSum(SUM([Value)), [ControlName])
where ControlName is the name of the control that contains the basic values that are summed. For example, if you were summing
a field named Quantity and the control that you enter the Quantity into was named QuantityText:
FlexSum(SUM([Quantity]), [QuantityText])
Then on the event AfterUpdate on the QuantityText control, force the summing control to Requery. In this example, I will call
the control SumQuantityText:
Private Sub QuantityText_AfterUpdate()
Me.SumQuantityText.Requery
End Sub
Also with the Change event of the QuantityText control if you wish it to update as they are typing a new value:
Private Sub QuantityText_Change()
Me.SumQuantityText.Requery
End Sub
That should pretty well do it (I hope)
Jeremiah Ellison
Ellison Enterprises - Your One Stop IT Experts
