Firstly you'll need a column (field) in the form's underlying table to bind
the control to. Set the controls ControlSource property to the name of the
column.
I assume you are currently 'pulling' values into the unbound controls by
means of an expression as the ControlSource. You'll now have to 'push' the
value in by means of some code in an event procedure. Let me illustrate this
with a simple example. Say you have a form bound to an Orders table with a
bound control CustomerID and an unbound control DiscountRate with a
ControlSource of:
=DLookup("DiscountRate", "Customers", "CustomerID = " & [CustomerID])
If you want to be able to vary the default discount rate for the customer on
an order-by-order basis then you'd include a DiscountRate column in the
Orders table. In the jargon the discount rate is now 'functionally
dependent' on the order not the customer, while the default discount rate is
'functionally dependent' on the customer; its functional dependence which
determines which columns legitimately go in which tables, so in this case
having a DiscountRate column in both tables is correct and does not introduce
any redundancy.
So, in this example you'd set the ControlSouce property of the control to
DiscountRate. In the AfterUpdate event procedure of the CustomerID control
you'd put the following code to look up the DiscountRate value from Customers
and assign it to the DiscountRate control in the form:
Me.DiscountRate = _
DLookup("DiscountRate", "Customers", "CustomerID = " & Me.CustomerID)
As you see the expression used is the same apart from the addition of Me.
Me is simply a shorthand way of referring to the current form. Actually,
that's an oversimplification; what it really refers to is the current
instance of the class in which the code is running, but think of it in the
simplified sense and you won't go far wrong.
If you are unfamiliar with adding code to an event procedure this is how its
done:
Select the control in form design view and open its properties sheet if its
not already open. Then select the relevant event property in the properties
sheet. Click on the 'build' button; that's the one on the right with 3 dots.
Select 'Code Builder' in the dialogue, and click OK. The VBA window will
open at the event procedure with the first and last lines already in place.
Enter the lines of code between these two existing lines.
Ken Sheridan
Stafford, England
