Using default value for calculation and recording to table

[bigalpha]

Firstly, I realize that I shouldn't normally record calculated figures into my table. I'm willing to buck proper procedure here for ease of use for referencing in reports; also, it's just one data point that will be calculated only once and not changed.

Anyhow, I have an IIF statement that calculates a total price based on a table of costs. I set this as the default value and it calculated properly - but once I set my text box to have a control source, it no longer calculates my cost.

I was hoping to set it so it calculated the cost and then write that total cost to my table.

edit: should mention that my calculated control is in a subform.

 

[bigalpha]

Doing the calculation in a query is just as easy as saving it in most cases.

To save the calculated value, you need to do it in a form event. You could use the BeforeUpdate event. You could do it in other events but you would need to write the code multiple times.

If Me.fld1 & "" = "" Then
    Msgbox "Fld1 is required.",vbokOnly
    Cancel = True
    Me.fld1.SetFocus
    Exit Sub
End If
If Me.fld2 & "" = "" Then
    Msgbox "fld2 is required.",vbokOnly
    Cancel = True
    Me.fld2.SetFocus
    Exit Sub
End If
Me.SavedField = Me.fld1 * Me.fld2

Thanks for the assistance. I'll try this tomorrow at work.

There is only one form that this calculated data will get computed, and it's a static event, after the first computation. It's easier for my beginning mind to use the field in the database when I need it rather than create the string to do the math each time.

Also, the computed number isn't a fld1 * fld2 scenario - it's more of a if fld1=x, then multiply by fld2, otherwise multiply by fld3.