Changing Unbound textbox to Bound

C

Christie

I am trying to add to an existing form in Access 2003 and used the help
module but cannot get this to work. When I click to add a text box it is
automatically unbound, however, I cannot figure how to either create boxes
that are bound or change the ones I just created from unbound. I need the
text boxes not to enter the information for each new customer so that I can
customize the data in each of the boxes for the customers. Any suggestions??

Thank you!
 
W

Wayne-I-M

Open the form in design view
Select View - Field List
Drag the Bound Control on to the form where you want it.
 
K

Ken Sheridan

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
 
J

Jeff Boyce

Christie

For a form's controls to be bound, the form needs a data source. What
table, query or recordset is "feeding" your form?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Joined
Nov 28, 2015
Messages
1
Reaction score
0
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

"Christie" wrote:

> I am trying to add to an existing form in Access 2003 and used the help
> module but cannot get this to work. When I click to add a text box it is
> automatically unbound, however, I cannot figure how to either create boxes
> that are bound or change the ones I just created from unbound. I need the
> text boxes not to enter the information for each new customer so that I can
> customize the data in each of the boxes for the customers. Any suggestions??
>
> Thank you!



Very well-said Ken!

Thank you for sharing your brain. I am in the middle of designing my own grocery store database and you help me a lot by sharing this idea.

But speaking of after update event, I have a subform named OrderDetails and a Product table which has StockLevel field. How could I automatically deduct quantity to StockLevel field in Product table by updating data in quantity field and product field in OrderDetails subform?

Appreciating your generosity..

Oslec
Philippines
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top