15.3. Three Macro Recipes
So far, you've created a basic macro, tried it out, and thought deeply about macro security. It's about time you got a payoff for all your work, and considered a few practical ways to use macros.
The full list of macro actions contains many actions that aren't that interesting, or relate only to specific project types (like Access projects that front SQL Server databases, which you'll consider in Chapter 18). The following sections highlight a few of the more useful macro commands. You can check them out in the downloadable content for this chapter, or try the shake-and-bake recipes in your own database.
15.3.1. Find a Record
The FindRecord action works just like the Datasheet Find feature you saw in Section 3.2.3. You fill in all the search information using arguments.
Let's say you want to search the Diet field in the AnimalTypes table, looking for the word "hay." Here are the actions you may use:
Once you put together the complete macro, you'll get something like this:
Note: You may have noticed that the Find What argument of the FindRecord action starts with an equal sign. It accepts an expression (Section 7.1). In this example, the expression's nothing more than a fixed piece of text, which is wrapped in quotation marks. However, you could substitute a more complex expression that uses operators, functions, and other advanced techniques.
The neat thing about this macro is that you can use it several times in a row to look for more occurrences of the text. If the AnimalTypes form's already open, then this macro just carries on to the next match.
Tip: For even more flexibility, you could create a macro that uses only the FindRecord action. That way, you could search for specific text in any field in any form or table. Of course, if you try to run such a macro and you don't have any forms or tables open, FindRecord can't do anything, and you get an error.
15.3.2. Print a Report
Do you need a helpful macro that automatically spits out a frequently used report? Access gives you several options. Here are two:
Tip: Don't try either of these techniques with an untrusted databaseAccess doesn't let you.
The following sequence of actions demonstrates the second approach. This macro prints two copies of a list of so-called customers who haven't actually ordered a single thing, using the CheapskateCustomers report:
As soon as Access performs the PrintOut action, the pages start streaming out of the default printer on your computer. You don't get a chance to confirm or cancel the operation. For even more fun, you can extend this macro with more steps so it prints several reports at once.
Tip: There's one more option. You can open a table or a report with the View Argument set to Print Preview. This option doesn't actually send the data to your printer, but it gets you one step closer. This option's best if you want a final chance to choose a printer, make sure the data's correct, and check that the report isn't ridiculously long. It also works in untrusted databases.
15.3.3. Email Your Data
One of the hidden gems of the Access macro language is SendObjectan all-purpose action for sending email messages.
SendObject is surprisingly versatile. You can use it in three ways:
Note: SendObject can send only a single database object at a time. If you want to send several database objects, you need to use SendObject several times. To send three reports, you need three email messages, with three attached files. In some cases, you may be able to get around this requirement by creating a clever query that fuses together all the information you want to send into one set of results. (For example, see union queries in Section 18.104.22.168.)
The nicest thing about SendObject is that you can use it in an untrusted database, provided you follow one rule: Set the Edit Message argument to Yes. That way, when the macro runs, you get a final chance to review the message, change any text, and cancel it if you're unhappy . But if you set Edit Message to No, then the SendObject action fires the message off without giving you a chance to step in. That behavior's considered risky, so Access doesn't allow it in an untrusted database.
The following macro converts two queries with sales information into Excel spreadsheets. It then mails them off to the head honchos.
If you're crafty, you can mail huge numbers of people at once. The most straightforward option's to supply a whole list of addresses for the To, Cc, or Bcc argument, separating each one with a semicolon (;). For an even better approach, use a mailing list. This technique may vary depending on your mail software, but in Outlook and Outlook Express, it's easyjust put the name of the mailing list in the To field. If you've created a mailing list named FairweatherFriends, then just type the word FairweatherFriends for the To argument.
Tip: Running out of room to edit your message? Press Shift+F2 while you're editing the Message Text property to pop up a much larger Zoom window, where you can see several lines of text at once.