Working with Macros and Expressions in Access 2010

Summary:  Microsoft Access 2010 introduces a new feature that is known as data macros to enable you to write macros attached to tables. The Macro Designer has also been improved. Learn about these options and explore associated code samples.

Applies to: Access 2010 | Office 2010

Published:  November 2010

Contents

• Overview

• Macro Designer

• Building Data Macros

• Expression Builder

• Macros and Expressions Made Easier

• Additional Resources

Overview

Microsoft Access 2010 developers, mostly, dislike writing macros. Macros, except in certain circumstances (such as creating autokeys and the AutoExec macro), are seen as second-class citizens in the world of application extensibility. They are a way for Microsoft Access users to get started before jumping into Microsoft Visual Basic for Applications (VBA) programming. While the actions used in writing macros (derivatives of the DoCmd object) correspond to the methods that are used in VBA, the macro designer interface pales compared to the rich experience seen in the Visual Basic Editor.

With the release of Access 2010, there are several new features in macro design and use that will appeal to the developer and user alike. These include an improved Macro Designer, table-based data macros, and changes to how you create expressions. The improved Macro Designer helps you create macros that are more flexible and easier to read and follow. Macros can now be attached to tables so that any object that is created based on that table inherits the macro. The Expression Builder now has IntelliSense to make creating expressions easier.

This article discusses the new Macro Designer. It will also show how to build data macros by using the Macro Designer. Finally, it will describe some changes to the Expression Builder.

Macro Designer

Access 2010 has a new Macro Designer that makes it easy to create complex macros while reducing coding errors. You use the designer to do this by using dropdown lists, IntelliSense, reuse of existing macros, drag-and-drop functionality, and copy/paste actions.

Developers will appreciate the improved user interface that aids with conditional block construction to facilitate a smooth logical flow of actions. It is also easy to share your macros either by email, to web pages, or to blogs and newsgroups.

If you have ever created macros in previous versions of Microsoft Access, you are familiar with the three column macro builder (see Figure 1). You added conditional statements in the Condition column, added macro actions in the Action column, and specified parameters in the Arguments column.

Figure 1. Legacy version of the macro creator

In the new Macro Designer for Access 2010, the layout more closely resembles a text editor. The three columns are gone. In their place are actions and conditional statements displayed in a familiar top-down format that is used by programmers. Arguments are displayed inline in a dialog box as shown in Figure 2.

Figure 2. Macro Designer

Adding a new action or conditional statement is simple. Either select it from the actions drop-down list, from a right-click menu, or select it from the Action Catalog pane (shown in Figure 3) to the right side of the Macro Designer.

Figure 3. Action Catalog

You can double-click the action or drag-and-drop it onto the Macro Designer. By default, the Action Catalog and Add New Action combo box show actions that execute in non-trusted databases. To see all actions, click the Show All Actions command on the ribbon (Figure 4).

Figure 4. Show All Actions command on the ribbon

You can collapse some or all of the code blocks by clicking the expanders to the left sie of the code block. You can also collapse or expand code blocks by using the expand or collapse controls in the Expand/Collapse group on the ribbon. Figure 5 shows a collapsed version of the macro shown in Figure 2.

Figure 5. Collapsed code blocks

The Action Catalog search box has a search capability that enables you to type in a term and have Microsoft Access filter and display items that match that term. The Action Catalog search box not only examines the action name but also includes the action description. For example, search for Query. Not only do the search results show actions with the word Query in them, but also ApplyFilter, GoToRecord, and ShowAllRecords that have Query in the description.

You can also add comments to macros by typing // in an Add New Action drop-down box or by dragging the Comments node in the Action Catalog. Comments appear as green text as shown in Figure 2. This ensures that the comments are easy to find and can be used to separate sections in the procedure. Rearranging macro code is easy also. Drag-and-drop code blocks into the new location or use the green up and down arrows (see Figure 5) on the blocks within the actions pane.

The Macro Designer introduces a new program flow construct called group blocks. Group blocks make it easy to put multiple macros into a group that expands or collapses as a unit for readability.

In the macro builder in previous versions of Microsoft Access, you could create simple conditional statements by using the Condition column. In the Access 2010 Macro Designer, you can create more versatile If statements by adding ElseIf and Else statements. To add these statements, select the If block and then click either the ElseIf text or the Else text in the lower right side of the code block. For example, click the ElseIf text and an ElseIf dialog box appears. Then as you type in the condition box, Microsoft Access uses IntelliSense to display identifiers, functions, and other database items.

The Macro Designer, together with IntelliSense, also provides various kinds of help. These include the following:

• AutoComplete displays a drop-down list of words that match an object, function, or parameter after you type enough characters to identify the term. You can either accept the suggestion by pressing ENTER or TAB or by continuing to type the name.

• Quick Info displays the correct syntax for any identifier in your macro or expression. Place the mouse so that the pointer is over an identifier and you will see its declaration information displayed in a dialog box.

• QuickTips provides additional information about a user interface component when a value is selected by using AutoComplete.

Even with all the improvements to the Macro Designer, many developers will still want to convert macros into VBA code. You can do this by using the Convert Macro to Visual Basic command on the ribbon.

In the next section, you create a data macro by using the Macro Designer.

Building Data Macros

Data macros are a new feature in Access 2010. Data macros enable you to attach logic to records and tables (similar to SQL Server triggers). That is, you write logic in one place and all forms and code that add, update, or delete data in a table inherit that logic. Data macros open up various scenarios:

• Check a field value before allowing the addition of another record

• Keep a history of the changes to a record

• Generate an email when a field value changes

• Verify the accuracy of data in a table

Legacy macros (herein called Macros) still exist although they can only be called from a form event, another macro, a report event, or VBA code.

There are two general kinds of data macros: "event" macros that are triggered when some action occurs to data in a table and stand-alone "named" macros that run when they are called by name. You can program a data macro to run immediately after you add, update, or delete data events, or immediately before a delete or change event.

Data macros can return values to Macros by using ReturnVars. ReturnVars in data macros resemble values returned by function/method calls in VBA or other programming languages. This makes it possible to display UI in the calling Macro based on what happens in the data macro. In the data macro, you specify the ReturnVars by using the SetReturnVar command as shown in Figure 6. These values are set in named data macros.

Figure 6. SetReturnVar returns values to macros

To reference variable in a Macro, use the ReturnVars command seen in following statement.

=[ReturnVars]![retrunVar1]

Note

Data macro logic only works with local tables; not linked tables although you can work around this when you are using an Access front-end and Access back-in by adding data macros to the source table.

Data macros are helpful because they reduce the clutter in your databases by reducing the need to attach the same macro to a series of forms. By adding the logic to the table, any form that is created based on that table inherits the logic. You can also ensure the integrity of you data by using data macros. Assume that an event is triggered in a form tied to the table without a data macro. If the user has access to your tables or can run queries, she may be able to bypass the form, thus circumventing your logic. You could restrict access to tables and prohibit running queries, but you cannot do this in all circumstances. By adding the logic directly to the table, the action is triggered even if the user makes the change outside the form.

Data Macros support the events list in Table 1.

Table 1. Events list

Events	Uses
BeforeChange	You may want to: Trigger an error message and block the change Compare the new value to the new value and open another form
BeforeDelete	You may want to: Trigger an error message and stop the deletion Display an order form to reorder the item
AfterUpdate, AfterInsert, AfterDelete	You may want to: Generate an email Loop through a set of records and update their status

To create an event data macro, first, in the left navigation pane, double-click the name of the table that you want the data macro attached to. On the Table tab, in the Before Events group or the After Events group, click the event to which you want to add the macro. Microsoft Access displays the Macro Builder. If you have already created a macro for this event, the Macro Builder opens to that macro.

To create a named macro, in the left navigation pane, double-click the name of the table that you want the data macro attached to. On the Table tab, in the Named Macros group, click Named Macro, and then click Create Named Macro. Microsoft Access displays the Macro Builder.

Consider the following example. Suppose that you have an order system that monitors the levels of merchandise in your inventory. In this system, when the number of items available falls below certain levels, you want to alert the staff that the levels are low or that they need to order more merchandise. To do that, you create a data macro on the table of items. That way, when you create a form that is based on that table, all of the logic that that you must have is already available.

To create the data macro, create a sample table named Items with the following fields.

Table 2. Sample data

Name	Type
Item	Text
Quantity	Number (Long Integer)
Status	Text

Save the table and add the following data to the Item column leaving the other columns blank.

Table 3. Add this data to the table

Coffee

Soda

Tea

Water

To add the macro, select the table and under Table Tools on the Fields tab, click the Views drop-down list and then click Datasheet View. With the table in datasheet view, under Table Tools, click the Table tab, and then click the Before Change event button. Why use the Before Change event instead of the After Change event? As a rule, it is better to use the Before Change event to modify and run additional code rather than to allow the table to update the record, and THEN create additional modifications to the same record that was just saved back to the table. The After Change event table trigger is better for using aggregate totals and saving those values into other tables. For example, maintaining inventory totals when a quantity value is changed. In these kinds of scenarios, you are usually modifying a different table.

Next, you create the If...Then...Else statement that is triggered every time that you make a change in the table. On the Items : Before Change tab, click the drop-down list and then click If. To add the condition to the If statement, type I after the If statement. Notice that you have IntelliSense. Select the Items table. After the [Items] identifier, type a period and then select Quantity. After Quantity, type Between 50 And 100. This sets the condition where if the quantity of items falls between 100 and 50, that portion of the If statement is triggered.

On the right side of the screen, the Actions Catalog contains a list of the available actions and program flow operators for that particular event. Click SetField and drag it to the If statement block. In the Name box, type Items.Status. In the Value box, type "Levels Low" (with the quotation marks). When the quantity of items falls between 50 and 100, the Status text is changed.

Click the If statement again and notice that in the lower-right-side of the block are Add Else and Add Else If options. Click the Add Else If option to add that block. In the condition box, type Items.Quantity<50. In the Actions Catalog pane, drag SetField to the Else If block. In the Name box, type Items.Status and in the Value box, type "Order Now!" (with the quotation marks). Anytime the quantity of items falls below 50, the Status field text is changed.

Click the Else If statement and then click Add Else. Drag SetField on to the Else block. In the Name box, type Items.Status and in the Value box type "", an empty string. This indicates that if the number of items is greater than 100, set the Status field to blank. The macro should now resemble that in Figure 7.

Figure 7. Completed data macro

Save and close the macro by right-clicking the Items:Before Change tab and then click Close.

To test the macro, with the table in datasheet view, change the quantity of Coffee to 100, click out of the record, and notice how the Status field text changes to Levels Low. Change the quantity of Sodas to 49 and the Status field changes to Order Now!. Change the quantity of Tea to 101. The Status field remains blank. All of this is shown in Figure 8.

Figure 8. Status field changes as quantity changes

Another option available with named data macros is the ability to pass in parameters. Parameters are often used to improve database performance because it forces developers and users to restrict data sets before the macro is run. This results in faster macros, less load on database servers, and smaller network traffic. Parameters also add flexibility because you can reuse a macro without modification. You can add parameters to named data macros by double-clicking the table that you want to attach the named macro to. On the Table tab, in the Named Macros group, click the Named Macro drop-down arrow and then click Create Named Macro. At the top of the Macro Builder (see Figure 9), click Create Parameter.

Figure 9. Create Parameter option in Macro Builder

Debugging data macros is not as rich an experience as it is for other programming code. You cannot step through the code or use the message box option. However, you can use the Application Log table with the OnError, RaiseError, and LogEvent macro actions to help find data macro errors. The Application Log table is a system table (known as USysApplicationLog) which by default is not displayed in the Navigation pane. If an error occurs in a data macro, you could tell what happened by viewing the information in the Application Log table.

To display the Application Log table, click the File tab to display the Backstage view. On the Info tab, click View Application Log Table. If you do not see the View Application Log Table button, this means that an Application Log table has not yet been created for this database. When an event is logged such as by using the LogEvent or RaiseError macro actions, the button appears on the Info tab.

Expression Builder

The Expression Builder is a tool to help you build expressions. You can start the Expression Builder from most places in Microsoft Access where you write expressions. Tables and fields, queries, form and report properties, controls, queries, and macros can all use expressions to evaluate data or logic to drive the behavior of an application. It offers easy access to the names of fields and controls in your database, as well as many of the built-in functions available to you when writing expressions. With the Expression Builder, you can create an expression from scratch, or you can select from some prebuilt expressions for displaying page numbers, the current date, and the current date and time, as an example.

In Access 2010, the Expression Builder has undergone significant changes. By using the improved Expression Builder (see Figure 10), you can write expressions faster and with fewer errors.

Figure 10. Expression Builder in Access 2010

The Expression Builder includes new features and a simpler user interface. You no longer must remember syntax and available functions or properties. The new Expression Builder has IntelliSense that provides all the information that you need as you type an expression.

In addition, the new user interface uses progressive disclosure that means that only the functions and properties that exist in a certain context are displayed. In previous versions of Microsoft Access, each of the contexts in which an expression is used shared a single, common expression evaluator. This means that regardless of where you use an expression, the functions and operators available to you are, in most cases, the same. In Access 2010, the Expression Builder is context-sensitive. For example, the FormatDateTime function is not available in the context of a table but is available in all other contexts. Also notice in Figure 10 that the operator buttons are gone. The operators are available in the Expression Elements pane. All of these changes reduce clutter, make choices much more manageable, and provide more editing area.

In the macro designer and in other places in Access 2010 where you want to build an expression, clicking the icon shown in Figure 11 displays the Expression Builder.

Figure 11. Expression Builder icon

Then as you type an expression, you are provided with a drop-down list of fields, functions, or expression elements similar to those shown in Figure 12.

Figure 12. Typing displays a dropdown list of options

You can find much more help on expressions and about how to use the Expression Builder in Access 2010 help.

Macros and Expressions Made Easier

Whether you are a new user of Microsoft Access or a seasoned developer, the ease-of-use improvements brought about by the new Macro Designer make it easier than ever to construct macros. The addition of the Else and Else If constructs make macros much more flexible. And the new Action Catalog and IntelliSense feature eliminate the need to memorize frequently used commands and actions. Additionally, the improved Expression Builder make creating expressions much more straight-forward. In total, these improvements give you more control over the functionality of the databases that you create.

Additional Resources

Find more information about the topics described in this article, see the following resources:

• Tutorials: Access 2010 Macro Designer

• What's New for Developers in Office 2010

• Access 2010: How to write expressions for web queries, forms, and reports