S
SFAxess
Chris,
It sounds like you are saving the calculation in a table,
which is highly ill-advised. I would suggest that you
calculate the data and present to the user at runtime.
Storing calculations can cause serious integrity issues.
Either way, you should make a VBA function in the form's
module which has code for the actual calculation.
i.e. (this is simplified)
Function CalculateFees()as Currency
CalculateFees=nz(me.txtFee1,0) + nz(me.txtFee2,0)
End Function
Assign the function to a text box's ControlSource
property. For instance, set the ControlSource property of
txtTotal to be "=CalculateFees()" --don't include the
quotes of course.
When you go to a different record, the control will
automatically be updated. Put some code in the AfterUpdate
() event of controls that are part of the calculation
that will Requery the txtTotal box after the user has
made changes.
i.e.
Sub txtFee1_AfterUpdate()
me.txtTotal.Requery
End sub
When the user makes a change in txtFee1 then saves the
record, moves to another control, or another record, the
txtTotal will be recalculated.
Note: If you want your function to save the calculation
to a field in a table every time you re-calc, you set the
ControlSource of txtTotal to the field that holds the
calculation and change your function like so:
Sub CalculateFees()
Dim curTotal as Currency
curTotal=nz(me.txtFee1,0) + nz(me.txtFee2,0)
me.txtTotal=curTotal
End Function
In this case, you will need to replace the
me.txtTotal.Requery line of code in the events previously
mentioned, to the following:
Call CalculateFees()
Hopefully that will work for you.
Best of luck!
It sounds like you are saving the calculation in a table,
which is highly ill-advised. I would suggest that you
calculate the data and present to the user at runtime.
Storing calculations can cause serious integrity issues.
Either way, you should make a VBA function in the form's
module which has code for the actual calculation.
i.e. (this is simplified)
Function CalculateFees()as Currency
CalculateFees=nz(me.txtFee1,0) + nz(me.txtFee2,0)
End Function
Assign the function to a text box's ControlSource
property. For instance, set the ControlSource property of
txtTotal to be "=CalculateFees()" --don't include the
quotes of course.
When you go to a different record, the control will
automatically be updated. Put some code in the AfterUpdate
() event of controls that are part of the calculation
that will Requery the txtTotal box after the user has
made changes.
i.e.
Sub txtFee1_AfterUpdate()
me.txtTotal.Requery
End sub
When the user makes a change in txtFee1 then saves the
record, moves to another control, or another record, the
txtTotal will be recalculated.
Note: If you want your function to save the calculation
to a field in a table every time you re-calc, you set the
ControlSource of txtTotal to the field that holds the
calculation and change your function like so:
Sub CalculateFees()
Dim curTotal as Currency
curTotal=nz(me.txtFee1,0) + nz(me.txtFee2,0)
me.txtTotal=curTotal
End Function
In this case, you will need to replace the
me.txtTotal.Requery line of code in the events previously
mentioned, to the following:
Call CalculateFees()
Hopefully that will work for you.
Best of luck!