Jump to content

How to Automate a Client Application Using Macros in Access 2010

  kbnotes's Photo
Posted May 04 2010 06:10 AM

In the forthcoming Microsoft® Access® 2010 Inside Out (Microsoft Press), author Jeff Conrad explains how to automate a client application using macros.

How to Automate a Client Application Using Macros in Access 2010

Data macros are attached to table events or the table itself and only interact 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 probably should use 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 of 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 Microsoft Windows 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 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.

The Macro Design Facility—An Overview

Microsoft redesigned the macro design window in Access 2010 in order for Access developers to be more productive when creating macros in their applications. Although functional, the older macro design window was not the most intuitive interface to work with. For example, when you wanted to create a new action for a macro, you selected the action in the upper part of the window, but you entered the arguments in the bottom part of the window. The Arguments column in the upper part of the window was read-only so you constantly needed to switch from the top window to the bottom window. You also could not create complex nested logic conditions for your macro execution and the readability of the macro elements was difficult to follow.

The following sections explain how to work with the macro design facility in Access 2010 in the context of user interface macros.

Working with the Logic Designer

On the Create tab, in the Macros & Code group, click the Macro button. Access opens the new Logic Designer for creating macros, as shown in the following figure.

Attached Image

Whenever you need to create or edit data macros or user interface macros in Access 2010, this is the design surface that you use. When you’re working with user interface macros, Access enables the commands in the Tools group and displays the macro actions available for user interface macros in the Action Catalog. You’ll also notice that Access did not collapse the Navigation Pane when you clicked the Macro command on the Ribbon as it does when you are working with data macros. When you are working with user interface macro objects—macros displayed in the Navigation Pane—Access does not open the Logic Designer window modally, which means you can open other database objects while working on your macro. (You’ll learn later in this chapter that Access opens the Logic Designer modally when you’re creating embedded macros.)

As you can see in the preceding figure, the new Logic Designer layout looks more like a Visual Basic code window. The Expand Actions, Collapse Actions, Expand All, and Collapse All buttons in the Collapse/Expand group selectively expand or collapse the actions listed in the macro designer surface. In the Show/Hide group on the Design tab, you can choose to hide the Action Catalog, shown on the right side of the Logic Designer window, by clicking the Action Catalog toggle button. When you’re working with macro objects, Access does not display the Close group on the Design tab as it does for data macros. When you want to save your macro changes, you can click the Save button in the Quick Access Toolbar or press CTRL+S. Note that if you attempt to close the Logic Designer window with unsaved changes, Access prompts you and asks if you want to save your changes before closing the window.

On the right side of the Logic Designer window is the Action Catalog. The Action Catalog shows a contextual list of the program flow constructs and macro actions that are applicable for user interface macros. If you are working with a client macro, Access displays macro actions available for client macros. Similarly, when you’re working with a web macro, Access displays macros actions available for web macros.
In the middle of the Logic Designer window is the main macro designer surface where you define your macro. You add program flow constructs, macro actions, and arguments to the designer surface to instruct Access what actions to take for the macro. If you have more actions than can fit on the screen, Access provides a scroll bar on the right side of the macro design surface so you can scroll down to see the rest of your actions.
In the bottom right corner of the Logic Designer window is the Help window. Access displays a brief help message in this window depending upon where the focus is located in the Action Catalog. (Remember: You can always press F1 to open a context-sensitive Help topic.)

To get you accustomed to using the Logic Designer for macros, let’s create a simple macro that displays a message. You can use the MessageBox action to open a pop-up modal dialog box with a message in it. This is a great way to display a warning or an informative message in your database without defining a separate form. To begin, let’s add a Comment block to the macro design surface. You’ll find the Comment block especially useful for documenting large macros that contain many actions. Click on the word Comments under the Program Flow node in the Action Catalog, hold the mouse key down, drag the comment onto the macro design surface, and then release the mouse button, as shown in the following figure. Note that in the figure, we collapsed the Navigation Pane so you can see more of the macro design surface.

Attached Image

Assume that this message will be a greeting so click inside the Comment block, and type Greeting message. Click outside the Comment block onto the macro design surface and Access collapses the size of the Comment block to just fit the text you typed and displays the text in green. The /* and */ symbols mark the beginning and end of a block of comments. Access designates anything written between those symbols as a comment and they are there only to provide information about the purpose of the data macro or particular action to follow.

Click into the Add New Action combo box on the macro design surface now and drop-down the list of macro actions. In the Add New Action combo box, you can specify any one of the 84 client macro actions and four program flow constructs provided by Access 2010. Select MessageBox from the list of actions in this drop-down list. After you select an action such as MessageBox, Access displays argument boxes for the specific action you choose, as shown in the following figure, in which you enter the arguments for the action.

Attached Image

Note: In Access 2010, Microsoft renamed the MsgBox action from previous versions of Access to MessageBox. If you have a existing databases that use the MsgBox action, Access 2010 can still understand and execute the action.

You use the Message argument box to set the message that you want Access to display in the dialog box you’re creating. The setting in the Beep argument box tells Access whether to sound a beep when it displays the message. In the Type argument box, you can choose a graphic indicator, such as a red critical icon, that will appear with your message in the dialog box. In the Title argument box, you can type the contents of your dialog box’s title bar. Use the settings shown in the preceding figure in your macro.

Note: Why doesn’t the list include all the macro actions available? Access 2010 includes 86 client macro actions, but not all these actions can run in a database that is not trusted. By default, Access displays only the macro actions that can run in a trusted database in the Action column. To see the complete list of client macro actions, click the Show All Actions button in the Show/Hide group on the Design tab. When you select an action that can run only in a trusted database, Access displays an exclamation point in the upper left corner of the action block. If a macro in your application includes actions that can run only in a trusted database, your user must trust your database to be able to run the macro.

Saving Your Macro

You must save a macro before you can run it. Click the Save button on the Quick Access Toolbar, or click the File tab on the Backstage view and then click Save. When you do so, Access opens the dialog box shown in the following figure. Enter the name TestGreeting, and click OK to save your macro.

Attached Image

0 Replies