3 4
By now, you might have the impression that macros aren't all that useful in Access 2002. The reasons macros seem to be less useful have more to do with the greatly increased power of VBA code than with any deficiencies in macros. Macros have remained as useful as they were in Access 1, but because VBA code has become so much more powerful, macros now appear quite limited in comparison. If you've been using macros since the early days of Access, your old macros will still work-you won't need to convert them to code. But if you're just starting out with Access 2002, there's little reason to use macros to automate a database, much as there isn't much (if any) reason to learn MS-DOS commands if you're working in Microsoft Windows 2000 or Windows XP with Microsoft Office XP.
These are some of the specific areas in which VBA code has more functionality than macros:
In addition to these macro limitations, the ease-of-use improvements brought about by the introduction of code behind forms in Access 2.0 and the new methods of creating menus and toolbars using the CommandBars collection (introduced in Access 97 and discussed in Chapter 13, "Customizing Access Toolbars and Menus [Command Bars]") make macros less useful and VBA code easier to write and more powerful.
The actions used in writing macros generally correspond to methods used in VBA code, although there are some exceptions. Table 14-3 matches up macro actions with the corresponding VBA methods. (Macro actions that are no longer useful but that have been retained for backward compatibility are noted.) If you want to write VBA code to replace your old macros, this table will help you find the right VBA method to use in your code. The next section offers more detail.
In general, macro actions correspond to methods of the DoCmd object, a component of the Access object model. The DoCmd object doesn't correspond directly to any element of the Access interface; it's simply a way to run a miscellaneous assortment of methods. As Access has evolved from version 1 to version 2002, more and more methods previously run from the DoCmd object have changed into methods of objects such as forms and controls. For example, the old GoToControl macro action/DoCmd object method has been replaced by the SetFocus method of the Control object.
Table 14-2. Macro actions and DoCmd methods
Macro action | DoCmd method | Description | Comments |
AddMenu | AddMenu | Creates a menu | Although AddMenu is still listed as a DoCmd method in Access 2002, since Access 97, users have been able to create command bars (menu bars and toolbars) by using the Customize dialog box in the interface or by using the CommandBars collection in the Office object model. |
ApplyFilter | ApplyFilter | Applies a filter to a form or report | |
Beep | Beep | Sounds a beep | |
CancelEvent | CancelEvent | Cancels an event | |
Close | Close | Closes an object, usually a form or report | |
CopyDatabaseFile | CopyDatabaseFile | Copies a database file | |
CopyObject | CopyObject | Copies a database object, such as a form or report | |
DeleteObject | DeleteObject | Deletes a database object, such as a form or report | |
DoMenuItem | Runs a menu item | Retained for compatibility with earlier versions of Access. There has not been a DoMenuItem macro action since Access 95. The RunCommand method (with one of its numerous constants) supersedes the functionality of the older DoMenuItem method. | |
Echo | Echo | Makes screen updates visible if set to Yes or invisible if set to No. | Retained for compatibility with earlier versions of Access. The Echo method of the Application object is the preferred method. |
FindNext | FindNext | Finds the next record that meets the criteria specified by the previous FindRecord action | |
FindRecord | FindRecord | Finds the first record that matches criteria specified in its arguments | |
GoToControl | GoToControl | Sets the focus on the specified control | Retained for compatibility with earlier versions of Access. Use the SetFocus method of an object instead. |
GoToPage | GoToPage | Goes to a specific page of a form | Although still valid, this method is rarely used, as multipage forms have generally been replaced with multipage tab controls. |
GoToRecord | GoToRecord | Goes to a specific record on a form | Still valid for moving to specific records on forms (first, last, next, previous), but in VBA code, GoToRecord has been generally replaced by the Find and Seek methods of DAO recordsets or the Find and Seek methods of ADO recordsets. |
Hourglass | Hourglass | Toggles the mouse pointer between an hourglass and the regular shapes | |
Maximize | Maximize | Sizes the active window to fill the entire Access window | |
Minimize | Minimize | Reduces the active window to a small title bar at the bottom of the main Access window | |
MoveSize | MoveSize | Changes the position and/or size of the active window | Retained for compatibility with earlier versions of Access. Use the new Move method of a form or report instead. |
MsgBox |
| Displays a message box | Has no corresponding DoCmd method. Use the MsgBox function instead. |
OpenDataAccessPage | OpenDataAccessPage | Opens a data access page | |
OpenDiagram | OpenDiagram | Opens a database diagram in an Access project | |
OpenForm | OpenForm | Opens a form | |
OpenFunction | OpenFunction | Opens a user-defined function in a SQL Server database for viewing in Access | |
OpenModule | OpenModule | Opens a module in Design view | |
OpenQuery | OpenQuery | Opens a select query, or runs an action query | |
OpenReport | OpenReport | Prints a report, or opens it in print preview | |
OpenStoredProcedure | OpenStoredProcedure | Opens a stored procedure in an Access project | |
OpenTable | OpenTable | Opens a table | |
OpenView | OpenView | Opens a view in an Access project | |
OutputTo | OutputTo | Outputs data in an Access object to one of a number of formats | |
PrintOut | PrintOut | Prints the active database object | Both the action and the method allow you to specify a page range and the number of copies to print. |
Quit | Quit | Exits Access | Retained for compatibility with earlier versions of Access. Use the Application object's Quit method instead. |
Rename | Rename | Renames a database object | |
RepaintObject | RepaintObject | Completes pending screen updates for a database object | |
Requery | Requery | Updates data in a database object | Retained for compatibility with earlier versions of Access. Replaced by the Requery method of forms and other objects. |
Restore | Restore | Restores a maximized or minimized window to its previous size | |
RunApp |
| Runs an application | Has no corresponding DoCmd method. Use the Shell function or the CreateObject function to run other applications. |
RunCode |
| Runs a function | Has no corresponding DoCmd method. Call the function directly in VBA code. |
RunCommand | RunCommand | Runs a command | |
RunMacro | RunMacro | Runs a macro | |
RunSQL | RunSQL | Runs a SQL statement | |
Save | Save | Saves a database object | |
SelectObject | SelectObject | Selects a database object | Because some actions can be performed only on the currently selected object, this action should be run first to ensure that the appropriate object is selected. |
SendKeys |
| Issues one or more keystrokes | Has no corresponding DoCmd method. Use the SendKeys statement instead |
SendObject | SendObject | Sends the specified database object as an e-mail message or as an attachment to an e-mail message | Generally, it's preferable to create an Outlook Mail item by using the CreateObject function and then fill it with Access data and send it by using components of the Outlook object model. |
SetMenuItem | SetMenuItem | Sets the state of menu items on a menu bar | Retained for compatibility with menus created using the old menu macro method (prior to Access 97). |
SetValue |
| Sets the value of a control | Has no corresponding DoCmd method. You can set the value of a field or control directly in VBA code. |
SetWarnings | SetWarnings | Toggles on or off warnings about deleting objects or modifying data with action queries | |
ShowAllRecords | ShowAllRecords | Shows all records (removes a filter) | |
ShowToolbar | ShowToolbar | Displays or hides a toolbar | |
StopAllMacros |
| Stops all macros | Has no corresponding DoCmd method. This action is not needed in VBA code. |
StopMacro |
| Stops the current macro | Has no corresponding DoCmd method. This action isn't needed in VBA code. |
TransferDatabase | TransferDatabase | Exports or imports a database file | |
TransferSpreadsheet | TransferSpreadsheet | Exports or imports a worksheet file | |
TransferSQLDatabase | TransferSQLDatabase | Exports or imports a SQL Server database file | |
TransferText | TransferText | Exports or imports a text file |
You can save a macro and then run the Macro Converter utility to convert it to VBA code, but this utility isn't as helpful as it might be. The Macro Converter simply translates each macro action into the corresponding code (usually a method of the DoCmd object), which often leads to inefficient code using obsolete methods such as GoToControl with the control name when the SetFocus method of the control would be more appropriate.
Even more unfortunate, macros are not converted to the appropriate VBA event procedures, but instead are converted to functions that must in turn be called from event procedures. Additionally, converted macro procedures often use the CodeContextObject property, which is used for determining the object in which the code is executing, and which you'll probably never see outside of a converted macro procedure.
InsideOut
Because of the serious limitations of the Macro Converter, requiring extensive rewriting to turn converted macro code into efficient VBA code, I recommend that you just write VBA code (event procedures and functions) from scratch to replace old macros, using Table 14-3 as a reference to the appropriate VBA methods.
See Chapter 20, "Customizing Your Database Using VBA Code," for more details on writing VBA procedures.
However, if you have an AutoKeys or AutoExec macro, leave them be-these special macros still have uses that can't be duplicated in VBA code.