desktop database application development tools, Microsoft Access has
very robust facilities for creating forms. For reporting applications
you will use yourself you may not need many forms. But if you are
setting up a database for someone else to use you will want to create
a set of forms for data entry, report selection, etc.
The purpose of
this article is not to present a tutorial on how to design a form.
There are many good sources of information on that. Rather, I want to
focus on the effect that table relationships and queries have on the
basic structure of the forms. Along the way we'll see just how much
of the legwork can be done for you by the Access form wizard,
especially if you have properly designed your tables, queries and
relationships. So let's cover a few basics first.
You can download the Access starter database,
box.net. This is one of the databases that can be used as a basic starting point in our classes, both onsite and classroom.
create two types of forms: bound and unbound. A bound form is
connected to a table or query via a record source property. An
unbound form has no record source. It can be used to collect
criteria, such as a date range, for reports, searches, etc. This
article focuses on bound forms.
Each form has
one (and only one) record source. This is an important point since it
would seemingly imply that a form can be used to update only one
table. But this is not so. A query which draws from more than one
table can also be used. If it is updatable then you can update
multiple tables via one form (more about this later). In the example
below we are using a work_orders table. It could just as easily have
been a query.
Another way to
update multiple tables is through subforms.
Access has the
ability to create subforms. These are similar to regular forms and
have their own record source. Once created they can be embedded in or
linked to a main form and Access will automatically maintain the
relationship between the data in the two forms.
of this article will discuss form creation in light of existing
tables, relationships and queries. We'll start with tables and then
delve into using queries.
Data Entry Forms
You did do a
thorough job of setting your field properties, didn't you? If not go
back and finish this job now. The Form Wizard will use the caption
property to generate the field labels. If you've set these already
then you will automatically get meaningful labels on your forms with
no further work.
A simple data
entry form is all that's needed to update many of the tables in your
database. For example customers, suppliers, vendors, parts,
locations, etc. You should have a data entry form for each of these.
It won't handle your transactions or fancier displays but we'll get
to that later. Here's a real simple form for updating employee
information for a sample database.
record source. This form was created simply by running the Form
Wizard, selecting the employees table as the record source and using
the columnar format. Of course I added a few things and moved a field
or two, but basically the wizard did all the work. Do note that you
should also set the caption property for the form.
Name your form
with a prefix of frm. For example: frmEmployees.
to one-to-many forms.
Submit the form below to be notified of new articles or other
Microsoft Access resources made available...
Learn more about our Microsoft Access courses..