Creating Special-Purpose Macros

3 4

As mentioned earlier in the chapter, from the earliest versions of Access, there have been two macro names reserved for special purposes: The AutoExec macro runs actions when a database is opened, and the AutoKeys macro sets up a group of hot keys to use in a database. New databases don't contain these macros by default, but if you want to run actions when opening a database or set up hot keys, the macros used for these purposes must be named AutoExec (or AutoKeys) for Access to recognize them.

Additionally, there's another type of special-purpose macro that has no reserved name. This macro makes it easier to run a complex series of functions, queries, and macro actions that automate importing data from an external source (such as a text file) and processing the raw imported data as needed to make it more useful in a database. This type of macro is handy if you frequently need to import and process data from an external source.

Using the AutoKeys Macro Group

The AutoKeys macro group is just as useful in Access 2002 as it was in Access 1.0. This macro group specifies actions to run from custom hot keys in an Access database. These hot keys supplement the built-in hot keys with your own hot keys to run actions you need frequently.

Each macro in the AutoKeys macro group corresponds to a letter of the alphabet, preceded by the caret symbol (^), which represents the Ctrl key. For example, after you create an ^A macro in the AutoKeys macro group, you can run the macro by pressing the Ctrl+A hot key. You can use all the letters of the alphabet as macro names in the AutoKeys macro group, but it's generally advisable to avoid hot keys that already have standard uses in Windows or Office, such as Ctrl+C or Ctrl+V.

Table 14-1 lists the macros in the AutoKeys macro group that I use in most of my Access databases. Of course, you can create your own hot keys to meet your needs.

Table 14-1. Sample AutoKeys macro group

Macro name	Action	Arguments	Description
^B	OpenForm	Form Name: fmnuMain	Opens the main menu form, named fmnuMain. (You can replace fmnuMain with the name of your main menu.) This macro is handy for quickly reopening the main menu when it has been closed accidentally.
^D	SendKeys	Keystrokes: %fa{home} +^{right 2}-{end}	Creates a duplicate of a database object. The new object's name is the same as the original object's but with a dash in front, and you can also append a numeral at the end. Used to create a backup copy of an object. (See Figure 14-5 for an example of this hot key in action.)
^E	Echo	Echo On: Yes	Turns Echo on. Useful when VBA code has stopped abnormally, leaving the screen frozen as a result of Echo being set to False in code.
^O	SendKeys	Keystrokes: {f2}{home}-O- {enter}{down}	Renames an object in focus by adding an -O- in front of its name. This macro is useful for preserving the original version of a database object before you start making changes.
^Q	SendKeys	Keystrokes: {f2}{home}-?- {enter}{down}	Renames an object by adding a -?- in front of its name. This prefix is used to identify an object that might not be needed anymore but that you're not yet ready to delete.
^R	SendKeys	Keystrokes: {f2}{home}- {enter}{down}	Renames an object by adding a dash in front of its name. This prefix indicates database objects that aren't needed anymore and that can be deleted when you next clean up the database.

Several of these macros are used to rename or copy database objects, which is a way of keeping older versions of objects available by giving them distinctive prefixes. The ^D macro is the one I use most often; it lets me create a series of backup copies of a database object I am working on. If something goes wrong with the object-or if I just decide that I like a previous version better-I can easily revert to an older version of the object. When the object is finalized, I delete all the old versions, which are sorted to the top of the list in the Database window because of the leading dash in the version names.

Many of my AutoKeys macros use the SendKeys action to send a series of keystrokes to Access. These macros correspond to the keystrokes you would use to perform the same action in the interface. For example, using the ^D macro is equivalent to the sequence of keystrokes you would have to perform to create a duplicate of the current database object with the new name. When you use this macro, you will see the same Save As dialog box (shown in Figure 14-5) as you would if you performed all the keystrokes.

Figure 14-5. Use the ^D macro to make a backup copy of a database object.

note

---

SendKeys isn't used much any more, but it can be useful for creating hot keys in the AutoKeys macro group. There isn't any other way to perform the series of actions in the ^D and ^R hot key macros in this macro group.

Creating an AutoExec Macro

Although the Startup dialog box (opened by choosing Tools, Startup) lets you select a form to open when a database is opened, specify a custom menu for the database, and set various other database options as you prefer, an AutoExec macro is still useful for a few specialized purposes. One of these uses is to turn on global error handling; another is to create a Word, Excel, or other object to use in Automation code.

For example, if you have a function named OpenWord that creates a Word object to work with when exporting Access data to Word documents, you could run the function from a RunCode macro action. The same is true for a function (say, SetErrorTrappingOn) that turns on global error trapping.

note

---

You can run only functions from the RunCode macro action. If you have a Sub that you need to run from a macro, you'll need to either convert it to a function or call it from within a function.

Using Macros to Automate Importing or Exporting Data

If you need to periodically import data from a text file, reformat the data in a query, and then append the massaged data to a table for use in your database, these actions can be conveniently run from a single macro. To illustrate creating such a macro, we'll use a sample text file typical of data downloaded from a mainframe, with the name and address data in all capital letters, and the entire prospect name in a single field.

tip

---

Before automating a series of actions with a macro, first make sure the actions work properly when performed manually.

Figure 14-6 shows the text file in Notepad.

Figure 14-6. A text file to be imported shows text in all capital letters when opened in Notepad.

To make this data useful in an Access database, some of the name and address data must be converted to proper case (first letter capitalized), and the prospect name must be split into FirstName, MiddleName, and LastName fields.

note

---

The text file used in the following procedure and the sample Test Access 2002 database can be found on the companion CD. This database contains the queries and tables used in the example.

To create the macro to automate importing data from this text file, follow these steps:

1. Open the Test Access 2002 database.
2. Click Macros in the Database window's Objects bar, and click New to open a new macro in the Macro Designer.
3. Select OpenQuery in the Action column's drop-down list, and press F6 to move to the Query Name box.
4. Click the drop-down arrow, and select the qdelProspectsRaw query.
5. On the next macro row, select RunCode as the macro action, and press F6 to move to the Function Name box.
6. Click the Build button to the right of the Function Name box to open the Expression Builder, and double-click the Functions folder to expand it.
7. Click the Test Access 2002 folder to display the functions in this database, click basUtilities and double-click ImportProspects in the list of functions to place it in the Expression box (see Figure 14-7), and then click OK to return to the Macro Designer.

   

   Figure 14-7. The Expression Builder helps you select a function to run from the RunCode macro action.

8. (Optional) In the macro's comments column, enter Import new prospects from text file.
9. In the next macro row, select the OpenQuery macro action, and press F6.
10. Click the down arrow, and select qdelProspectsNew.
11. (Optional) Type a description, such as Clear old prospects table.
12. In the next macro row, select the OpenQuery action, and select qappProspectsNew as the query name.
13. (Optional) Enter the description Append new prospects to tblProspectsNew.
14. In the last macro row of this example, select the MsgBox action, and type New prospects imported in the Message box.
15. Save the macro as mcrImportNewProspects.

To test the macro, run it from the Macro Designer by clicking the Run button on the toolbar. You'll see a series of confirmation messages as the macro runs the queries and code. The final table of cleaned-up prospect data is shown in Figure 14-8.

Figure 14-8. After processing by an append query, the imported data is in proper case, and the name data is split into FirstName, MiddleName and LastName fields.

In future, when you need to import and process a new prospects file, you can simply double-click this macro in the Database window to run it.

tip

---

To suppress the confirmation messages while running the macro, insert a new macro row at the beginning of the macro, and select the SetWarnings macro action with a value of No.