Section 15.3. Three Macro Recipes

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:

• OpenForm to open the form that you'll use to display the matching the record (in this case, AnimalTypes). You can substitute OpenTable to search using a datasheet.

• GoToControl to move to the field you want to search (in this case, Description). If you're planning to perform a search that spans every field, then you can skip this step.

• FindRecord to find the text. It's up to you whether you want to start searching at the first record or the current one, as in this example. You can also choose whether you'll search for the text anywhere inside the field, or require the whole field value to match the search text exactly.

Once you put together the complete macro, you'll get something like this:

Table 15-1.

Action	Important Arguments [1]	Description
1. OpenForm	Form Name : AnimalTypes	Opens the form. If it's already open, then this switches to the existing window.
2. GoToControl	Control Name: Diet	Jumps to the Diet field.
3. FindRecord	Find What: = "hay" Match: Any Part of Field Only Current Field: Yes Find First: No	Finds the specified text anywhere in the Diet field, starting at the current record.

^[1] You can use the default values for all the other arguments.

---

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:

• If you want to use the standard print settings, then you can print any report by using the OpenReport action, and setting the View argument to Print.

• If you want to customize the print quality, number of copies, and starting and stopping page, you need to use a three-step approach. Start with Open Report, use PrintOut to send it off, and then wind up with Close to tidy up.

---

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:

Table 15-2.

Action	Important Arguments [2]	Description
1. OpenReport	Report Name: Cheapskate-Customers	Opens the report (but as you'll see, it's only around for a couple of seconds).
2. PrintOut	Copies: 2	You can use other arguments to print just a range of pages or change the quality. However, you can't pick the printer.
3. Close	Object Type: Report Object Name: Cheapskate-Customers	There's no need to keep this report open, now that the printout's sent off.

^[2] You can use the default values for all the other arguments.

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.

UP TO SPEED
SendObject Works with Your Email Software

The SendObject action uses a standard called MAPI (Messaging Application Programming Interface), which means it lets you use just about any Windows email program. It doesn't matter whether you favor Outlook, Eudora, Pegasus, or something way more exoticSendObject can fire up your email application and use it to send a message.

If you're not sure which email program is set for automatic use on your computer, then you can easily find out. Just head to the Control Panel, select Internet Options, and then click the Programs tab. You'll find your standard Web browser, your email software, and a few less common Web- related applications (like newsreaders).

SendObject is surprisingly versatile. You can use it in three ways:

• To email a database object to another person . The database object's converted to another format you choose, like an Excel spreadsheet, an HTML Web page, or even a print-read PDF file (if you've installed the free Save As PDF add-in, as described in Section 10.2.3). You specify the object you want to send, using the Object Type and Object Name arguments.

• To email the current database object . This way gives you an infinitely flexible macro that can send off whatever data you're currently looking at. The only limitation's that you need to know what type of object you're planning to send, whether it's a full table, a query that highlights important information, or a report with grouping and subtotals. Just set the Object Type argument accordingly , and leave Object Name blank.

• To send an ordinary email message . To send a message, you simply leave both the Object Type and Object Name arguments blank. You can fill in the message using the Message Text property. This method's a handy way to let someone know when you've added some new data, or finished a hefty editing job.

---

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

---

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.

Table 15-3.

Action	Important Arguments [3]	Description
1. SendObject	Object Type: Query Object Name: MonthlySales-Totals Output Format: Excel Work-book (.xlsx) To: [e-mail] Subject: Monthly Update Message Text: Here are the most recent sales figures, straight from our macro-fied Access database. You'll get the customer totals in a separate email . Edit Message: Yes	Sends an email message to [e-mail], with the data from the MonthlySalesTotals query converted to an Excel workbook. The message subject and message text are set by the Subject and Message Text arguments. You have a chance to tweak them before the message is sent. Figure 15-14 shows this action.
2. SendObject	Object Type: Query Object Name: CustomerSales-Totals Output Format: Excel Workbook (.xlsx) To: [e-mail] Subject: Monthly Update Message Text: Here are the totals by customer . Edit Message: Yes
Sends an email message to [e-mail], with the data from the CustomerSalesTotals query.

^[3] You can use the default values for all the other arguments.

Figure 15-14. When the Edit Message property's set to Yes, you get a final chance to review (and change) the message before it's sent.

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.

---