Make your own push-button database: your information is just a click away.

Access, 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?

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_glandonexample.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, page 78).

[ILLUSTRATION OMITTED]

Now select Design View and click on OK (exhibit 2, page 78)

[ILLUSTRATION OMITTED]

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).

[ILLUSTRATION OMITTED]

That will bring up the AutoFormat screen (exhibit 4, below).

[ILLUSTRATION OMITTED]

Select a preformatted design from the left-hand column and click on OK.

To add command buttons to this form, click oil 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).

[ILLUSTRATION OMITTED]

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, page 79).

[ILLUSTRATION OMITTED]

When the Command Button Wizard opens, select Form Operations and Open Form and click on Next (exhibit 7, below).

[ILLUSTRATION OMITTED]

Select Customers and click on Next (exhibit 8, below).

[ILLUSTRATION OMITTED]

Accept the default option: Open the form and show all the records. Click on Next (exhibit 9, below).

[ILLUSTRATION OMITTED]

Change text to read Enter Customers. This will be the caption on the button. Then click on Next (exhibit 10, below).

[ILLUSTRATION OMITTED]

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).

[ILLUSTRATION OMITTED]

Finally, when you click on Finish, your screen should look like exhibit 12, page 81.

[ILLUSTRATION OMITTED]

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.

[ILLUSTRATION OMITTED]

Select the Customer Invoice Listing (exhibit 14, at right) and click on Next. Name the button cmdRunQuery.

[ILLUSTRATION OMITTED]

At this point the Database Manager should resemble exhibit 15, below.

[ILLUSTRATION OMITTED]

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).

[ILLUSTRATION OMITTED]

Complete the button by clicking on Next and choosing the report. Again, crick on Next (exhibit 17, page 83).

[ILLUSTRATION OMITTED]

Change the button caption text to Invoice Report and crick on Next (exhibit 18, page 83).

[ILLUSTRATION OMITTED]

Name the button cmdInvRpt and click on Finish (exhibit 19, page 83).

[ILLUSTRATION OMITTED]

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, page 83, and crick on Next.

[ILLUSTRATION OMITTED]

Change the button caption to Exit Program and click on Next (exhibit 21, below).

[ILLUSTRATION OMITTED]

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, page 84.

[ILLUSTRATION OMITTED]

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: None

The form should now resemble exhibit 23, below.

[ILLUSTRATION OMITTED]

To add a header, select View, Form Header/Footer and place your cursor on the line between Form Header and Detail (exhibit 24, at right).

[ILLUSTRATION OMITTED]

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.

[ILLUSTRATION OMITTED]

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.

[ILLUSTRATION OMITTED]

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.

Key to Instructions

To help readers follow the instructions in this article, we use 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.

TERRYANN GLANDON, CPA, PhD, is an assistant professor of accounting at the University of Texas at El Paso. Her e-mail address is [e-mail].