Automating a Client Application Using Macros in Microsoft Access 2010

• Uses of Macros

• The Macro Design Facility—An Overview

• Defining Multiple Actions

• Working with Submacros

• Understanding Conditional Expressions

• Using Embedded Macros

• Using Temporary Variables

• Trapping Errors in Macros

• Understanding Macro Actions That Are Not Trusted

• Making Your Application Come Alive with Macros

In Chapter 7, “Creating Table Data Macros,” you learned about the new data macro feature in Microsoft Access 2010. Data macros are attached to table events or the table itself and interact only at the data layer. In this chapter and the next, you’ll learn about user interface macros. In Access 2010, you can define a user interface macro to execute just about any task you would otherwise initiate with the keyboard or the mouse. The unique power of user interface macros in Access is their ability to automate responses to many types of events without forcing you to learn a programming language. The event might be a change in the data, the opening or closing of a form or a report, or even a change of focus from one control to another. Within a user interface macro, you can include multiple actions and define condition checking so that different actions are performed depending on the values in your forms or reports. For the remainder of this chapter and the next, we’ll only use the term macros to refer to user interface macros.

Macros are particularly useful for building small, personal applications or for prototyping larger ones. Macros are also essential if you want to automate a web database and display the application in a web browser using Access Services and SharePoint 2010. (You’ll learn how to automate a web database in Chapter 21, “Automating a Web Application Using Macros.”) As you’ll learn in Chapter 24, “Understanding Visual Basic Fundamentals,“ on the companion CD, you probably should use Microsoft Visual Basic for complex applications or for applications that will be shared by several users over a network. However, even if you think you’re ready to jump right into Visual Basic, you should study all the macro actions first. You’ll find that you’ll use nearly all the available macro actions in Visual Basic, so learning macros is an excellent introduction to programming in Access in general.

Uses of Macros

Access 2010 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 or a Notepad (.txt) file. You can then open the file in Microsoft Word 2010 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 (MDI) 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.