Uses of Macros

Access 2007 provides various types of macro actions that you can use to automate your application. With macros, you can

• Open any table, query, form, or report in any available view or close any open table, query, form, or report.

• Open a report in Print Preview or Report view or send a report directly to the printer.

• Send the output data from a report to a Rich Text Format (.rtf) file, a Windows Notepad (.txt) file, or a Snapshot (.snp) format file. You can then open the file in Microsoft Word or Notepad.

• Execute a select query or an action query. You can base the parameters of a query on the values of controls in any open form.

• Include conditions that test values in a database, a form, or a report and use the results of a test to determine what action runs next.

• Execute other macros or execute Visual Basic functions. You can halt the current macro or all macros, cancel the event that triggered the macro, or quit the application.

• Trap errors caused during execution of macro actions, evaluate the error, and execute alternate actions.

• Set the value of any form or report control or set selected properties of forms and form controls.

• Emulate keyboard actions and supply input to system dialog boxes.

• Refresh the values in forms, list box controls, and combo box controls.

• Apply a filter to, go to any record in, or search for data in a form’s underlying table or query.

• Execute any of the commands on any of the Access Ribbons.

• Move and size, minimize, maximize, or restore any window within the Access workspace when you work in multiple-document interface mode.

• Change the focus to a window or to any control within a window or select a page of a report to display in Print Preview.

• Display informative messages and sound a beep to draw attention to your messages. You can also disable certain warning messages when executing action queries.

• Rename any object in your database, make another copy of a selected object in your database, or copy an object to another Access database.

• Delete objects in your database or save an open object.

• Import, export, or attach other database tables or import or export spreadsheet or text files.

• Start an application and exchange data with the application using Dynamic Data Exchange (DDE) or the Clipboard. You can send data from a table, query, form, or report to an output file and then open that file in the appropriate application. You can also send keystrokes to the target application.

Consider some of the other possibilities for macros. For example, you can make moving from one task to another easier by using command buttons that open and position forms and set values. You can create very complex editing routines that validate data entered in forms, including checking data in other tables. You can even check something like the customer name entered in an order form and open another form so that the user can enter detailed data if no record exists for that customer.

Inside Out-Do We Recommend Using Macros? 

In versions prior to Access 2007, we would have strongly recommended against using macros. Now that macros offer error trapping and the ability to create and manipulate simple variables, we can recommend using them for all simple applications and perhaps even moderately complex ones. Macros still won’t work well for a complex production application. The debugging facilities are very limited. If you run into any problem in your macro code, it can be very difficult to figure out the solution. Most complex applications also require manipulating recordsets behind the scenes, and you can’t do that at all with macros.

Even if you plan to use Visual Basic from the very beginning, it’s still worth your while to learn the basics of programming with macro actions. In truth, the best (and perhaps only) way to perform certain actions in Visual Basic is to execute the equivalent macro action. For example, the only way to open a form that does not have any Visual Basic code is to execute the OpenForm action from within your Visual Basic procedure. (When a form has a module, an advanced way to open one or more copies of a form is to set a module object to the form’s module.) As you’ll learn in the next chapter, you execute macro actions in Visual Basic as methods of a special object called DoCmd. (In Article 6, on the companion CD, we note the macro actions that have better native equivalents in Visual Basic.)