| Creating a form with wizard | Creating a blank form | Adding calculated controls |
| Formatting Controls | Adding headers and footers | Summary |
| Exercise |
Learning Outcomes
On completion of this chapter you will know:
·
How to create a blank form
·
Components that can be added to
a form
·
The concepts of properties of
form and form components
·
How to attach a form to a table
·
How to associate form
components with fields in a table
·
How to use form components for
facilitating data entry and data validation
Introduction
A form is a device for looking at the
records of a table, one record at a time.
This has a number of advantages. Firstly we are not distracted by data
in the records before or after the one we are looking at. Secondly we may not
be interested in looking at all of the fields of the record – only at certain
ones. If this is the case then we can
ensure that only the fields we want to look at are displayed on the form.
There are two ways of creating forms:
manually or by using a wizard. The wizard is very quick and also very easy to
use and learn. It also has a problem in
that it will create a form that will do 90% of what you want to do, but in
order to get the form to do the other 10% you must be able to manually tweak
the controls. For this reason we shall
look at creating form both using the wizard and also manually.
Creating a Form using the Form Wizard
To start the form wizard click on the Create tab and in the Forms group click
on Form Wizard. This will start the first step of the Form Wizard which is
shown below in Figure 3‑1
Figure 3‑1
Our first task is to select the table or
query that the form is to be based on.
As we have only one table at the moment called Students we select that
table from the combo box labeled Tables/Queries.
Once we do this the names of the fields in that table appears in the box
labeled Available Fields. Between this box and the box labeled Selected Fields are two buttons, one
with a single arrow and another with a double arrow. The two boxes and the two buttons help us to
select the fields from the table we want in our form. If we click the single arrow button then the
selected field in Available Fields
will be sent across to the box Selected
Fields. If we click on the double
arrow button then all of the fields in Available
Fields will be sent across to the Selected
Fields box as shown below in Figure 3‑2
Figure 3‑2
Once we have all of the fields we want on
our form in the box Selected Fields
we click on Next. This brings us to the
next step of the wizard shown in the left panel of Figure
3‑3
below.
Figure 3‑3
Both the left and the right panels above
are parts of the wizard where we select the layout and style of our forms. As a beginner simply leave the default
offered to us in both cases and click on Next.
Figure
3‑4
After we click Next on the styles stage we come
to the final stage of the wizard above.
Here we select a name for our form and then click on Finish.
This will now bring up our form as shown in Figure
3‑5.
Figure 3‑5
Here we see that all of the fields in the
table have a corresponding text box on the form and that the names of the
fields have become captions for the text boxes.
Also notice that the Province field is represented by a combo box
instead of a text box. This is because
the wizard picked up the fact that this field has a lookup for its values. Consequently the wizard put a combo box in
the form and added the values in the lookup’s value list to it. Here the combo box is opened, showing the
other possible values for the field Province.
Creating
a blank form
Creating a blank form is very similar to
creating a blank table. We simply go
to the Create tab and from the
Forms group click on Blank Form. This gives us a display as shown in Figure
3‑6.
Figure 3‑6
As well as the form a panel appears to the
right of it with an icon labeled Show
all tables. If we click on this icon
it displays a list of the database tables and their fields as shown in Figure
3‑7. As
we have only one table that is all that is showing here along with its fields.
Figure 3‑7
In order to add fields to the form we
simply drag the names of the fields we want across to the form. In this case there is no need to switch
between Design and Form views as the form stays permanently in Form view and
the value of the fields are instantly available once the field has been dragged
across to the form.
Figure
3‑8
Adding calculated
Controls to a Form
As well as displaying the fields of a table
a form can also be used to display data that does not exist in the table but
is calculated from that data. Here
we shall use two examples of data that is calculated from existing data. Firstly we shall add a textbox to the form that
will calculate the amount owning by a student. This will be calculated by subtracting the value
in Fees Paid from 1200. Secondly we
shall have another text box which will show “Paid in Full” if the value in
Fees Paid is 1200, otherwise it will show “Money Owing”.
Figure 3‑9
To begin with we must have the form in Design View. To do this we right click
on the form’s tab and from the pop up menu select Design View. This will give
a display as in Figure 3‑9
above. Notice that once we change into
the Design View of a form that the
tabs for form design appear. From the Design tab we select the text box tool
and with it draw a textbox beneath the combo box for Province. Access
automatically creates a label to go with the textbox. In the label we simply enter “Amount
owing”. In the textbox, which initially
is showing Unbound, we enter a
formula for calculating the amount owing i.e. =1200 – [Fees Paid]. This
simply means to subtract the value in the textbox Fees Paid from 1200.
For our text comment we draw another text
box. In this case we can delete the accompanying label. In the new textbox we
enter an IIF function. This will be =IIF([Fees
Paid]=1200,”Paid in Full”,”Money owing”).
The general format of the IIF function is =IF(condition,true,false)
Condition refers to a
statement whose value can be either true or false.
True refers to what we
do if the condition is true.
False refers to what
we do if the condition is false.
In the real formula [Fees Paid]=1200 is the condition. This may be either true or false. Thus if the value in the field Fees Paid is 1200 then [Fees Paid]=1200 will
be true. On the other hand if the value
in the field Fees Paid is not 1200
then the condition [Fees Paid]=1200 will be false.
“Paid in Full” is the true part of the
function. This is what is displayed in
the textbox if the value of the condition is true or in other words if the
value in the field Fees Paid is 1200.
“Money owing” is the false part of the
function. This is what is displayed in
the textbox if the value of the condition is false or in other words if the
value in the field Fees Paid is not 1200. Figure
3‑10 below
shows the two textboxes with their formulae. This of course is in Design View. Figure
3‑11 shows
the same form in Form View, where
instead of seeing the formulae we see the result of their calculation.
Figure 3‑10
Figure 3‑11
Formatting Form Controls
To finish off our form we shall add some
formatting to its controls. Again we
must be in Design View. Once we
are here the Form Design Tools
tabs become visible as shown below. In
this instance we select the Format
tab. The first set of formatting is to apply to all of the text boxes on the
form. The font and size of the text is to be changed
to Times New Roman, size 12, bold. The
font colour is to be black. The background is to
be yellow. To do this we select those
text boxes. Next we select the Format tab and in the Font group select the font name and size.
We select the Bold icon. In the font colour
icon we select black from its drop down list.
Similarly we select yellow from the drop down list of the background
colour icon.
For the labels we format the text to Times
New Roman, size 12, normal.
Figure 3‑12 below
shows us how our form will look in Design
View while Figure 3‑13 shows
us the form in Form View. In clarity this is a great improvement on
that shown in Figure 3‑11.
Figure 3‑12
Figure 3‑13
Creating Form Header and Footer
Headers and footers on forms are similar
to those in word processing. Their
purpose is to display general data that does not fit into the area of the
main form itself. Both header and footer can be turned on or off
by ensuring that we are in Design View
and then right clicking on the form itself. This brings up the pop up menu shown below. Clicking
on Form Header/Footer puts extra
space at the top and bottom of the form which are labeled Form Header and Form Footer.
Figure
3‑14
Figure
3‑15
Above we have inserted a label in both the
header and footer, with the purpose of the form in the top label and the name
of the school in the bottom label. In Figure 3‑16 below
we see the altered form in Form View.
Figure 3‑16
Summary
A form is a device for presenting the contents
of a table one record at a time. It has two main views – Design View and Form View. In the Design View we specify which table the
form is to be linked to, what fields the controls of the form were to be linked
to and what type of controls we would be using.
We specify which table the form is to be linked to by using the Record Source property of the form. Once
this property has been changed to the name of a table, Access automatically
provides us with a list of the table’s fields.
All we need to do is to drag the names of the fields onto the form
and the system automatically creates a text box and links that text box to
the field. If we wish to use List boxes
or Combo boxes, wizards will automatically guide us through the steps of setting
up those controls so that they display a list of values and that they are
connected to a field in a table.
Exercise 3
Create a form for the table you created
in Exercise 2. The controls for the
fields will be text boxes apart from the fields Publisher,
Topic and Status.
The control for the field Publisher will be a combo box. Its drop
down list will have the values “Penguin”, “Pelican”, “Puffin”, “Macmillan”, “Hodder & Stoughton”, “Oxford University Press” and
“Allen & Unwin”.
The control for the field Topic will also be a combo box and its
drop down list will have “Literature”, “History”, “Geography”, “Science”, “Education”. The control for the field Status will be a list box that will have values “On shelf”, “On
Loan” and “Missing”.*