need calculated field in form

[Vernon]

I have an existing form that is linked to a table. The
purpose of the form is to input data into the table.

I have one field that includes "fees per unit" (in
dollars, selected from a pull down menu which is linked to
another table) and another field that includes "units"
(the user inputs ordinal data -- 1,2,3 etc). I need the
existing field labeled "obligation fee" to be an automatic
multiplication of the "fees per unit" and "units" fields.
I am hoping that the "obligation fee" field can
automatically appear to the person inputing the data as
soon as they fill in the "fee per unit" and "units" field
and I also want the table receiving the data to show this
value.

It is very important that the "obligation fee" field be a
calcuated value as we are looking for discrepancies
between actual fees paid and "obligation fees."


Thanks,
Kendra Vernon

 

[Nikos Yannacopoulos]

Kendra,

Put the following lines of code in the Before Update event of both the "fees
per unit" and "units" controls:

If Not IsNull(Me.[fees per unit] ) And Not IsNull(Me.[units]) Then
Me.[obligation fee] = Me.[fees per unit] * Me[units]
End If

To put the code where it must go, while in form design view select each
control and right-click to dispay Properties (if not already visible). Go to
tab Events, locate the one called Before Update, place the cursor in the box
next to it and click on the little button with the three dots on the right.
Select Code Builder; you will be taken to the VB editor screen, and the
cursor will be between two lines like:

Private Sub ControlName_BeforeUpdate(Cancel As Integer)

End Sub

Paste the code in between. Make sure the control names are identical to
those appearing under Name on the Other tab of the properties window (or
change in the code if required, to match the actual names in the form
design). Save and you are done.

A comment on saving this value in a table: storing data that can be easily
calculated on the basis of other stored data at any time is not good
practice. it just adds to the database complexity and storage space
requirements, offering nothing in return. You can always multiply the two
numbers in any query, form, report or in code.

HTH,
Nikos