This is a question of functional dependence. Its commonly encountered with
invoices where the unit price of a product will change with time but each
invoice record need to keep the price at the time that the invoice was
raised. You'll find an example in the sample Northwind database where the
Orders Subform looks up the current default unit price from the products
table and assigns it to the unit price in the Order Details table with the
following code in the ProductID combo box's AfterUpdate event procedure:
On Error GoTo Err_ProductID_AfterUpdate
Dim strFilter As String
' Evaluate filter before it's passed to DLookup function.
strFilter = "ProductID = " & Me!ProductID
' Look up product's unit price and assign it to UnitPrice control.
Me!UnitPrice = DLookup("UnitPrice", "Products", strFilter)
Exit_ProductID_AfterUpdate:
Exit Sub
Err_ProductID_AfterUpdate:
MsgBox Err.Description
Resume Exit_ProductID_AfterUpdate
Consequently when the unit price values in the Products table change the
unit price values already in the Order details table are unaffected, but new
rows in that table use the current prices from the Products table.
In relational speak the current unit price is functionally dependent on the
key of Products, but the unit price for each order item is functionally
dependent on the key of Order Details, hence the need for unit price columns
in both tables.
Your situation is, I think, analogous to this and can be handled in the same
way, in fact more simply so if you have just the one default rate as you only
need to look up the one value rather than the value for a particular product,
so when adding a new record you can simply assign the value in the form's
BeforeInsert event procedure. If your current rate is in a column
CurrentRate in a one row table tblCurrentRate say, and the column in the
form's underlying table is called RateApplicable the code would be:
Me.RateApplicable = DLookup("CurrentRate", "tblCurrentRate")
The gross fee would then be in a computed control which multiplies the
RateApplicable by the ThousandsOfWords control, i.e. with a ControlSource of:
=[ RateApplicable]*[ ThousandsOfWords]
or if you are storing the gross fee in a column in the table rather than the
rate applicable you can look up the value and multiply it by the
ThousandsOfWords value and assign it to the GrossFee control all in one in
the ThousandsOfWords controls AfterUpdate event procedure:
Me.GrossFee = DLookup("CurrentRate", "tblCurrentRate") * Me. ThousandsOfWords
Normally in situations like this, however, one would tend to do the former,
i.e. store the values on which the calculation is based, i.e. RateApplicable
and ThousandsOfWords in columns in the table and compute the gross fee on the
fly in a computed control or computed column in a query. You'll see that the
Northwind database does it this way, returning the Extended Price in a
computed column in the Orders Subform's underlying query, based on the stored
UnitPrice, Quantity and Discount values.
Ken Sheridan
Stafford, England
