• 11
• What Are Macros and When Do You Need Them?
• Why Learning About Macros Is Important
• The Basics of Creating and Running a Macro
• Macro Actions
• Action Arguments
• Macro Names
• Macro Conditions
• Running an Access Macro
• Running a Macro from the Macro Design Window
• Running a Macro from the Macros Tab
• Triggering a Macro from a Form or Report Event
• Modifying an Existing Macro
• Inserting New Macro Actions
• Deleting Macro Actions
• Moving Macro Actions
• Copying Macro Actions
• Documenting Your Macro: Adding Comments
• Testing a Macro
• When You Should Use Macros and When You Shouldn't
• Converting a Macro to VBA Code
• Creating an AutoExec Macro
• Creating an AutoKeys Macro
• The DoCmd Object
• Practical Examples: Adding an AutoExec Macro to the Time and Billing Application
• Summary

11

What Are Macros and When Do You Need Them?

Why Learning About Macros Is Important

Although macros should not be used to develop the routines that control your applications, a few specific application-development tasks can be accomplished only by using macros. It is therefore important to understand at least the basics of how macros work. Furthermore, using Access 95 macros can often help you to get started with the application-development process because Access 95 macros can be converted to VBA code. This means that you can develop part of your application using macros, convert the macros to VBA code, and proceed with the development process. Although I don't recommend this approach for serious developers, it provides a great jump-start for those new to Access or Windows development in general.

The Basics of Creating and Running a Macro

To create a macro, click the Macros tab and then click New. The Macro Design window pictured in Figure 11.1 appears. This window allows you to build a "program" by adding macro actions, arguments, names, and conditions to the macro.

Figure 11.1. The Macro Design window showing the macro action and comments columns.

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 provide Access with specifics regarding the selected action. For example, if the macro action instructs Access to open a form, the arguments for that action tell Access which form is to be opened and how it is to be opened (Form, Design, or Datasheet view or Print Preview). Macro names are like subroutines. Multiple subroutines can be included within one Access macro. Each of these routines is identified by its macro name. Macro conditions allow you to determine when a specific macro action will execute. For example, you might want one form to open in one situation and a second form to open in another situation.

Macro Actions

As mentioned, macro actions instruct Access to perform a task. You can add a macro action to the Macro Design window in several ways. One method is to click a cell within the Macro Action column and then click to open the drop-down menu (see Figure 11.2). A list of all the macro actions appears. Select the desired action from the list, and it is added instantly to the macro. Use this method of selecting a macro action if you are not sure of the name of the macro action and want to browse the available actions.

Figure 11.2. The Macro Action drop-down showing all of the available macro actions.

After you have been working with macros for a while, you'll know which actions you want to select. Rather than opening the drop-down and scrolling through the entire list of actions, you can click a cell within the Action column and then begin to type the name of the macro action you want to add. Access locates the first macro action beginning with the character(s) you type.

The OpenTable, OpenQuery, OpenForm, OpenReport, and OpenModule actions are used to open a table, query, form, report, or module, respectively. These actions and associated arguments can all be filled in quite easily using a drag-and-drop technique:

1. Tile the Database window and the Macro Design window on the desktop (see Figure 11.3).
   
   

Figure 11.3. The Database window and Macro Design window tiled.

1. Select the appropriate tab from the Database window. For example, if you want to open a form, select the Forms tab.
   
   
2. Click and drag the object you want to open to the Macro Design window. The appropriate action and arguments is automatically filled in. Figure 11.4 shows an example where the frmClients form was dragged and dropped onto the Macro Design window.
   
   

Figure 11.4. The Macro Design window after the frmClients form was dragged and dropped on it.

The process of dragging and dropping a table, query, form, report, or module onto the Macro Design window saves you some time because all the macro action arguments are automatically filled in for you. Notice in Figure 11.4 that six action arguments are associated with the OpenForm action: Form Name, View, Filter Name, Where Condition, Data Mode, and Window Mode. The process of dragging and dropping the frmClients form to the Macro Design window filled in four of the arguments for the OpenForm action. The name of the form (frmClients), the view (Form), the data mode (edit), and the window mode (normal) were all automatically filled in. Macro action arguments are covered more thoroughly in the next section.

Action Arguments

A macro action argument is like a parameter to a command or function. The macro action arguments that are available differ depending on what macro action has been selected. The macro action arguments give Access specific instructions on how to execute the selected macro action. Some macro action arguments force you to select from a drop-down containing appropriate choices; others allow you to enter a valid Access expression. Macro action arguments are automatically filled in 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 five steps:

1. Select a macro action.
   
   
2. Press the F6 function key on the keyboard to jump down to the first macro action argument for the selected macro action.
   
   
3. If the macro action argument requires that you select from a list of valid choices, click to open the drop-down for the first macro action argument associated with the selected macro action. All the available choices will appear. Figure 11.5 shows all the available choices for the Form Name argument associated with the OpenForm action. Because the selected argument is Form Name, the names of all the forms included in the database are listed in the drop-down.
   
   

Figure 11.5. Available choices for OpenForm argument.

1. If the macro action argument requires that you enter a valid expression, you can type the argument into the appropriate text box, or you can solicit assistance from the Expression Builder. An example is the Where Condition argument of the OpenForm action. After you click the Where Condition text box, an ellipsis appears. If you click on the ellipsis, the Expression Builder dialog is invoked (see Figure 11.6).
   
   

Figure 11.6. The Expression Builder dialog allows you to easily add complex expressions to your macros.

1. To build an appropriate expression, select a database object from the list box on the left, 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 11.6, the currently selected database object is Built-in Functions. The currently selected elements are Date/Time and Date. Click OK to close the Expression Builder dialog. The completed expression appears in Figure 11.7.
   
   

Figure 11.7. Completed expression for Where argument of OpenForm action.

It is important to remember that each macro action has different macro action arguments. Some of the macro action arguments associated with a particular macro action are required, whereas others are optional. If you need help on a particular macro action argument, click the specific argument. Access provides you with a short description of the selected argument. If you need additional assistance, press F1; Help appears for the macro action and all its arguments (see Figure 11.8).

Figure 11.8. Help on OpenForm action.

Macro Names

Macro names are like subroutines. They allow you to place more than one routine within a macro. This means that you can create many macro routines without having to create a large volume of separate macros. It is appropriate to 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 contains all the routines required for report handling.

Only two steps are needed to add macro names to a macro:

1. Click the Macro Names button on the Macro Design toolbar. The Macro Names column appears (see Figure 11.9).
   
   

Figure 11.9. The Macro Names column allows you to create subroutines within a macro.

1. Add macro names to each macro subroutine. Figure 11.10 shows a macro with three subroutines: OpenFrmClients, OpenFrmTimeCards, and CloseAnyForms. The OpenFrmClients subroutine opens the frmClients form showing all the clients added within the last 30 days. The OpenFrmTimeCards subroutine opens the frmTimeCards form. The CloseAnyForms subroutine displays a message to the user and then closes the active form.
   
   

Figure 11.10. A macro containing three subroutines.

The Macro Names column of the macro is a toggle. You can hide it and show it at will, without losing the information within the column.

Macro Conditions

At times you'll 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. The Macro Conditions column appears (see Figure 11.11).
   
   

Figure 11.11. The Conditions column of a macro allows you to designate a condition under which a macro action executes.

1. Add conditions to each macro action as desired.
   
   

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

DateDiff("yyyy",[Forms]![frmPersonalInfo]![txtBirthDate],Date())

 Between 25 And 49

Figure 11.12. Example of a macro containing conditions.

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, a message box is displayed indicating that the individual is over a quarter century old.

Notice the ellipsis on the second line of the CheckBirthDate subroutine. The ellipsis indicates to Access that the macro action should be executed only if the condition entered on the previous line is True. In this case, if the condition is True, the macro is terminated.

If the first condition is not satisfied, the macro continues evaluating each condition within 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, none of the conditions are met, and no message are displayed.

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 first or second option button is selected. The third line of the subroutine executes regardless of the Option Group value. This is because no ellipsis is entered in the Condition column of the macro action. If no ellipsis is found on any line of the subroutine, the macro action executes unconditionally. If an ellipsis were placed before the line, the macro action would execute only if the value of OptGender was 2.

Running an Access Macro

You have learned quite a bit about macros but have not yet learned how to execute them. The process of executing a macro varies depending on what you are attempting to accomplish. A macro can be run from the Macro Design window, from the Macros tab, by being triggered from a Form or Report event, or by selecting a Menu or Toolbar option. The first three methods are covered here. Invoking a macro from a Menu or Toolbar option is covered in Chapter 13, "Let's Get More Intimate With Forms: Advanced Techniques."

Running a Macro from the Macro Design Window

A macro can be executed easily from the Macro Design window. It is easy to run a macro without subroutines. Click Run on the Macro Design toolbar. Each line of the macro is executed unless conditions have been placed on specific macro actions. After you click the Run button of mcrClients (pictured in Figure 11.13), the frmClients form is opened.

Figure 11.13. Running a macro from the Macro Design window.

From Macro Design view, you can run only the first subroutine within a macro. To run a macro containing subroutines, click Run on the Macro Design toolbar. The first subroutine within the macro executes. As soon as the second macro name is encountered, the macro execution terminates.

Running a Macro from the Macros Tab

To run a macro from the Macros tab of the Database window, follow these two steps:

1. Click the Macros tab of the Database window.
   
   
2. Double-click the name of the macro you want to execute, or click the name of the macro and then click Run.
   
   

If the macro you execute contains macro names, only the macro actions with the first subroutine are executed.

Triggering a Macro from a Form or Report Event

Chapter 10, "The Real Scoop on Objects, Properties, and Events," introduces the concept of executing code in response to an event. Here you learn how to associate a macro with a command button.

The form pictured in Figure 11.14 illustrates the process of associating a macro with the Click event of a command button on a form. Four steps are needed to associate a macro with a Form or Report event:

1. Select the object you want to associate the event with. In the example, the cmdCheckGender command button object is selected.
   
   

Figure 11.14. Associating a macro with a Form or Report event.

1. Open the Properties window and click to select the Event properties.
   
   
2. Click the event that you want the macro to execute in response to. In the example, the Click event of the command button is selected.
   
   
3. Use the drop-down to select the name of the macro you want to execute. If the macro contains macro names, make sure you select the correct macro name subroutine. In the example, the macro mcrPersonalInfo and the macro name CheckGender have been selected. Notice the period between the name of the macro and the name of the macro name subroutine.
   
   

[ic:tryit]To practice implementing the techniques you have learned, build the macro pictured in Figure 11.12:

1. Click the Macros tab of the Database window.
   
   
2. Click New.
   
   
3. Click the macro names and condition buttons on the Macro Design toolbar to show both the Macro Names and Condition columns of the Macro Design window.
   
   
4. Enter all the macro names, actions, arguments, and conditions shown in Table 11.1.
   
   
5. Save and name the macro mcrPersonalInfo.
   
   
6. Build a form.
   
   
7. Add an option group containing two option buttons. Set one of their Text properties to Male and the other to Female. Set one of their values to 1 and the other to 2. Name the option group optGender.
   
   
8. Add a text box for the birth date. Set the Format and Input Mask properties to Short Date. Name the text box txtBirthDate.
   
   
9. Add two command buttons to the form. Name the first button cmdCheckGender and set its Text property to Check Gender. Name the second button cmdCheckAge and set its Text property to Check Age. Set the Click event of the first command button to mcrPersonalInfo.CheckGender. Set the Click event of the second command button to mcrPersonalInfo.CheckAge.
   
   
10. Save the form as frmPersonalInfo.
    
    
11. Test the macros by clicking on each of the command buttons after selecting a gender and entering a birth date.
    
    

Table 11.1. The mcrPersonalInfo macro.

Macro Name

Macro Condition

Macro Action

Argument

Value

Modifying an Existing Macro

You have learned quite a bit about macros. You have learned how to create a macro, add macro actions and their associated arguments, create macro subroutines by adding macro names, and conditionally execute the actions within the macro by adding macro conditions. Once a macro has been created, you might want to modify it. To modify a macro, you must first enter Design view for the macro:

1. Click the Macros tab of the Database window.
   
   
2. Select the macro you want to modify.
   
   
3. Click Design.
   
   

The design of the macro appears. You are now ready to insert new lines, delete existing lines, move the macro actions around, or copy macro actions to the macro you are modifying or to another macro.

Inserting New Macro Actions

To insert a macro action:

1. Click the line above which you want the macro action to be inserted.
   
   
2. Tap your Insert key, click the Insert Row button on the toolbar, or select Insert|Row. A new line is inserted in the macro at the cursor.
   
   

To insert multiple macro actions:

1. Place your cursor on the line above which you want the new macro action lines to be inserted. Click the Macro Action Selector. Macro Action Selectors are the gray boxes that appear to the left of the Macro Action column.
   
   
2. Click and drag to select the same number of Macro Action Selectors as the number of macro actions you want to insert.
   
   
3. Tap the Insert key, click the Insert Row button on the toolbar, or select Insert|Row. All new macro lines are inserted above the macro actions that were selected.
   
   

Deleting Macro Actions

To delete a macro action:

1. Click the Macro Action Selector of the macro action you want to delete.
   
   
2. Tap the Delete key, click Delete Row on the toolbar, or select Edit|Delete Row.
   
   

To delete multiple macro actions:

1. Click and drag to select the Macro Action Selectors of all the macro actions you want to delete. All the macro actions should become black (see Figure 11.15).
   
   
2. Tap the Delete key, click Delete Row, or select Edit|Delete Row.
   
   

Figure 11.15. Selecting and deleting macro actions.

Moving Macro Actions

You can move macro actions in a few ways, including dragging and dropping and cutting and pasting.

To move macro actions by dragging and dropping:

1. Click and drag to select the macro action(s) you want to move.
   
   
2. Release the mouse button.
   
   
3. Place your mouse cursor over the Macro Action Selector of any of the selected macro actions.
   
   
4. Click and drag. A black line appears, indicating where the selected macro actions will be moved.
   
   
5. Release the mouse button.
   
   

If you accidentally drag and drop the selected macro actions to an incorrect place, use the Undo button on the Macro Design toolbar to reverse your action.

To move macro actions by cutting and pasting:

1. Click and drag to select the Macro Action Selectors of the macro actions you want to move.
   
   
2. Click Cut on the Macro Design toolbar (or use Ctrl+X).
   
   
3. Click within the line above which you want the cut macro actions to be inserted. Do not click the Macro Action Selector.
   
   
4. Click Paste. The macro actions are inserted at the cursor.
   
   

Do not click the Macro Action Selector of the row where you want to insert the cut macro actions unless you want to overwrite the macro action you have selected. If you do not click to select the Macro Action Selectors, the cut lines are inserted into the macro without overwriting any other macro actions. If you click to select Macro Action Selectors, existing macro actions are overwritten.

Copying Macro Actions

Macro actions can be copied within a macro or to another macro. To copy macro actions within a macro:

1. Click and drag to select the Macro Action Selectors of the macro actions you want to copy.
   
   
2. Click Copy on the Macro Design toolbar (or use Ctrl+C).
   
   
3. Click within the line above which you want the copied macro actions to be inserted. Do not click any Macro Action Selectors unless you want to overwrite existing macro actions (see previous Warning).
   
   
4. Click Paste. The macro actions are inserted at the cursor.
   
   

To copy macro actions to another macro:

1. Click and drag to select the Macro Action Selectors of the macro actions you want to copy.
   
   
2. Click Copy on the Macro Design toolbar (or use Ctrl+C).
   
   
3. Open the macro that will include the copied actions.
   
   
4. Click within the line above which you want the copied macro actions to be inserted.
   
   
5. Click Paste. The macro actions are inserted at the cursor.
   
   

Documenting Your Macro: Adding Comments

Just as it is useful to document any program, it is useful to document what you are trying to accomplish within your macro. These comments can be used when you or others are attempting to modify your macro at a later time. They can also be used as documentation because they print when you print the macro.

To add a comment to a macro, click the Comment column of the macro and begin to type. Figure 11.16 shows a macro with comments. As you can see in Figure 11.17, these comments appear in the printed macro.

Figure 11.16. Adding comments to a macro.

Figure 11.17. Comments included in the printed macro.

Testing a Macro

Access does not provide very sophisticated tools for testing and debugging your macros. However, it does provide a method by which you can step through each line of a macro:

1. Open the macro in Design view.
   
   
2. Click Step on the toolbar.
   
   
3. To execute the macro click Run. The first line of the macro is executed. The Macro Single Step dialog appears (see Figure 11.18). This dialog shows you the Macro Name, Condition, Action Name, and Arguments. In the figure, the Macro Name is mcrPersonalInfo, the Condition evaluates to False, and the Action Name is MsgBox. The MsgBox arguments are You Are Over a Quarter Century Old, Yes, and Information.
   
   
4. To continue stepping through the macro, click Step. If you want to halt execution of the macro without proceeding, click Halt. To continue normal execution of the macro without stepping, click Continue.
   
   

Figure 11.18. The Macro Single Step dialog allows you to view the macro name, condition, action name, and arguments for the current step of the macro.

[ic:tryit]It is easiest to learn about the process of stepping through a macro by experiencing it first-hand. To begin, open the mcrPersonalInfo macro created in the last example in Design view, and follow these steps:

1. Click the Step button on the Macro Design toolbar.
   
   
2. Run the frmPersonalInfo form, also created in the last example.
   
   
3. Select a gender and type in a birth date.
   
   
4. Click the Check Gender command button. This should invoke the Macro Single Step dialog.
   
   
5. Step through the macro one step at a time.
   
   
6. View the Macro Name, Condition, Action Name, and Arguments for each step.
   
   
7. Change the gender and run the macro again. Carefully observe how this affects the execution of the macro.
   
   
8. Next click the Check Birth Date command button, and step through the macro one step at a time, viewing whether the condition evaluates to True or False.
   
   
9. After the macro completes, try entering a different value for the birth date.
   
   
10. Step through the macro again and carefully observe whether the condition evaluates to True of False for each step.
    
    

As you can see, although Microsoft provides some tools to help you debug your macro, you'll probably agree that they are quite limited. This is one reason why most developers prefer to develop applications using VBA code.

The Step button on the Macro Design toolbar is a toggle. Once you activate Step Mode, it is activated for all macros within the current database and all other databases until you either turn the toggle off or exit Access. This can be quite surprising if you do not expect it. You might have invoked Step Mode in another database quite a bit earlier in the day, only to remember that you forgot to click the toggle button when some other macro unexpectedly goes into Step Mode.

When You Should Use Macros and When You Shouldn't

As mentioned, certain tasks within Access can be performed only by using macros. These tasks include:

• Creation of menus (AddMenu macro action)
  
  
• Reassignment of key combinations (AutoKeys macro)
  
  
• Assignment of a custom action to a toolbar
  
  
• Automation of Access by other applications using Dynamic Data Exchange (DoCmd)
  
  

Although this chapter provides you with many other examples of macros, these are provided only so that you can obtain a cursory understanding of macros and how they work. Developers should only use macros to accomplish the four tasks listed here. As you'll see later in this section, VBA code is much more appropriate for most of the tasks that your application performs.

Although macros are not your only choice as a starting point for your applications in Access 95, the AutoExec macro is one of two choices for this task. For more about the AutoExec macro, see the section in this chapter titled "Creating an AutoExec Macro."

Macros must be used for certain tasks, but they are not the best tool for creating code that controls industrial-strength applications because they are quite limited in both function and capability. Access macros are limited in the following ways:

• You cannot include error handling in an Access macro.
  
  
• You cannot create user-defined functions using macros.
  
  
• Access macros do not allow you to create variables or pass parameters.
  
  
• Access macros provide no method of processing table records one at a time.
  
  
• Using Access macros, you cannot use Object Linking and Embedding Automation or Dynamic Data Exchange to communicate with other applications.
  
  
• It is more difficult to debug Access macros than it is to debug VBA code.
  
  
• Transaction processing cannot be accomplished using Access macros.
  
  
• You cannot call Windows API functions using Access macros.
  
  
• You cannot perform any replication using Access macros.
  
  
• Access macros do not allow you to create database objects at runtime.
  
  

Converting a Macro to VBA Code

Now that you have discovered all the limitations of macros, you might be thinking about all the macros you have already written that you wish you had developed using VBA code. Or, after seeing how easy it is to accomplish certain tasks using macros, you might be disappointed to learn how limited macros are. Fortunately, Access 95 comes to the rescue—it is easy to convert an Access macro to VBA code. Once the macro has been converted to VBA code, the code can be modified just like any VBA module. The process of converting an Access macro to VBA code consists of six steps:

1. Open the macro you wish to convert in Design view.
   
   
2. Select File|Save As/Export.
   
   
3. Click the Save As Visual Basic Module option button (see Figure 11.19).
   
   

Figure 11.19. The macro File|Save As/Export dialog allows you to save a macro as a Visual Basic module.

1. Click OK. The Convert Macro dialog appears (see Figure 11.20).
   
   

Figure 11.20. The Convert Macro dialog appears when you are saving a macro as a Visual Basic module. It allows you to specify whether error handling and comments will be added to the Visual Basic module.

1. Indicate whether you want to add error handling and comments to the generated code. Click Convert.
   
   
2. You receive indication that the conversion completed. Click OK.
   
   
3. The converted macro appears under the list of modules with "Converted Macro:" followed by the name of the macro. Click Design to view the results of the conversion process.
   
   

Figure 11.21 shows a converted macro. The macro is converted into distinct subroutines, one for each macro name. The macro is complete with logic, comments, and error handling. All macro conditions are converted into If...Else...End If statements. All macro comments are converted into VBA comments. Basic error-handling routines are automatically added to the code.

Figure 11.21. A converted macro as a module.

When you convert a macro to a Visual Basic module, the original macro remains untouched. Furthermore, all of the objects in your application will still call the macro. It is important that you realize that to effectively utilize the macro conversion options, you must find all places where the macro was called and replace the macro references with calls to the VBA function.

Creating an AutoExec Macro

Prior to Access 95, the only way to have something happen when a database was opened was to use an AutoExec macro. With Access 95, you can use either an AutoExec macro or Startup options to determine what occurs when a database is opened. Nevertheless, using an AutoExec macro to launch the processing of your application is certainly a viable option.

My personal preference is to include as few macros in my application as possible. For this reason, I tend to designate a startup form for my application. The startup form calls a custom "AutoExec" routine when it is opened. This methodology is covered in Chapter 35, "Distributing Your Application with the Access Developer's Toolkit."

The process of creating an AutoExec macro is quite simple. An AutoExec macro is just a normal macro saved with the name AutoExec. An AutoExec macro usually performs tasks such as hiding or minimizing the Database window and opening a Startup form or switchboard. The macro pictured in Figure 11.22 hides the Database window, displays a welcome message, and then opens the frmClients form.

Figure 11.22. Example of an AutoExec macro.

When you are opening your own database to make changes or additions to the application, you'll probably not want the AutoExec macro to execute. To prevent it from executing, hold down your Shift key as you open the database.

Creating an AutoKeys Macro

An AutoKeys macro allows you to redefine keystrokes within your database. You can map selected keystrokes to a single command or to a series of commands. Follow six steps to build an AutoKeys macro:

1. Open a new macro in Design view.
   
   
2. Make sure the Macro Name column is visible.
   
   
3. Enter a Key Name in the Macro Name column. Allowable Key Names are defined in Access Help and can be found in Figure 11.23.
   
   

Figure 11.23. List of valid Key Names for AutoKey macro.

1. Select the macro action you want to associate with the Key Name. You can apply conditions and arguments just as in a normal macro. You can have Access execute multiple commands in one of three ways: Associate multiple macro actions with a Key Name, perform a RunCode Action, or perform a RunMacro action.
   
   
2. Continue adding Key Names and macro actions to the macro as desired. Separate each Key Name by one blank line to improve readability.
   
   
3. Save the macro as AutoKeys. The moment you save the macro, the Key Names are in effect, and the keystrokes are remapped. The AutoKeys macro comes into effect automatically each time you open the database.
   
   

It is generally not a good idea to remap common Windows or Access keystrokes. Your users become accustomed to certain keystrokes having certain meanings in all Windows applications. If you attempt to alter the definition of these common keystrokes, your users will become confused and frustrated. It is therefore important to use keystroke combinations that are rarely, if ever, used within Windows.

The DoCmd Object

Most macro commands can be performed in VBA code using the DoCmd object. The macro action becomes a method of the DoCmd object. The arguments associated with each macro action become the arguments of the method. For example, the following method of the DoCmd object is used to open a form:

DoCmd.OpenForm "frmClients", acNormal, "", "[tblClients]![IntroDate]>Date() _

-30", acEdit, acNormal

The OpenForm method of the DoCmd object opens that form that appears as the first argument to the method. The second argument indicates the view in which the form will be opened. The third and fourth arguments are used to specify a Filter and Where Condition, respectively. The fifth argument of the OpenForm method is used to specify the Data Mode for the form (Add, Edit, or Read Only). The sixth argument is used to indicate the Window Mode (Normal, Hidden, Minimized, or Dialog).

Notice the intrinsic constants that are used for the OpenForm arguments. These intrinsic constants greatly aid in the readability of the code. They can be found in Help for each DoCmd method.

Practical Examples: Adding an AutoExec Macro to the Time and Billing Application

In Chapter 13, "Getting More Intimate with Forms: Advanced Techniques," you learn how to add a switchboard to your application. In Chapter 34, "Distributing Your Application," you learn how to use Startup options to designate a Startup form for your application. For now, we'll build an AutoExec macro that acts as the launching point for our application. The macro starts the application by hiding the Database window, displaying a message to the user and opening the frmClients form.

Build the macro pictured in Figure 11.22. Start by opening a new macro in Design view. Set the first action of the macro to DoMenuItem. Set the Menu Bar to Database, the Menu Name to Window, and the Command to Hide. The DoMenuItem macro action and selected arguments serve to hide the Database window. Set the second action of the macro to MsgBox. Set the Message to "Welcome to the Client Billing Application." Set Beep to No, the Type to Information, and the Title to "Welcome." The final action of the macro opens the frmClients form. Set the action to OpenForm. Set the FormName to frmClients. Leave the rest of the arguments at their default values.

Close and reopen the database. The AutoExec macro should automatically execute when the database is opened. Close the database and open it again, holding down the Shift key to prevent the macro from executing.

Summary

Many end-users attempt to develop entire applications using macros. Although this is possible, it is not appropriate. Macros do not provide the developer with adequate control, error handling, debugging capabilities, and many other features that are requisite to successful application development.

Although macros should not be used to develop your application, certain tasks can be accomplished only by macros. These include the creation of menus, the reassignment of key combinations, the assignment of a custom action to a toolbar, and the automation of Access by other applications using DDE. Furthermore, an AutoExec macro is one of two ways that you can determine what happens when an application loads. Unless you are using a macro to perform one of these tasks, you should always automate your application using VBA code.