You may be new to access but you seem to have a pretty good grasp of what's
required here, and just need to learn how to implement it.
As its sound like a slightly complex computation I'd suggest you do it in
code by means of a function. A function is a block of code which returns a
value based on other values. These can be passed into the function as its
'arguments' or the code in the function can get them from somewhere. Lets
take a simple example. Say you have a table Products with a numeric column
ProductID a Currency column UnitPrice and a numeric column VATRate (VAT =
Value Added Tax if you are not in Europe) which is a fractional value, say
0.1 for a 10% tax rate.
You have an InvoiceDetails subform (within an Invoice parent form) bound to
a table InvoiceDetails with columns ProductID, UnitPrice, Qty and VATRate.
Of these UnitPrice and VATRate can change with time so these need to be
columns in the InvoiceDetails table, not computed on the fly, which is
analogous to your situation). The Invoice details subform would also need to
show the TotalNetPrice and TotalGrossPrice, but these are computable so
should not be stored in the table.
It would actually be quite simple to do this with expressions in the form,
but to illustrate the use of functions lets do it that way for the total
prices:
First you need to add the functions to a standard module, so open any
existing module or create a new one from the Modules page of the database
window. The module will be shown in the VBA editor at its 'declarations'
area. To insert a function select Procedure from the Insert menu on the VBA
menu bar. In the dialogue select Function as the type and Public as the
scope. The latter makes it available anywhere in the database. Name it
GetNetPrice and click the OK button. With the bare bones of the function
visible in the VBA editor the first thing to do is give it some arguments, so
enter the following between the parentheses after the function's name:
varProductID, varUnitPrice, varQty
Because the function needs to return a price put the following after the
parentheses:
As Currency
It should now show:
Public Function GetNetPrice(varProductID, varUnitPrice, varQty) As Currency
End Function
The code for the function is entered between these lines as follows:
Public Function GetNetPrice(varProductID, varUnitPrice, varQty) As Currency
Dim curUnitPrice As Currency
' first make all both arguments are not Null
If Not IsNull(varProductID + varUnitPrice + varQty) Then
curUnitPrice = DLookup("UnitPrice", "Products", "ProductID = " &
varProductID)
' compute total net price as function's return value
GetNetPrice = curUnitPrice * varQty
End If
End Function
To get the Gross Price add another function to the module. As well as
taking arguments this function calls the first function:
Public Function GetGrossPrice(varProductID, varUnitPrice, varQty,
varVATRate) As Currency
Dim curNetPrice As Currency
' call function to get net price
curNetPrice = GetNetPrice(varProductID, varUnitPrice, varQty)
'compute gross price as function's return value
GetGrossPrice = curNetPrice * (1 + varVATRate)
End Function
You'll see that this time the function takes an argument for the VATRate as
well
Back in the InvoiceDetails subform we need to insert the current UnitPrice
for the product and its current VAT rate. So in the ProductID control's
AfterUpdate event procedure we'd put this:
Me.UnitPrice = DLookup("UnitPrice", "Products", "ProductID = " & ProductID)
Me.VATRate = DLookup("VATRate", "Products", "ProductID = " & ProductID)
As values have been inserted into these controls the values in the
underlying fields will remain static if the UnitPrice or VATRate in the
Products table is changed. It can also be edited manually if necessary. In
fact its not even necessary to have then as controls on the form so long as
they are in its underlying recordset.
The Qty control is a simple text box in which a number is entered.
The computed TotalNetPrice and TotalGrossPrice controls get their values by
means of the two functions. These can be called directly form each control's
properties sheet by entering the following function calls as their
ControlSource properties:
=GetNetPrice([ProductID],[UnitPrice],[Qty])
=GetGrossPrice([ProductID],[UnitPrice],[Qty],[VATRate])
I hope the above hypothetical example has given you an insight into how this
sort of thing can be done using VBA. Don't hesitate to post back if you need
any clarification as to how to implement something similar in your database.
I'm actually off to Paris around 24 hours from now, however, and while I'm
there have no intention of going anywhere near a computer, so if you don't
hear back from me I'll be back next week and will be able to pick up the
thread again then.
Ken Sheridan
Stafford, England
LM said:
I am very new to MS Access.
I have a form that updates one table. I have a total_cost field on the form
that I want to compute the total based upon a lookup of other table(s) field
values based upon what is in a couple of the forms fields at the moment .
Then taking the values plug them into a formula somewhat like below, and
update the field total_cost in the table. I want to update the table with
the calculated total_cost as opposed to always doing a calculation on the fly
because I want the record to relfect what the total_cost was at time record
entered into db.
PricePerInch * TotalInches * qty = total_cost
I have played with the expression builder and it does not seem to offer me
the ability to lookup data in related tables, assign value to variables then
plug into the formula, to then save back to the field in the table that the
form field is bound to.
So do I look at a macro or do I need to learn how to use the VB
functionality - and if VB how do I relate the VB code to the specific field
total_cost (I guess it is called a control on the form).
Thanks for any suggestions to point me in the right direction or even URLs
to info on what I need to learn.
