Section 15.1. Macro Essentials | Access 2007[c] The Missing Manual

15.1. Macro Essentials

Although you may not realize it, you've already used macros. In Chapter 14, you created buttons that could perform useful tasks , like opening another form or navigating to a specific record. To create these actions, you used the Command Button wizard, which asks a few simple questions, and then generates a made-to-measure macro.

UP TO SPEED
Macros vs. Code

In the past, macros have had a bit of a mixed reputation. Some Access gurus avoided them, preferring to use more powerful Visual Basic (VB) programming language (which you'll pick up in the next chapter). Microsoft contributed to the confusion by suggesting that macros were an oldschool technique, and not the best option for forward-thinking developers.

With Access 2007, macros finally get more attention from Microsoftand a more respectable reputation. Although macros don't have anywhere near the power of raw VB code, they're simple, clean, and quick in a way that VB code can never be. But the most important advantage that macros have is security . Because Access knows what every macro does, it can vouch that most macros are safe.

In other words, Access knows that an OpenForm macro action can be used only to open a form, so it doesn't need to worry that it could delete your files, spam your friends , or reformat your hard drive. On the other hand, Access isn't as trusting with VB code. As a result, it's liable to lock out your code- powered features, even if they're no more threatening than two bunnies cuddling on a pillow. (You'll take a closer look at the security story in Section 15.2.)

Even if you're planning to give macros a pass, and become a black belt Visual Basic coder , you should still start with this chapter. Here, you'll learn some important information about how macros plug into forms (Section 15.5). As you'll see in the next chapter, code routines interact with forms in exactly the same way.

Although the Command Button wizard's easy to use, it's not all that flexible. Now, you're ready to get more power by building your own macros.

15.1.1. Creating a Macro

In the following example, you'll start slow, with a simple macro that opens a table, and then heads straight to the last row. Here's how to create it:

Choose Create Other Macro .

A new window appears for you to create your macro. Unofficially, this window's known as the macro builder .

Every macro's made up or a sequence of one or more steps, or actions . To create a macro, you supply this list of actions, putting each one in a separate row (see Figure 15-1). Initially, this list's empty, and your macro doesn't do anything at all.

Choose the first action .

Access has a predefined list of actions you can use to cook up a macro. When you add an action, you simply choose it from this list, as shown in Figure 15-2. For this example, begin by choosing the OpenTable action.

Figure 15-1. This macro consists of two actions. The first action opens a table, and the second goes to a specific record. When you run the macro, Access starts at the top of the list and moves down, performing each action in sequence.

Figure 15-2. Click the drop-down arrow (circled) to see all the actions you can use, in alphabetical order. Once you pick an action, a concise but helpful description appears in the window's bottom-right corner.

Note: Right now, you're working with only the actions that Access considers safe for all databases. A little later (in Section 15.2), you'll consider how you can use a few actions that Access considers risky business.

Choose the arguments for your macro at the bottom of the Macro tab, in the Action Arguments section (see Figure 15-3) .

Most actions need some information from you in order to carry out their business. An OpenTable action doesn't have much meaning unless you tell Access exactly what table you want it to open. These extra bits of information are called arguments .

Figure 15-3. When you select an action in the list, all the arguments appear at the bottom of the window. As this example shows, the OpenTable action has three arguments (Table Name , View, and Data Mode). Every time you click one of these boxes, a brief description of the argument appears in the box on the right. For quick reference, your argument values also appear in a comma-separated list in the Arguments column next to your action.

The OpenTable action actually has three arguments:

Table Name is the table you want to open. You can choose it from a drop-down list of tables. For this example, you can use any table that isn't empty.
View lets you pick the view mode that's used. You can choose ordinary Datasheet view to enter information, Design view to change the table structure, Print Preview to get ready to print the data, or PivotTable and PivotChart to work with a pivot table summary (as described in Section 9.3). For this example, choose Datasheet view. (Of course, once the table's open, you can still switch to a different view by right-clicking the tab title, or using the View button on the ribbon.)
Data Mode determines what type of changes are allowed. You can use the standard option, Edit, to allow all changes, Read Only to allow none, or Add to allow only record insertions. In this example, choose Read Only.

Note: You've already seen how custom macros have more power than the Command Builder wizard. When using the Command Builder wizard, you can open forms and reports , but not ordinary tables, and you can't control the view mode or the allowed types of edits. Macros don't face these limitations.

Optionally, type some extra information in the Comment column to help you remember what this action does .

Don't use the Comment column to explain the obvious (as in "Opens the Products table"). Instead, use it to explain the significance of a step in a more complex operation. Right now, you don't need comments, but later on, when you create macro groups and use conditions, you'll see how they become useful.
Move to the next row, and then repeat steps 2 to 4 to add another action .

You can add a practically unlimited number of actions to a macro. (For Access trivia buffs, macros top out at 999 actions.) Each action takes a separate row, and Access runs your actions in order, from top to bottom.

To complete this example, add a GoToRecord action. This action moves through the table you just opened to get to the record you want.

Using arguments, you'll need to point out the correct object (set the Object Type to Table, and the Object Name to whatever table you picked in step 3). Then, you can use the Record and Offset arguments to specify exactly where you want to end up. Using the Record argument, you can choose to head to the previous row (Previous), the next row (Next), the new row placeholder at the bottom of the table (New), a specific row (Go To), the first row (First), oras in this examplethe last row (Last). If you choose Go To, then you can use the Offset argument to point out a specific positionlike setting it to 5 to jump to the fifth record.

Note: Some macro actions depend on previous macro actions. GoToRecord is a prime exampleit assumes you opened a table, form, or query that has the record you want to see. If you use GoToRecord without having a suitable object open, you get an error message when you run the macro.

And just for fun, why not add one more macro action? Try the MsgBox action, which displays a message of your choosing in a small Message box window. You set the message in the Message argument. Try something like "Your first macro just finished doing its job." You can also add an optional title (using the Title argument), warning beep (by setting the Beep argument to Yes), and predefined icon (using the Type argument).

Tip: You can reorder actions at any time. Simply click the margin just to the left of the item you want to move; this click selects the action. Then, drag the action to its new position. Access automatically bumps other actions out of the way.
Press Ctrl+S to save your macro, and then provide a macro name .

You could name the macro in this example ShowTheLastProduct. If you don't save the macro explicitly, then Access politely asks you to when you close the macro window, or when you run your macro for the first time.

Macros appear in the navigation pane. If you're grouping objects by type, you'll see that macros get their own type. If you're using the Tables and Related Views grouping, Access adds macros to an extra group at the bottom named Unrelated Objects.

Note: When you use the Command Builder wizard, you're also creating a macro. However, this macro doesn't appear in the navigation pane, because it's locked into a specific form. This type of macro is known as an embedded macro , because it's embedded inside a form object.

15.1.2. Running a Macro

Now that your macro's finished, you're ready to try it out. Access gives you four ways to run a macro:

You can run it directly . Just find the macro you want in the navigation pane, and then double-click it. (This method works only if the macro isn't already open.) Or, if the macro's open, choose Macro Tools Design Tools Run.

Macro Run Macro. Youll then get the chance to pick your macro out of a list.
You can trigger it using a keystroke . You can, for instance, set up a macro that opens your end-of-month financial report whenever you hit Ctrl+F. You'll learn how in Section 15.4.2.
You can run it automatically when the database is first opened . You could create a macro that always starts you out by running your favorite query and showing you the results. You'll try this out in Section 15.4.3.
You can attach it to a form . You could set your macro to spring into action automatically when a button's clicked or new data's entered. This way's the most common way to use macros, and the way the Command Builder wizard works. You'll explore this technique in Section 15.5.

In this chapter, you'll get a chance to try out all these techniques. But right now, keep to the simplest option, and run the macro you created in the previous section using the Macro Tools Design Tools Run command. Figure 15-4 shows the result.

Figure 15-4. Here's the result of running the ShowTheLastProduct macro. Access opens the Products table (in read-only mode, so no changes are allowed), moves to the last, most recent record, and then shows a message informing you that the macro's finished.

Tip: If you want to tweak a macro that you've already created, right-click it in the navigation pane, and then choose Design View. You'll wind up back in the macro window you used to create the macro.

15.1.3. Debugging a Macro

Not all macros run without a hitch. If you've made a mistakemaybe your macro tries to open an object that doesn't exist, or use an argument that doesn't make senseyou get a detailed error message, as shown in Figure 15-5.

Figure 15-5. This error message occurs if you use the OpenTable action without filling in a value for the required Table Name argument.

Although macro error messages are quite descriptive, they don't always give you enough information to pinpoint the problem. For example, the error message shown in Figure 15-5 has more than one possible causeit could result from a failed OpenTable action (as it does in this example), or a TransferText or TransferSpreadsheet action. And even if you know it's OpenTable that's to blame, that information won't help you if you call OpenTable more than once in the same macro.

To diagnose problems, you can use debugging a programming technique that lets you put your macro under the microscope and see exactly what's happening. The type of debugging that Access gives you with macros is called single-step debugging , because it lets you test your macro one action at a time. That way, you know exactly when the error occurs.

To use single-step debugging, follow these steps:

Open your macro in Design view .

All new macros begin in Design view. If you want to test a macro you created earlier, find it in the navigation pane, right-click it, and then choose Design View.
Choose Macro Tools Design Tools Single Step .

Single Step's a toggle button, which means it appears highlighted when it's selected. After you click Single Step, it should be highlighted. (If not, single-step debugging was already switched on, and you just turned it off. Click Single Step again to switch it back on.)

Choose Macro Tools Design Tools Run .

Your macro begins to run. But now there's a difference. Before each action, Access shows you the relevant information in the Macro Single Step window. Figure 15-6 shows you how it works.

Figure 15-6. This window tells you that you're running a macro named ProblematicMacro. The next step is to perform an OpenTable action, with the argument values shown in the Arguments box. (Pay no heed to the Condition box, because you haven't yet learned how to craft conditional macros.)

Click Step, Continue, or Stop All Macros, depending on what you want to do next .

Step performs the action. If the action completes successfully, then Access pauses the macro again, and then shows you the Macro Single Step window with information about the next action. That's why this process is called single-steppingit lets you perform a single step at a time. If you click Step and the action fails, then you see the error information, as shown in Figure 15-7.

Figure 15-7. When an error occurs, you can't go any further. The Macro Single Step window shows the Access error number for your problem (which is useful if you need to search for help in Microsoft's online knowledge base), but it doesn't let you keep going. You must click Stop All Macros, fix the problem, and then try again.

Continue switches off single-step mode and runs the rest of the macro without interruption. If an error occurs, it fails with an error message, just like before.
Stop All Macros stops the macro before it performs any more actions. The all in Stop All Macros indicates that if there's more than one macro running at once, then Access aborts them all. You could create a macro that calls another macro. If you stop processing during this sequence of events, then both macros give up.

Note: The Single Step setting affects all macros, including any you created with the Command Builder wizard. So remember to switch it off when you're done testing your macro. Otherwise, the Macro Single Step window appears when you're using macros that work perfectly fine.