Microsoft Access for Beginners
- Additional Information
Advanced Form Design
Custom Menus and Tools
When using the
sample database included with this series, you might notice that
the tool and menu bars at the top of the screen look different than normal.
This is because in addition to designing forms for the database, I also designed
a custom menu and toolbar as part of the interface. If you right-click anywhere
on these bars, the popup menu should contain a Customize command that will take
you to the menu screens where you can pick and choose which buttons and menu options
you want to show to users. This can be very useful both for hiding potentially
dangerous options from inexperienced users and providing custom functions that will
automate various tasks in your application.
Office 2007 introduces the Ribbon which mostly replaces the menu and toolbars in
Access. Access still has a Quick Access toolbar, however, which can be customized
to include the controls you need. When using the demonstration database, you'll
find the custom menu and toolbar shown under the Add-Ins menu.
(Click on the image to see the equivalent screen from Access 2007.)
Under the Toolbars tab in the Customize window, you'll see a long list of specialized
toolbars that can be turned on or off using the checkboxes next to them. At
the bottom of the list are the custom bars that I designed.
Custom utility bars are part of the application and are not dependent on the forms.
You can specify different menu and tool bars for different forms and reports.
In this way, you could design separate custom bars based on the needs and functions
of specific sections of your program. If you import a form from one database
file to another, you will see an option to import whatever custom utility bars are
referenced by it. If you don't want to specify a menu bar on every form you create,
you can also name one menu bar as the default in the application startup options
and it will appear throughout the database, even when viewing the database window.
A third type of custom menu is a popup menu. This is the menu that appears
when you right-click on a form or another object. Again, this menu can be
customized to meet the specific needs of the application.
My personal use of custom utility bars is mostly limited to removing unneeded or
confusing options but as you work with Access, you may find the ability to customize
these menus to be a powerful part of application design that you wouldn't want to
be without. It is one more tool that you can use to make the application your
own.
Form Events
In addition to properties that affect appearance and other aspects, forms and other
objects also have events that are triggered by user actions or other events.
These include events such as On Current (the event that runs every time the user
moves to a new record) and On Open (run when the form is first opened). The
events on the form's property list can hold VBA code or macro references that can
be used to change other form properties or perform various actions when a specific
event occurs. A number of events can and often do occur in rapid succession
in what would appear to the user to be only one event. For example, when you
click on the button on the Welcome screen that opens the Employee form, it's Click
event is triggered. This event contains code that opens the Employee form.
When the form is opened, the following events occur between the time the command
is issued to open the form and the time you see the form on the screen:
Open →→ Load →→ Resize →→ Activate →→
Current
You can see all of these events listed on the form's property sheet where you can
get more information by clicking on any of them and pressing F1. For right
now, just understand that these events are always going on in the background and
Access makes them available to give you precise control over how your application
operates. For this reason, Access programming is referred to as event-driven.
Tab Order
The tab order screen showing the controls on the main Employee form.
One of the properties of your form that determines it's behavior is the Tab Order.
This one can be seen in design view by clicking on the View menu and then Tab Order.
This shows the order in which focus will move between the controls when you press
the Tab key. The property sheet for each control contains the Tab Index and
Tab Stop properties which determine if and when a control will receive the focus.
Another short example of how I use form events to solve a problem is found on the
tab control in the center of the Employee Data form. Each page of a tab control
has its own independent Tab Order and the tab control itself has a place in the
form's Tab Order. This means that at some point, the Tab key will take you
to the first available page on the tab control and then the Tab Order for that page
will go into effect. The only problem is that the page's Tab Order will not
normally take you from one page to another. Once you reach the last control
on the tabbed page, pressing the Tab key will move you away from the tab control
and back to the next field on the main form. Pressing CTRL-TAB will move you
between the pages of the tab control but it would be nice if there was a smooth
flow between the pages.
In design mode, you'll notice a small blank rectangle on each of the tab pages except
for the last one. These are actually small command buttons on which the Visible
property is set to False so that the user doesn't see them and can't click on them.
They are the last item in the Tab Order of their respective pages. If you
look at the events for these buttons, you'll see that the Got Focus event for each
has a single line of code that moves the focus to the first control on the next
page.
Private Sub cmdTab1_GotFocus()
Me.Contact.SetFocus
End Sub
In the code above, the GotFocus event of one of these buttons is fired when the
user presses the Tab key and the button receives the focus. The code passes
the focus to the first control on the next page and the tab control appears to switch
smoothly between pages.
Other examples of event-driven features:
- On the Additional page of the tab control, the bottom half of the page contains
a subform for information on past employees. This form is only visible if
the Current Employee field on the first page is clear. When the user updates
this checkbox or moves to a record for a previous employee, the form becomes visible
and focus automatically changes to the subform. Look at the code under the
Current Employee field's After Update event and the form's On Current event to see
how this is done.
- The program manages and displays the appropriate forms by using the Close and Exit
commands. The Welcome form also uses the Tag property to identify it as a
background form to be displayed if no other work forms are open.
Back to Top
Advanced Features
As I've indicated, Access offers a lot of flexibility in its design features so
that you can build a program that will meet your exact needs. The features
that I've described here only scratch the surface of what can be done with forms
and other objects.
The final item I want to mention is the employee photo field on the Employee Data
form. Storing photos or other images in Access is a challenge because of the
way the program stores the image information. Access has an OLE Object field
type that will store any kind of file, including other Office documents, Acrobat
files and images. For binary files like Word and Acrobat, this works great.
For image files, however, Access stores the image in an uncompressed format. In
other words, an image that takes up less than 50 KB in compressed JPEG format on
disk takes up well over a megabyte when inserted into the Access table. That's
over a megabyte for every picture stored. The database file would get huge.
A common solution that I use here is to store images on disk instead and store the
path information of the image file in the database. The photo field that you
see on the form is actually an unbound object field and the photo location field
above it is bound to the text field in tblPersonnel that stores the path information.
The Show Employee Pictures button under the object field uses VBA code to switch
between showing and hiding the photos. The field displays the image without
taking up room in the database.
The ability of Access to link to information as well as store it is an important
feature of the program that's not limited to object fields. An Access application
can link to tables in another Access database or even on a server database such
as Microsoft SQL Server to provide data for editing and analysis. Most of
the applications that I've designed have been split into a front-end and back-end
where the back-end contains all of the tables and the front-end contains everything
else along with links to the tables in the back-end. Among other things, this
separates the data from the programming and enables easier updating of the program.
An Access application can also act as a front-end for network databases like SQL
Server. Conversely, other programming languages such as Visual Basic can be
used to build a front-end for a set of Access tables.
Back to Top
Working Out the Bugs
It's a rare occasion when anything I'm designing works exactly right the first time.
At the very least, some tweaks are needed here and there to get the right result.
Sometimes the process can be much more involved and can take hours before a form
or function works the way I need it to. During that time, I'll be making changes
here and there, consulting technical forums and help files and almost always learning
something I didn't know before. Another rarity is a project where I don't
learn at least two or three new and fun things I can do with Access. Discovery
is half the fun, after all.
The more time you spend testing and debugging a program, the better. Every
hour that you spend testing means at least one less potentially embarrassing problem
for the user to discover. Entering pages of test data and coming up with test
scenarios can be tedious and sometimes Access can seem to have its own obstinate
personality when that one function you know should work doesn't. Debugging
is essential, however, if the interfaces you design are going to meet the user's
needs.
Of course, beyond the process of debugging, there's the time that you spend looking
for ways in which to improve the application. One saying that's stuck with
me over the years is "That which is 'good enough' seldom is." Your willingness
to look for improvements in your programs for the benefit of the users, even when
you don't believe they're strictly necessary, makes the difference between designing
a barely adequate program that eventually has to be replaced at great expense by
a professional programmer and providing a great solution that is praised by the
people who see and use it.
Return to Series Index