Microsoft Access for Beginners - Part IV
Designing the Forms
Contents
Introduction
A Word about VBA
Form Design Toolbar
The Welcome Screen
Form Design Basics
Managing your Data
Additional Controls
Conclusion
Introduction
In Parts I and I!, I showed
you how to build the foundation of your database application with properly designed
and related tables. Then I showed you how queries can read and manipulate
the data you've stored in your tables. Now you need a way for the average
user to quickly access and edit the data when needed. As wonderful as a properly
designed set of tables and queries is, the database window does not provide a great
interface for someone who knows little about the structure of your database or even
for you on a regular basis.
The word 'interface' is important because that is what you are designing when you
create a data entry form. A well-designed application interface does the following:
- Provides easy access to the features of the program
- Simplifies data entry and retrieval with fields that anticipate, where possible,
the values to be entered
- Helps to ensure correct data entry and protects existing data by providing templates,
masks and validation rules that require items such as dates to be entered correctly
- Anticipates user error where possible and prevents or corrects for it
- Offers as much compatibility as possible with accepted design standards and user
expectations so as to minimize the learning curve required to use the program
- Conforms to the user's natural workflow so that data can easily be entered and updated
in the relevant order
This represents the ideal that you can work toward as you gain experience with Access
and other programming tools. When you design an interface with these goals
in mind, your programs are more likely to be readily accepted by the users and you
will have an easier time training people on how to use them.
I design my applications so that the average user will never have to directly open
a table or even view the database window. All work should be completed through
the forms and menus. This reduces errors and the chance of a necessary form
or query being accidentally deleted or records being improperly deleted from a table.
It's not necessary to completely prevent access to the database window except in
the most secure applications as some users may be qualified to add their own queries
and tools. You should, however, do everything you can to minimize the confusion
for those who aren't.
Back to Top
A Word about VBA
For me, any discussion of forms includes references to
Visual Basic for Applications (VBA), the programming language that Access uses to provide extra functionality
behind forms and reports. In this article and the supplemental
article on advanced form design,
I make references to the VBA code that I've attached to the form objects in the
sample database. You don't need any actual VBA knowledge to benefit from this
chapter beyond the understanding that code can be attached to objects to carry out
tasks when a button is pressed or other events are triggered. I do recommend
that you look at these examples as a first introduction to the use of VBA with Access
and start learning the language as soon as possible.
For an introduction to Visual Basic for Applications, check out the series
Programming Microsoft Access, a supplement to this series that explains
VBA and some general programming concepts in more detail.
Back to Top
Sample Files
If you have not already done so, please download the demonstration files:
Demo.zip - Access demonstration database and original Excel spreadsheet
(These files require Access and Excel 2000 or later.)
Form Design Basics
Data Sources
Creating forms from scratch is about two things: learning how to use the different
controls available and how to link the data to them. Every form has a Record
Source property which determines where the data comes from. This can be a table,
a query or a SQL statement. The screenshot below shows part of the properties
sheet for the main employee form from the demo database with the Record Source holding
a SQL statement.
The record source of a form specifies where the data comes from. It can be a SQL
statement or a separate query or table. Clicking on the Build button (
) on the right of the record source field will bring up the query
window to show you the query behind the form. The text for this query is stored
completely within the form rather than as a separate query in the database.
The record source can also hold the name of a table or a separate query that will
function as the data source. If it does, then all of the records returned
from that source will be available to the form. Using a SQL statement as shown
above or a separate query is one way to limit the number of records shown. The form
works with the record source to determine which record is currently displayed and
to manage the transfer of data between that record and the controls on the form.
Another word for the collection of records made available to the form is a recordset. This recordset is sorted according to the
specifications of the query or the order in which the records are held in the table.
The controls you place on the form such as text boxes, combo boxes, radio buttons,
etc. access individual fields from the recordset. Each control has a Control Source
property which determines which field it will work with. In the figure below,
the properties sheet for the LastName text box is shown with the Control Source
set to the LastName field provided by the record source shown above.
Each time the form displays a new record, this control is updated to display the
content of the LastName field from that record.
Once you understand these two properties, the rest is about placement of controls
and fine-tuning of their properties.
Creating the Form
Starting out with a new, blank form is as simple as selecting Insert / Form from
the main menu or selecting New in the Forms section of the database window.
(In Access 2007, select Form Design from the Create menu and then Choose "Add Existing
Fields" from the Design menu.)
All of the options in the New Form dialog except for the first one are wizards that
will create the new form and populate it with controls or guide you through the
steps for creating the requested form type. The selection box at the bottom presents
a list of all of the tables and queries saved to the database. I've selected
the main employee table here. Clicking the OK button with these options results
in a screen that looks something like this:
(Click on the image to see an example of this screen from Access 2007.)
Notice the secondary window in the above screenshot that shows the list of fields
available in the data source. You can click and drag these fields onto the
form to automatically add controls to the form. In the screenshot below, I've
added five of the main fields to the form and arranged them. Notice that the
form created a combo box for the Department field which uses a lookup.
When I switch from Design to Form View, the form automatically loads the first record
from the table it's using as a data source and displays the requested fields.
At the bottom of the form is the record selection toolbar which enables you to move
through the available records. As with any list, the recordset has a beginning
and an end. On this form, moving beyond the last record will create a new
blank record and the function of the controls will change. Instead of reading
and editing the data, anything typed into the controls will be written to the new
record. Depending on the needs of your application, the form can be set to
simply display read-only data and to disallow the creation of new records.
Experimenting with the other form properties available in Design View will also
help you become familiar with the many way in which you can get the most out of
your forms.
Working with Properties
As you've seen by now, every object in Access has a list of properties that you
can set to customize your application. The form designer is especially flexible
in the ways you can adjust the look and feel of your program. If the properties
screen is not already visible, pressing F4 or right-clicking anywhere on the form
and selecting Properties from the menu will bring up the list.
One of the things to keep in mind about form properties is that a form has a number
of sub-sections including the main detail area where I placed the controls shown
above as well as a header and footer section. This is in addition to all the
controls you place and a main list of properties that apply to the entire form as
a whole.
In the above screenshot, notice the combo box at the top of the properties sheet.
This control contains a list of all of the form elements including the controls
that you create. Here, it's set to the properties for the form itself.
Each element has a different list of property settings available to it. As
you select different controls or sections with this box, you will notice that they
are selected within the form design window as well. This can be an easy way
to find a specific control on an intricate form.
There are far too many properties available to detail here but you can get more
information on any of them by placing the cursor in one of the properties fields
and pressing F1 for help. The more time you spend getting familiar with them,
the more you will be able to access the power behind Access form design.
Form Design Toolbar
One of the standard toolbars that you'll use quite a bit when working with Access
is the Form Design Toolbar, sometimes called the Toolbox. If this bar doesn't
appear automatically when you enter form design mode, you can view it by selecting
Toolbars on the View menu. (In Access 2007, an equivalent set of tools is
available from the Design menu under the Form Design Tools menu when in design mode.)
This toolbar includes icons for all of the form controls that I mention in this
article plus a few more. It also has a Control Wizards option.
(Click on the image to see the equivalent controls from Access 2007.)
For the beginner, command buttons and other form controls are one of the few exceptions
that I make to what I said in the last chapter about not using the wizards.
If you have the Control Wizards activated, a dialog box will appear as soon as you
create a form control and guide you through setting up the control to perform any
one of a limited number of actions. The wizard will write all the code necessary
to carry out the action needed. For small controls such as buttons, it's fine
to use these when first designing forms until you become comfortable designing macros
and writing code. It is still important, however, to learn how to design without
them for finer control.
Back to Top
The Welcome Screen
Forms can serve other purposes besides data entry and when you first open the database,
you'll see one example of this. The Personnel Database main menu is a form
that serves as a welcome screen for the application. Right now, it only contains
a few command buttons for navigation but it could also feature a company logo and
additional information such as copyright notices and instructions. In the
application startup options, you can specify a form to load as soon as the application
starts. This enables you to define a welcome screen like this one or run code
that is attached to a form to carry out almost any task at startup.
(Click on the image to see the Access 2007 version of this screen.)
The figure above shows the startup settings screen for the sample application.
You can view this screen by selecting Startup from the Tools menu. (In Access 2007,
it's in the Current Database section of the Access Options screen.) This dialog
holds settings unique to the application you're designing. I've set an application
title that will display at the very top of the screen while the application is loaded.
I've also set the Welcome form as the form to be displayed at startup. The
option directly below this, Display Database Window, specifies if the window will
be shown or hidden. I've set it to be hidden but you can see it whenever you
need to by pressing F11.
Sometimes you might want to override these settings if you're making changes to
the database. If you hold down the SHIFT key as you're opening the database,
this will bypass all of these properties and give you immediate access to the database
window for the application. There is also a way to disable the SHIFT-key bypass
for especially secure applications but this is a subject for another chapter and
something that should obviously be used with care.
Back to Top
Managing your Data
If you click on the Manage Personnel Data option on the Welcome screen, the program
brings up the Employee Data screen where you can view the sample data included in
the database. As you look through this form, you'll see fields from many of
the tables that were mentioned in the previous chapters. In addition to the
main Personnel table, there's contact, department and and skills information for
each employee. This is accomplished in two different ways.
First, instead of being based on a single table, the form is based on a query.
If you open the form in design view, view the properties of the form and then view
the Record Source property, you'll see it contains a SELECT statement. This
is a query on six different tables with tblPersonnel being the table that links
them all together. In Part I, I mentioned the importance of limiting the fields
in each table to those directly relevant to the table's subject. Here, you
can see how the form can be used to bring all of that data back together in a coherent
way.
Another thing you may notice about all the tables in the record source is that they
are all based on one-to-one relationships with tblPersonnel. None of these
tables contain more than one record for each employee in the system which is why
they can all be included in this form which shows one 'record' for each employee.
There are other tables, however, that contain multiple records for each employee
such as tblSkills and tblNotes. To include these, we need another method of
sharing the form.
A subform is a form that can reside as part of another form. These parent
and child forms use the same relationship as the tables they are based on.
In this case, the Skills and Notes subforms are related to the main form by a one-to-many
relationship between tblPersonnel and their respective tables. You define
this relationship when you add the subform to the main form. Every time you
move to a different employee record, the subforms are updated to show the relevant
records for that employee.
These subforms can either be created independently of the main form and then added
or you can create them with the subform / subreport button on the toolbar.
In either case, they are saved as separate objects in the database. Using
subforms, you can add, edit and delete multiple records in the related tables as
needed.
Back to Top
Additional Controls
The Employee Data form has a good sampling of some of the controls you can use to
enter and manage data in Access. Using these very flexible controls, you can
build an interface that provides the features and protections outlined in the introduction
to this chapter. You'll probably want to have the form open in design view
as I go over some of the controls that this form uses. To view the properties
on any control, right-click on it and select Properties from the menu.
Tab Controls
This form contains a lot of fields, far too many to be placed on one flat form without
causing it to appear cluttered and disorganized. For this reason, I decided
to use a tab control to group many of the fields by subject. This control
is available on the Toolbox bar. Each one of these tabbed pages is an object
with its own set of properties and you can add and remove pages as needed from the
control. In the figure below, you can see how one of the tabs contains the
Skills subform mentioned earlier.
You can add controls to each page of the tab control in the same way that you would
add them to the form itself. Controls can also be dragged from the form to
a tab control. The tab control can hold as many tabs as you need and as you can
see in the example here, they're used to group data by subject. They are very useful
in cases like this one and there are a couple of extra features that I'll talk about
later on.
Combo Boxes
A number of fields on the Employee form are represented by combo boxes, which are
the fields with the dropdown arrow on the right side which enable you to choose
from a list of values. When typing values in these boxes, you'll notice that
they also attempt to supply possible values based on what you're typing. This
is called the Auto Expand feature. In addition to speeding up data entry, combo
boxes can also help to ensure accuracy by supplying a set number of choices for
the user to enter into the field.
To show the flexibility available through this control, look at three specific fields
under the Work Information tab; Department, Job Title and Work Status. The
names are self-explanatory but each of these controls supplies possible values in
different ways.
If you look at the Row Source property for the Department field, you'll notice that
it's actually a query. The control uses a query to pull up a list of the available
departments in the facility so that when the user enters the department for the
employee, he or she only has to start typing the department number or press F4 for
the pull-down list. The query uses a field titled "Label" which combines the
department number and name fields into one so that both can be shown. Also,
you'll notice that the Limit to List property for this control is set to Yes.
This prevents the user from entering any values that don't match what is in the
department table. All of the properties in this section affect the way this
control accesses and displays information. By clicking once on a property
and pressing F1, you can find additional information on its function and uses.
The Job Title field is also based on a query but finds its values in a different
way. This control gets the list of titles from the Job Title field in the
table and the user is not limited to the current list. Since the field supplies
the table and reads from it, every time the user enters a new title, it is
added to the list of values the control will present the next time it's refreshed.
The drawback to this approach is that it allows the user to enter variations on
the same value or misspellings. It can be used temporarily, however, to compile
a list of necessary values. Those values can then be placed into a lookup
table and the control can be changed to only allow values from that table.
Whatever approach is used depends on the needs of the application.
The Work Status field indicates if an employee is currently reporting for work or
on a leave or suspension of one kind or another. It can also be used to mark
employees who have been laid-off and are subject to callback. In this case,
there is a very limited number of values that a user should enter but unlike the
Department field, these values are not found in a lookup table. Because there
are so few of them, they can be specified in another way. Instead of using
a query to supply the values, the control uses a value list and the options are
entered in the Row Source property as string values (non-numeric values enclosed
in quotes) separated by semi-colons. The pull-down list on the control shows
these values just like the other controls did and the user is limited to using one
of the values supplied.
This type of control can be created manually when designing the form and you can
adjust the settings as needed. In the case of the Department and Work Status
fields, however, the settings were made automatically as soon as the fields were
placed on the form. This is because the lookup functions described above were
actually defined when creating the tables. If you open tblPersonnel in design
view, you'll see that the Department and Work Status fields are both designed to
get their information in the same way these controls do. When I placed the
controls on the form, they inherited the properties of the table fields that they
are bound to. This inheritance only happens when the control is created.
If you change the table field properties, the control does not change automatically.
Checkboxes
A few of the fields in the database are true / false fields or boolean fields. When
you place a boolean field on a form, a checkbox is automatically created.
When the box is checked, it equals a True value which is sometimes represented by
-1. When unchecked, it equals a false or 0 value. You can also use the
Triple State property to let the control deliver a third value to the field if necessary
which could be shown as 1.
(Click on the image for the Access 2007 equivalent.)
There is another feature that's related to the checkbox and, although I don't use
it in this application, it's something you should check out. The Option Group
is a control that groups together a number of checkboxes or option buttons to deliver
one value to a table field. You would use this when you have a small number
of optional values for a field and you want to display a list of the values for
the user to choose from. One example would be if you were storing credit card
information and the user needed to select from three or four different card types.
I generally favor using a combo box supplied by a value list as shown with the Work
Status field because it takes up less space on the form and is faster to fill out.
Sometimes, however, an option group can be the appropriate choice depending on the
application. The example below shows how the appearance of an option group
would compare to the combo box control that this application uses for the Shift
field.
Command Buttons
Command buttons are one of the most useful and versatile tools for designing forms.
The buttons on the Welcome screen were used solely for navigation while the five
buttons that you see on the Employee form also set various conditions on the form
and move it from one state to another.
A command button provides a familiar device that the user will recognize as a way
to start a task. When the user clicks on the button, it appears to act just
like a real button, moving in and out. What the user is actually doing is
activating the macro or VBA code that is attached to one of the button's event properties,
usually the On Click or On Dbl Click event. To see what I'm referring to,
choose one of the buttons and find the On Click event in the button's properties.
The words '[Event Procedure]' indicate that VBA code has been written for that event.
Then click on the Build button () that appears when you click on the property. This will
bring up the
VBA design environment in a separate window and you can view some of
the code that I've written for these buttons.
Private Sub cmdAddNew_Click()
If Me.CurrentMode <> eAdd Then
Me.CurrentMode = eAdd
Else
Me.CurrentMode = eRead
End If
End Sub
The code above is an example from the button that adds a new employee. The
first and last lines indicate the beginning and end as well as the name of the sub
and its attachment to the command button's click event. The second line is
a comment that is ignored by Access and serves to document the code. The rest
is a simple decision process which looks at a custom property I created for the
form and then sets the form to either add a new employee or to read-only mode.
Back to Top
Conclusion
My first work with Access was meant to automate some of the work that I was doing
with documentation and auditing. I had worked with other databases and the
BASIC language before so I had some knowledge of programming concepts and good practices.
I still didn't know anywhere near as much as I know now or as I thought I did then
but I managed to turn out a couple of programs that worked well and were eventually
noticed by supervisors, co-workers and even people outside the company. By
the time I left that company, I had designed several databases for various departments
and had earned a reputation beyond the limits of my own position. By learning
as much as you can about tools like Access, you can do the same.
When I was in college studying BASIC programming, I remember one of my teachers
commenting on how BASIC allows for flexibility and individual style. The logic
and methods that someone uses to design an interface in Access or another environment
can be as unique as the way they use their spoken language or do anything else.
The methods that I used above to keep the form in the right mode and accomplish
other tasks are my own. Another programmer might do it differently and still
arrive at the same result. If you work with Access over the years, you will
develop your own style based on the way you approach different problems. What's
most important in the end is that the program works well over the long-term and
meets the user's needs.
The introduction of forms and the event-driven interface in this chapter shows a
different side of Access than you saw when setting up tables and queries.
Where tables need to be setup according to the rules of data normalization for the
application to work efficiently, forms and reports allow for much more flexibility
and creativity on your part. Your next step should be to learn as much about
form design and event handling as possible so you will have the foundation to build
any application you need to. This knowledge will help you not only with Access
but with other database systems and programming environments.
Back to Top
For more information:
Return to Series Index