Microsoft Office Reference Guide

In a recent update, we discussed how to customize a database template in Access for our own purposes. This is a good way for new Access users to get their feet wet in designing their own "applications."

What's an Access application? It's just a highbrow term fir an Access database that looks so cool and works so well that you might be able to market it to someone. Like, someone sees that you know Microsoft Office and says, "You know, I have a medical office where I keep lots of different records and generate lots of reports. Can you help me out?"

If you know enough Access to put together some related tables for patients, vendors, invoices and diagnoses, you're most of the way home. But to make it really slick, you want to put in some nice little touches, such as automatic buttons and a Switchboard. The buttons automate tasks like opening forms; the Switchboard opens with the database itself, and makes the client feel important.

The Switchboard is really just another nicely designed form that opens first, displaying a logo, the name of the database, and some buttons that launch the other cool stuff.

Figure 567

Now before we get to designing and using the Switchboard, let's dig a little deeper into Access itself and take a look at its Macros.

As you probably know, you can generate macros in Word, Excel, and PowerPoint by using a Macro Recorder. Access has no such animal. Instead, Access Macros have their own specific window as objects, along with the other objects (like tables, forms, reports, queries and so on). If you click on Reports in the Objects list, you can see any macros that were previously created.

Figure 568

If you click New, you open a new Macro in Design mode, which gives you a list of actions to perform. Among the most useful are the actions which will automatically open one of the other Access components.

Let's assume that we want to automatically generate a report based on our data. We would choose Open Report from the dropdown list.

Figure 569

We still have to tell Access which report, and how it should be opened. So, in the Action Arguments panel at the bottom, we choose a Report from the drop down menu, and how it should be opened; since we want to review it before it prints, we go with Print Preview.

Figure 570

We could add more actions to the list, like a Beep or a Message Box to the user with hints or suggestions, but let's just open the report for now. When we close the macro window for the first time, we're prompted to enter a name to save the new macro; when we do, it enters the Macro window permanently.

The easiest way to use the macro is to just double-click it. In this case, it simply opens the specified report in a preview window ,so we can review it before it prints.

Figure 571

But to show off, we can run the report from a button in another object, like a form. If we open a form in the database in Design mode, we can easily create a button from the Toolbox.

Figure 572

Next, we right-click and select the button's properties, and change its caption to Run Report. (Not the button name: the caption.)

Figure 573

In the Event tab, we assign our new macro to the Click Event. (Macros automatically appear in the action drop-down list.)

Figure 574

Now, when we open the form, there's a button that instantly launches our report from our macro.

Figure 575

A Switchboard is another form that opens automatically when the database opens. We took a quick look at it when we discussed how to customize a database template in Access for our own purposes. In fact, we added a logo and changed text on our Switchboard which we got from the original template.

But what if there is no template?

We can still use the Switchboard tools in Access by clicking Tools > Database Utilities > Switchboard Manager. If there isn't one, Access lets us know and asks if we want to create one.

Figure 576

We say OK, sure, and a new Switchboard (form) is created for us just as we had with the template.

You can use the Switchboard Manager to customize which forms open for data entry from the main switchboard, or create secondary windows with more buttons. These buttons are a lot less complicated; they just use friendly terms like "open form," and you don't need a macro.

Figure 577

You have two options to run the report we added to our macro. You can create a button on the Switchboard to run the report directly; or, train your client or user to run the Form that has the button with the macro from the Switchboard, and then use the button on the form to run the report. That's a matter for you to decide, depending upon the client's work flow.

In any case, adding buttons with macros and designing a cool switchboard turns your Access project from an ordinary database into something you can refer to as an "application."