Make Your Own Push-Button Database

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

Exhibit 1

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

Exhibit 3

Exhibit 4

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

Exhibit 5

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

Exhibit 7

Exhibit 8

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

Exhibit 9

Exhibit 10

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 .

Exhibit 11

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 .

Exhibit 13

Exhibit 14

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

Exhibit 15

Exhibit 16

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

Exhibit 17

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 .

Exhibit 19

Exhibit 20

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 .

Exhibit 21

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

Exhibit 23

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.

Exhibit 25

Exhibit 26

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 .