[ Team LiB ] Previous Section Next Section

The Basics of Creating a Macro

To create a macro, you click the Macros tab from the Database window and then click New to open the Macro design window shown in Figure 13.1. In this window, you can build a "program" by adding macro actions, arguments, names, and conditions to the macro.

Figure 13.1. The Macro design window, showing the Action and Comment columns.

graphics/13fig01.jpg

Macro actions are like programming commands or functions; they instruct Access to take a specific action—for example, to open a form. Macro arguments are like parameters to a command or function; they give Access specifics on the selected action. For example, if the macro action instructs Access to open a form, the arguments for that action tell Access which form it should open and how it should open it (in Form, Design, or Datasheet view, or using Print Preview).

Macro names are like subroutines. You can include several subroutines in one Access macro, and you identify each of these subroutines by its macro name. Macro conditions allow you to determine when Access executes a specific macro action. For example, a company might want an inventory reorder form to open if an item is low or out of stock. If the item is fully stocked, perhaps just a detailed description form would display.

Macro Actions

As mentioned earlier, a macro action instructs Access to perform a task. You can add a macro action to the Macro design window in several ways. One method is the following:

  1. Click a cell in the Action column.

  2. Click to open the drop-down list (see Figure 13.2). A list of all the macro actions appears.

    Figure 13.2. The Action drop-down list, showing all the available macro actions.

    graphics/13fig02.jpg

  3. Select the action that you want from the list. Access adds it to the macro.

You use this method of selecting a macro action if you aren't sure of the macro action's name and want to browse the available actions.

After you have been working with macros for a while, you will know which actions you want to select. Rather than open the drop-down list and scroll through the entire list of actions, you can click a cell in the Action column and then start typing the name of the macro action you want to add. Access fills in the field with the first macro action that begins with the character(s) you type.

You use the OpenTable, OpenQuery, OpenForm, OpenReport, and OpenModule actions to open a table, query, form, report, and module, respectively. You can select these actions and their associated arguments quite easily by using a drag-and-drop technique:

  1. Open the Chap13Ex database, which is available at www.samspublishing.com.

  2. Select Macros from the list of objects in the Database window.

  3. Click New to create a new macro. The Macro design window appears.

  4. Tile the Database window and the Macro design window on the desktop (see Figure 13.3).

    Figure 13.3. The Database window and Macro design window, tiled.

    graphics/13fig03.jpg

  5. Select the appropriate tab from the Database window. For example, if you want to open a form, select the Forms tab.

  6. Click and drag the object you want to open to the Macro design window. Access automatically fills in the appropriate action and arguments. Figure 13.4 shows the effects of dragging and dropping the frmClients form onto the Macro design window.

    Figure 13.4. The Macro design window after the frmClients form is dragged and dropped onto it.

    graphics/13fig04.jpg

  7. Close the Database window and maximize the Macro design window.

Dragging and dropping a table, query, form, report, or module onto the Macro design window saves you time because Access automatically fills in all the macro action arguments for you. Notice in Figure 13.4 that Access associates six action arguments with the OpenForm action: Form Name, View, Filter Name, Where Condition, Data Mode, and Window Mode. The drag-and-drop process fills in three of the arguments for the OpenForm action: the name of the form (frmClients), the view (Form), and the window mode (Normal). The next section more thoroughly covers macro action arguments.

Action Arguments

As mentioned previously, macro action arguments are like command or function parameters: They give Access specific instructions on how to execute the selected macro action. The available arguments differ, depending on what macro action you select. Some macro action arguments force you to select from a drop-down list of appropriate choices; others allow you to enter valid Access expressions. Access automatically fills in macro action arguments when you click and drag a Table, Query, Form, Report, or Module object to the Macro design window. In all other situations, you must supply Access with the arguments required to properly execute a macro action.

To specify a macro action argument, follow these steps:

  1. Select a macro action.

  2. Press the F6 key to jump down to the first macro action argument for the selected macro action.

  3. If the macro action argument needs to be selected from a list of valid choices, click to open the drop-down list of available choices for the first macro action argument associated with the selected macro action. Figure 13.5 shows all the available choices for the Form Name argument associated with the OpenForm action. Because the selected argument is Form Name, Access displays in the drop-down list the names of all the forms included in the database.

    Figure 13.5. Available choices for the OpenForm argument.

    graphics/13fig05.jpg

  4. If the macro action argument requires you to enter a valid expression, you can type the argument into the appropriate text box or get help from the Expression Builder. Take a look at the Where Condition argument of the OpenForm action, for example. After you click in the Where Condition text box, an ellipsis appears. If you click the ellipsis, Access invokes the Expression Builder dialog box. (See Figure 13.6.)

    Figure 13.6. Adding complex expressions to macros.

    graphics/13fig06.jpg

  5. To build an appropriate expression, select a database object from the list box on the left and then select a specific element from the center and right-hand list boxes. Click Paste to paste the element into the text box. In Figure 13.6, the currently selected database object is Built-in Functions, and the currently selected elements are Date/Time and Date. Click OK to close the Expression Builder. The completed expression appears in Figure 13.7.

    Figure 13.7. The completed expression for the Where Condition argument of the OpenForm action.

    graphics/13fig07.jpg

Remember that each macro action has different macro action arguments. Access requires some of the arguments associated with a particular macro action, and others are optional. If you need help with a particular macro action argument, you can click the argument, and Access gives you a short description of that argument. If you need more help, you can press F1 to see Help for the macro action and all its arguments. (See Figure 13.8.)

Figure 13.8. Help on the OpenForm action.

graphics/13fig08.jpg

Macro Names

As mentioned previously, macro names are like subroutines: They allow you to place more than one routine in a macro. This means you can create many macro routines without having to create several separate macros. You should include macros that perform related functions within one particular macro. For example, you might build a macro that contains all the routines required for form handling and another that has all the routines needed for report handling.

You need to take only two steps to add macro names to a macro:

  1. Click the Macro Names button on the Macro Design toolbar or choose View | Macro Names. The Macro Name column appears. (See Figure 13.9.)

    Figure 13.9. Creating subroutines within a macro.

    graphics/13fig09.jpg

  2. Add macro names to each macro subroutine. Figure 13.10 shows a macro with three subroutines: OpenFrmClients, OpenFrmTimeCards, and CloseAnyForm. The OpenFrmClients subroutine opens the frmClients form, showing all the clients added in the past 30 days. The OpenFrmTimeCards subroutine opens the frmTimeCards form, and the CloseAnyForm subroutine displays a message to the user and then closes the active form.

    Figure 13.10. A macro with three subroutines.

    graphics/13fig10.jpg

graphics/book_icon.gif

The Macro Name column toggles. You can hide it and show it at will, without losing the information in the column.


Macro Conditions

At times, you want a macro action to execute only when a certain condition is true. Fortunately, Access allows you to specify the conditions under which a macro action executes:

  1. Click the Conditions button on the Macro Design toolbar or choose View | Conditions. The Condition column appears. (See Figure 13.11.)

    Figure 13.11. Designating the condition under which a macro action executes in the Condition column of a macro.

    graphics/13fig11.jpg

  2. Add the conditions you want to each macro action.

The macro pictured in Figure 13.12 evaluates information entered on a form. The CheckBirthDate subroutine evaluates the date entered in the txtBirthDate text box. Here's the expression entered in the first condition:

DateDiff("yyyy",[Forms]![frmPersonalInfo]! _
    [txtBirthDate],Date()) Between 25 And 49
Figure 13.12. An example of a macro that contains conditions.

graphics/13fig12.jpg

This expression uses the DateDiff function to determine the difference between the date entered in the txtBirthDate text box and the current date. If the difference between the two dates is between 25 and 49 years, the macro displays a message box indicating that the person is over a quarter century old.

The ellipsis on the second line of the CheckBirthDate subroutine indicates that Access should execute the macro action only if the condition entered on the previous line is true. In this case, if the condition is true, the action terminates the macro.

If the value in the text box doesn't satisfy the first condition, the macro continues evaluating each condition in the subroutine. The CheckBirthDate subroutine displays an age-specific message for each person 25 years of age and older. If the person is younger than 25, the text box value does not meet any of the conditions, and the macro does not display a message.

The CheckGender subroutine works a little bit differently. It evaluates the value of the optGender option group. One of the first two lines of the subroutine executes, depending on whether the user selects the first or second option button. The third line of the subroutine executes, regardless of the Option Group value because the macro does not contain an ellipsis on that line of the macro action's Condition column. If you do not enter an ellipsis on any line of the subroutine, the macro action executes unconditionally. If you place an ellipsis before the line, the macro action executes only if the value of OptGender is 2.

    [ Team LiB ] Previous Section Next Section