106
3.3 Bound, unbound and computed controls
A form may be bound to a table. In this case its
controls can be bound to fields of the table, so that the
user can see the fields and update them through the
control. As an example, fsubStayList was bound to the
guest table, and we could see and update the guest data.
In this section we will look at this in more detail. We
will explicitly bind controls and let controls be com-
puted from multiple fields.
Unbound control in a bound form
1. Open fsubStayList2 in design mode.
2. Select the checkbox tool and add a checkbox to the
detail form as shown on Figure 3.3A.
3. Switch to user mode. There will be a checkbox in
each detail form, but all of them will be gray. The
reason is that the checkbox has not got any value
yet.
4. Click the checkbox so that it shows a tick. Move to
the next record. All the checkboxes have now got a
tick. Click to remove the tick. It disappears from
all the checkboxes.
As we added the checkbox, it became an unbound
control. The Yes/No value is not stored in the database,
but it is a single dialog variable in the form. All the
checkboxes show this single variable and thus show the
same. If you close and open fsubStayList2, the dialog
variables disappear and are created again. The check-
boxes are gray again.
Bound control
5. Show fsubStayList2 in design mode. Set this prop-
erty for the checkbox:
Data -> ControlSource = passport
(choose the field from the list). Change to user
mode.
This action binds the checkbox control to the passport
field in tblGuest. The first form instance shows data
from the first record in tblGuest, the second instance
shows data from the second record, etc. You can bind
the control to any field in tblGuest no matter whether it
was included when the Wizard generated the form.
In user mode, the checkboxes still look gray but that is
because they try to show the passport fields. A
checkbox can show a Yes/No value or a number (with
zero shown as No). The passport fields are either blank
or contain a text, and the checkbox doesn't know what
to show.
6. Try to check and uncheck some of the boxes.
Notice that they are independent of each other. The
Yes/No value is stored in the passport field of
tblGuest. Look at the table contents. Notice that
Yes is stored as -1, No as 0. (Sorry if some of the
real passport numbers disappeared.)
Computed control
7. Combine two database fields. A text box may be
computed from database fields. Try it with the
address text box. Set its ControlSource to this ex-
pression
= phone & address2
(Make sure to type the equal sign too.)
The equal sign changes the checkbox to a computed
control. The & is the concatenation operator. We have
asked Access to concatenate the two guest fields and
show them as a single text. Check in user mode that
this is what you get. However, now you cannot enter
anything in the address text box - Access has no place
to store what you enter.
On Figure 3.3A we have made the combined field look
a bit better with a comma and a space between the two
parts. As shown on the figure, we have concatenated
the control source from three parts, the phone field, a
text constant holding the comma and a space, and the
address2 field:
= phone & ", " & address2
Don't worry about the square brackets on the figure.
Access often adds them as a parenthesis around names
in order to deal with names that contain spaces, # and
other strange characters.
Troublesome expressions
Sometimes an expression may give strange results be-
cause the expression happens to refer to something you
didn't anticipate. Here are some examples.
8. Self-reference. Concatenating phone and address
is just an experiment. It is more useful to concate-
nate address1 and address2. Try to enter this con-
trol source in the address text box:
= address1 & ", " & address2
Access doesn't give you an error message, but in user
mode you will see the text #Error rather than the guest
data. This highly user-friendly message (;-) is in this
case caused by a self-reference:
Notice that the programmer name for the address text
box is address1 - exactly the same as the name of the
database field. Access assumes that we want to con-
catenate the address1 text box and the address2 field. In
order to do this, Access has to compute the address1
text box, but this means concatenating address1 and
address2 once again. The computation would never
stop and Access shows it with #Error.
9. Repair self-reference. In order to repair the
problem, give the address1 text box another pro-
grammer name. On the Other tab, replace the
name address1 with Address. In user mode, the
form should now look as the last form on Figure
3.3A.
40
3. Access-based user interfaces
