If you find yourself doing the same routine task over and over again, you might want to consider creating a macro to complete the task for you. A macro helps you perform routine tasks by automating them. Instead of manually performing a series of time-consuming, repetitive actions, you can record a single macro that does the entire task all at once for you.
For example, instead of clicking the Reports icon in the Objects bar in the
database window, finding and opening a specific report, printing it, and then
closing it, you could create a macro to print the report with the click of a
single button.
A macro is a set of one or more actions that perform a particular operation,
such as opening a form or printing a report. Macros can help you to automate
common tasks. For example, you can run a macro that prints a report when a user
clicks a command button.
In a way, you can think of macros as a very simple introduction to programming
because you can use them to create automated tasks and somewhat complex procedures.
Best of all, you don't have to know a single line of codeAccess provides you
with everything you need to write a macro.
Creating and Running a Macro
In some programs, such as Microsoft Excel or Word, you can create macros with a "macro recorder" to record your commands, keystrokes, and mouse clicks. Unfortunately, there isn't a "macro recorder" or Macro Wizard to help you create a macro in Microsoft Access. Don't worryit's not as difficult as it sounds. Working in Macro Design view really isn't all that different from working in Table Design viewit's where you define and edit your macro objects.
Simple macros that automate a single task, such as opening a form or report, are incredibly simple to createwe'll create such a macro in this lesson. More complicated macros with several steps or procedures may require a little bit of planning. Before you create a complicated macro, think about what you want the macro to do and the individual actions that are required to complete this operation. Practice the steps needed to carry out the operation and write them down as you goit will make writing the macro a lot easier.
And so, without any further ado, let's create our first macro.
1.Start Microsoft Access, open a new database, click the Macros icon in the
Objects bar, and click the New button.
The Action cell is where you tell Access what you want the macro to do.
2. Click the first blank Action cell, then click the list arrow.
A list of actions appears. An action, or command, is the basic building block
of a macroit's an instruction that tells Access what you want the macro to do.
There are more than 50 different actions you can choose from.
3. Scroll down and select MsgBox.
When you run the macro, the MsgBox action will display a message box that contains
a warning or an informational message. The Message argument is the most important
argument for the MsgBox action because it determines the text that is displayed
in the message box.
4. Click the Message argument box and type Welcome to Macros.
There are several other arguments you may want to specify for the MsgBox action,
such as the type of icon that is displayed in the message box (None, Critical,
Warning?, Warning!, or Information) and the text that is displayed in the title
bar of the message box.
5. Click the Type argument box, click the list arrow, and select Information.
Click the Title argument box and type Notice.
6. Click the first blank Comment cell and type This Macro displays a message
box.
Comments are completely optional, but they do make your macros easier to understand,
especially if other users will edit them.
6. Click the Save button on the toolbar, save the macro as mcrMsg and click
OK.
You're finished working in the Macro window for now so...
7. Close the Macro window.
Time to test your new macro. The Macros icon in the Objects bar in the Database
window should be selected.
8. Double-click the mcrMsg macro.
Access runs the mcrMsg macro and displays a message box.
Editing a Macro
Some Microsoft Access tasks require several steps. For example, a particular task might require you to (1) open a form, (2) select a specific record, (3) select a specific field in that record, and then (4) copy the information in that field to the Windows clipboard. Macros can contain as many actions as necessary to automate even the most complicated tasks. Each action appears in its own row and is evaluated and executed in the order in which it appears in the Macro window, from top to bottom.
In this lesson you will edit the macro you created in the previous lesson to change its arguments and add some more steps or actions.
You need a Form created before using this macro.
1. Select the mcrMsg macro and click the Design button.
The mcrMsg macro opens in Design view. Let's add two more actions to this macro.
2. Click the Action cell just below the MsgBox action, click the list arrow,
scroll down the list, and select Open Form.
The OpenForm action is added to the second line of the macro window. Most of
the time you will have to give Access more information about how to execute
each action. For example, here we will have to tell Access which form to open
with the OpenForm action. You use arguments to supply Microsoft Access with
information about how to carry out the action. Each type of action has its own
set of arguments, which appear in the Action Arguments panel, located at the
bottom of the macro window.
3. Click the Form Name text box in the Action Arguments panel, click the list
arrow, and the form u have.
That's the only argument we need to specify for this exercise, but notice that
there are additional arguments for the OpenForm action, such as the View argument,
which lets you select the view in which to open the form (Form view, Design
view, or Print Preview).
Let's add another action to the mcrEmployees macro.
4. Click the Action cell just below the OpenForm action, click the list arrow,
scroll down the list, and select Maximize.
When you run the macro, the Maximize action will maximize the window so that
it fills the entire Microsoft Access window. Because the Maximize action is
so simple and straightforward, it doesn't have any additional arguments.
5. Click the Save button on the toolbar.
That's all the we need to do for this lesson.
6. Close the macro window.
Let's test the macro.
7. Double-click the mcrMsg macro.
Sure enough, the mcrEmployees macro (1) displays the message box, (2) opens
the frmEmployees form, (3) maximizes the form window.
Working with Macro Groups
If you are creating lots of macros, you might want to consider organizing them into a macro group to help you manage them. A macro group stores several related macros together in a single macro object. When you create a macro group, you must give each macro in the macro group its own unique name to identify where each macro starts and ends. You do this by entering the macro names in the Macro Name column, which you display by clicking the Macro Names button on the toolbar.
When you combine two or more macros within the same macro group, you must run them separately, by referring to the macro group name, followed by the macro name. For example, mcrEmployees. mcrEmployees frmEmployees refers to the Open frmEmployees macro in the mcrEmployees macro group.
In this lesson you will learn how to group several related macros together in a macro group.
1. Select the mcrMsg macro and click the Design button.
In order to work with macro , you need to display the Macro Name column.
2. Click the Macro Names button on the View menu.
First you need to give the macro you created in the previous two lessons a name.
The cursor is already positioned in the Macro Name cell of the first row.
--------------------------------------------------------------------------------
Note: Always enter the macro name in the Macro Name column, next to the Action
where the macro starts.
--------------------------------------------------------------------------------
3. Type Open frmEmp in the first blank cell in the Macro Name column.
The macro name "Open frmEmp" identifies the macro you created in the
previous two lessons. To create another macro in the same macro group, type
its name in the Macro Name column next to the first action of the new macro.
4. Go to the fourth row.
The cursor should be positioned in the Macro Name column next to the first blank
Action row. This is where you will add another macro to the macro group.
5.Type Print rptEmp.
"Print rptEmp" is the name of the new macro we will create.
6. Click the Action cell to the right of the Print rptEmp macro name, click
the list arrow, scroll down, and select OpenReport.
Similar to the OpenForm action, which opens a form, the OpenReport action opens
a report. Next, you need to specify the arguments for the OpenReport action.
7. Click the Report Name text box in the Action Arguments panel, click the
list arrow, and select rpEmp.
This macro will open the rpEmp report. Notice Print appears in the View argument
boxthis will send the rpEmp report directly to the printer. Let's add a comment
to this new macro.
8. Click the blank Comment box in the Print rpEmp macro row and type This macro
prints the rpEmp report.
You want to add one more action to the Print rptEmployeeSales macro.
9. Click the Action cell just below the OpenReport action, click the list arrow,
scroll down, and select MsgBox.
You need to tell Access what you want the message box to say.
10. Click the Message argument box and type The Employee report has been sent
to the printer.
Let's specify several additional arguments for the MsgBox action, such as the
type of icon that is displayed in the message box.
11. Click the Type argument box, click the list arrow, and select Information. Click the Title argument box and type Notice.
12. Click the Save button on the toolbar and close the macro window.
Let's test our new macro. When you combine several macros within the same macro group you must run them separately using the Tools command on the Access menu.
--------------------------------------------------------------------------------
Note: Don't run a macro group by double-clicking it or selecting it and clicking
Run. Doing so will run every macro in the macro groupoften with disastrous results!
--------------------------------------------------------------------------------
13. Select Tools -> Macro -> Run Macro from the menu.
The Run Macro dialog box appears. Here's where you select the specific macro
you want to run.
14. Click the Macro Name list arrow, select mcrEmp.Open frmEmp, and click OK.
Access runs the Open frmEmp macro.
Assigning a Macro to an Event
Running macros from the Database window or menu is a pain in the neck. That's why most database developers assign macros to controlsparticularly, buttonsso that when a user clicks the button or control, a macro is activated.
1. Open the frmEmp form in Design view.
You want to add a command button to the frmEmployees form to print the report.
First you need to add the command button.
2. Click the Toolbox button, if necessary, and click the Command Button button
on the Toolbox.
The mouse pointer changes to a , indicating that you can click and drag the
command button control onto the form.
3. Place the pointer below the DOB field label and click and drag the pointer
down and to the right to create a command button Click Cancel if the Command
Button Wizard appears.
Let's give this button a more meaningful text label.
4. Make sure that the command button is still selected, then click its text
label and replace the text with Print Report. Click anywhere outside of the
command button when you're finished.
We're ready to assign a macro to the buttonto do this you will need to display
the command button's Properties.
5. Select the command button,right click and select the Properties.
The lists all the events to which you can assign a macromost of them you will
never use.
6. Click the On Click box, click the list arrow, and select mcrMsg.print rpEmp.
Close the Properties dialog box when you're finished.
Let's see how our new command button works.
7. Click the View button on the toolbar to switch to Form view, then click
the new Print Report button.
Microsoft Access runs the mcrMsg macro and sends the report to the printer.
8. click the Save button.
Event Properties That Can Trigger Macros
Event Description
Before Update Macro or function that runs when data in a field or record is
changed but before the changes are actually saved to the database. Often used
to validate data.
After Update Macro or function that runs when data in a field or record is changed
and is saved to the database.
On Change
Macro or function that runs when the contents of a text box or combo box changes
or when you move from one page to another page in a tab control.
On Enter
Macro or function that runs when a control first gets the focus (is selected).
The Enter event occurs before the focus moves to a particular control (before
the GotFocus event). You can use an Enter macro or event procedure to display
instructions when a form or report first opens.
On Exit
Macro or function that runs when a control loses focus (is deselected) on the
same form.
On Got Focus
Macro or function that runs when a control gets the focus (is selected).
On Lost Focus
Macro or function that runs when a control loses the focus (is deselected).
On Click Macro or function that runs when a control is clicked.
On Dbl Click Macro or function that runs when a control is double-clicked.
On Mouse Down
Macro or function that runs when the user presses the mouse button.
On Mouse Move
Macro or function that runs when the user moves the mouse over a control.
On Mouse Up
Macro or function that runs when the user releases the mouse button.
On Key Down
Macro or function that runs when the user presses a key on the keyboard.
On Key Up
Macro or function that runs when the user releases a key on the keyboard.
On Key Press Macro or function that runs when the user presses an ANSI key on
the keyboard.
Creating Conditional Expressions
A condition takes action based on a certain condition. For example, if an employee's age weekly sales are more than $2,500, then a condition could calculate a 5-percent commission bonus for the employee; otherwise, it wouldn't calculate a bonus. If you're at all familiar with programming, a condition is similar to an If...Then statement. You enter conditions in the Condition column in the Macro window. If a condition is true, Access executes the action in that row. If a condition isn't true, Access skips the action in that row and moves to the next row. Conditions often compare values in a specific control on a form or report to a number, date, or constant. For example, evaluate if the value in a City field is not equal to "Minneapolis." Make sure that you use the proper Microsoft Access syntax when referring to controls in forms or reports.
Make sure you have the frmEmployees form from the previous lesson open. Click the View button on the toolbar to switch to Design view.
We want to add a conditional expression to the mcrEmployees macro. If a macro is assigned to a control on a form or report, you can open and edit the macro directly from the form or report without having to access it through the Database window.
1. Select the command button, click the Properties button on the toolbar, click
the Event tab, and click the On Click box.
A Build button appears in every event property. Click this button to create
or modify the macro or Visual Basic procedure assigned to the event.
2.Click the Build button.
The mcrSales macro appears in Design view.
3. Click the Conditions button on the toolbar.
The Condition column appears. This is where you need to add the conditions you
want Access to evaluate before it executes an action. It's often easier if you
use the Expression Builder to help you create your macro conditions.
4. Click the first blank cell in the Condition column and click the Build button on the toolbar.
5. Double-click the Forms folder in the bottom-left window, double-click the
All Forms folder, then click thefrmEmployees folder.
When you select the frmEmployees folder in the left window, the middle window
displays all the controls in the selected form.
6. Scroll down the middle window, and find and double-click the City control.
Access adds Forms![frmEmployees]![City] to the expression area. Now you need
to specify how you want to evaluate the City field.
7. Click in the Expression box and add "Minneapolis".
8. Click OK.
The Expression Builder dialog box closes. The condition you entered will execute
the OpenForm action only if the City field is not equal to "Minneapolis."
The condition you entered only affects the first row or action in the macrothe
other actions in the macro will execute without being evaluated. If you want
to evaluate the other actions, they must each have their own statement in the
Condition column. Let's add some more actions to the macro.
9. Copy the first row in the Condition column and paste it in the second and
third rows.
We're finished modifying the macro.
10. Save your changes and close the Macro Design window. Click the View button
on the toolbar to display the form in Form view.
Let's test our conditional macro.
11. Find a record whose City field is NOT "Minneapolis" and click
the Sales Report button. Click OK and then close the commission report. Click
the Save button and close the frmEmployees form.
