Summing two fields in a form & saving to a third

[BrotherBook]

Hi-

I've written VBA code to sum two fields in my form and save the information down into a third field. My variable where i calculate the information is working correctly, but the text box i am trying to save the value to always shows zero. All three fields are set up as doubles in order to capture the decimals.

Private Sub Spread_AfterUpdate()

Dim CouponCalc As Double

CouponCalc = Me.Cost_of_Funds.Value + Me.Spread.Value

MsgBox (CouponCalc)

Me.Coupon.Value = CouponCalc

MsgBox (Me.Coupon.Value)
End Sub

MsgBox (CouponCalc) yields .0333 (.0033 + .02)
MsgBox (Me.Coupon.Value) yields 0

 

[pr2-eugin]

Brother, is this absolutely required for you to store this information in the table? Storing calculated information in table is not really recommended.

This can simply be displayed for the record you are currently using; with an unbound text box??

 

[BrotherBook]

Paul,

I'm not quite sure yet if it will be absolutely necessary for me to store this value. I am going to be creating reports based on this information, and I have never written a report in Access.

For now i'll keep the field in my underlying table, but change my form to be unbound for that box.

Is there anything wrong with my code that would cause this not to store correctly?

Thanks,
Mike

 

[Brianwarnock]

Try the before update event, with the after update event for bound controls the old control value is not replaced until the after update event for the form is fired, ie the record is saved.

But Paul is correct it is very rare that calculated values need be saved and doing so can cause all sorts of problems not just be against the rules of normalisation which exist to avoid such problems.

Brian