ccess, the Microsoft database,
although very powerful and flexible, is hardly the
friendliest tool in the Office suite. Even
experienced users have been known to fume over its
complexity. So what do you do if your organization’s
customer lists and inventory data are stored in
Access and you want that important information to be
easily accessible to staff without investing in
hours of training?
Key to Instructions
To help readers follow the
instructions in this article, we used two
different typefaces:
Boldface type
is used to identify the names of
icons, agendas and URLs.
Sans serif type indicates the
names of files and the names of commands
and instructions users should type into
the computer.
| The answer is
Switchboard , a push-button
feature built into Access that was designed to
make Access easy to use. Switchboard
is an interface between users and those
esoteric forms, reports and tables that make up
the Access database. With it in place, novice
users never even see the innards of Access: All
the critical database functions are displayed on a
simple opening screen and each is executed by
clicking on appropriate buttons. Not only do the
buttons make it fast and convenient to use Access,
but because no one but the real technicians get to
tinker “under the hood,” those complex tables and
forms are hidden from self-appointed experts who
otherwise may try to “improve” the database.
In this article I will take you through a
typical Switchboard setup. After
you’ve done it once, you can easily create a
custom design for your own organization or client.
I’ve prepared an example, with which you can
follow along as we build the interface. To
download the file, go to
www.aicpa.org/download/pubs/jofa/2004_07_glandon-example.mdb
. Once it’s downloaded, open it and
click on Tools , Database
Utilities and Switchboard
Manager . You’ll notice that
Switchboard has a
one-size-fits-all design, and while it may work
for your organization, you will have the option of
creating a fully customizable interface. Begin by
creating a blank form by selecting Forms
from the list of Objects
and then click on New
(exhibit 1 ). Now select
Design View and click on
OK ( exhibit 2 ).
To change the background of the form by using
one of the available formats, click on the
Form Selector and the
AutoFormat icon on the toolbar
( exhibit 3 , below). That will
bring up the AutoFormat screen
(exhibit 4, below).
Select a preformatted design from the left-hand
column and click on OK .
To add command buttons to this form, click on
View, Toolbox . Select the
Command Button once and click
inside the form. You will be able to change button
size and position later ( exhibit 5 ,
below).
Note: The Control Wizards
on the Toolbox must be
active (depressed) in order for the wizard to open
when you draw the command button on the form (
exhibit 6 ).
When the Command Button Wizard
opens, select Form Operations
and Open Form and click
on Next ( exhibit 7 , below).
Select Customers and click on
Next ( exhibit 8 ,
below).
Accept the default option: Open the
form and show all the records . Click
on Next ( exhibit 9 ,
below). Change text to read Enter
Customers . This will be the caption on the
button. Then click on Next (
exhibit 10 , below).
Name the command button cmdEnterCust . Be aware
this is not a caption; it only gives the button a
name, allowing you to identify it later if
necessary ( exhibit 11 , below).
Finally, when you click on Finish
, your screen should look like
exhibit 12 .
Save the form as Main by clicking on
File and Save
. Enter Main where the default name
Form1 appears and click on
OK . Switch to Form
View (by clicking on View
and Form View ). Click
on the Enter Customers button to
test it; the Customers Form
should appear. Return to
Design View (by clicking on
View and Design View
). You can resize the button by selecting
it and then adjusting the handles that appear
around the edges. Create a button designed
to enter inventory by using the same procedure as
above. Be sure to select the Inventory
Form for the Enter Inventory
button in the Command Button
Wizard .
A BUTTON TO CREATE A QUERY
Now we’ll create a query button
to display the data in the Customer
Invoice Listing report that we’ll use
later. In the Design view, click
on the Command button in the
Toolbox to bring up the wizard.
Select Miscellaneous and
Run Query as shown in
exhibit 13 , below, and click on
Next . Select the
Customer Invoice Listing (
exhibit 14 ) and click on Next
. Name the button cmdRunQuery .
At this point the Database Manager
should resemble exhibit 15 ,
below. Now we’ll create buttons to print
reports: Click on the Command
button in the Toolbox to
bring up the wizard, select Report
Operations and either Preview
Report for a screen display or
Print Report for a paper copy (
exhibit 16 , below).
Complete the button by clicking on Next
and choosing the report. Again, click on
Next ( exhibit 17 ).
Change the button caption text to Invoice
Report and click on Next (
exhibit 18 ).
Name the button cmdInvRpt and click on
Finish ( exhibit 19 ).
Repeat the steps to create buttons for
Customers Report and
Inventory Report . Be sure to
select the appropriate reports in the
Command Button Wizard:
Customers for the Customers
Report button and Inventory for the
Inventory Report button.
The final button to create is Exit Program ,
which closes the Access database. Use the
Command Button Wizard as
before, except select Application
and Quit Application as
shown in exhibit 20 and click on
Next .
Change the button caption to Exit Program and
click on Next ( exhibit 21
, below). Name the button cmdExit and
click on Finish .
Finally, we’ll make the Main
form appear automatically when the
database file is opened and give it a professional
appearance. Select Tools and
Startup from the menu bar. A
dialog box will appear as shown in exhibit 22
.
Give the application the title Garden Supply
and select the Main form to be
displayed. Deselect the check box next to
Display Database Window to
prevent the Database window from
appearing when the file is opened and click on
OK . You may want to
bypass the automatic opening of the Main
form and instead access the
Database window simply by
holding down the Shift key and double-clicking the
file in Windows Explorer, which you can launch
from your desktop toolbar.
FINAL TOUCHES
To give the application a
professional appearance, open Main
form’s Design View and
click on Form Selector , then
right-click and select Properties
, making the following changes on the
Format tab: Caption:
Garden Supply Main Scroll Bars: Neither
Record Selectors: No Navigation
Buttons: No Border Style: Thin Min
Max Buttons: Nono The form should now
resemble exhibit 23 , below. To
add a header, select View, Form
Header/Footer and place your cursor on
the line between Form Header and
Detail ( exhibit 24 ).
Now hold down the left mouse button and drag
down about an inch to allow space to enter a
label. Go back to the Toolbox ,
click once on the Label function
( exhibit 25 , below) and then click
inside the Form Header to enter
Garden Supply Main Menu . You can change the font
and size by using the icons on the formatting
toolbar. To make the buttons a uniform
size, hold down Shift and select each button with
the mouse. On the menu bar, select Format,
Size, To Tallest ; then select
Format Size, To Widest . Click
anywhere on the form to deselect the buttons. Then
use the mouse to arrange the buttons, or try the
Align and Spacing
options under the Format
menu. Finally, save the form and
close the database. When you reopen it, the
Main form should automatically
appear without the Database
window. Exhibit 26 , below, shows
the finished product.
There are many additional features you can add.
For example, you can create a command button to
open a specified Excel workbook. Or one of your
buttons can open Word or Excel. As you can see,
the opportunities to customize Access to suit your
unique needs are nearly limitless, and now you
have the knowledge to put the database to work for
you. TERRYANN GLANDON, CPA, PhD,
is an assistant professor of accounting at the
University of Texas at El Paso. Her e-mail address
is
tglandon@utep.edu . |