In this article, you will learn all about macros — what they are and how they automate tasks for users to save time. It examines the essentials of making and using macros.
A macro is a tool that lets you automate tasks and integrate functionality into your forms, reports, and controls. For instance, if you incorporate a command button to a form, you link the button’s OnClick event to a macro. The macro will have the commands that you want the button to perform every time it is ticked.
In Access, it is useful to consider macros as a simplified programming language that you compose by building a list of actions to perform. When you create a macro, you choose every action from a drop-down list then fill in the needed information for every action. Macros allow you to add functionality to forms, reports, and controls without writing code in a Visual Basic for Applications (VBA) module. Macros give a subset of the commands that are accessible in VBA, and most people find it simpler to construct a macro than to write VBA code.
For instance, assume that you need to begin a report right from one of your data entry forms. You can add a button to your form then make a macro that starts the report. You can either have a standalone macro, which is then bound to the OnClick event of the button, or embed the macro directly into the OnClick event of the button itself. In any case, when you tap the button, the macro runs and starts the report.
You create a macro by using the Macro Builder as shown in the illustration below.
On the Create tab, in the Macro & Code group, click Macro.
The term macro is frequently used to denote independent macro objects. A macro object can contain several macros— referred to as a macro group. A macro group is shown in the Navigation Pane as a single macro object though it contains multiple macros. Unquestionably, it is possible to make each macro in a separate macro object, but it bodes well to gather some related macros into a single macro object. The name in the Macro Name column distinguishes every macro.
A macro comprises individual macro actions. Most actions involve one or more arguments. Moreover, you can assign names to every macro in a macro group. You can also add conditions to control how each action is run. The succeeding sections will explain every feature in detail.
If your macro object contains just a single macro, macro names are needless. You can simply refer to the macro by the name of the macro object. But in the case of a macro group, you need to give a unique name to every macro.
An argument is a value that gives information to the action, for example, what string to show in a message box, which control to operate on, and so on. Some arguments are compulsory while others are discretionary.
When you choose the data macro that you wish to run in the macro designer, Access database identifies if the data requires parameters. If it does, it displays text boxes where you can type in the arguments.
A condition determines specific criteria that must be met before an action will be run. You can use any expression that evaluates to True/False or Yes/No. The action will not be performed if the expression evaluates to False, No, or 0 (zero). If the expression evaluates to some other value, the action will be executed.
One condition can control two or more actions. You can achieve this by writing an ellipsis in the Condition column for each succeeding action that you need the condition to apply to. If the expression evaluates to False, No, or 0 (zero), none of the actions are executed. If the condition evaluates to some other value, all actions are run.
In Access database 2010, the Macro Designer layout looks a lot like a text editor. The three columns are replaced with actions and conditional statements presented in a familiar top-down format that is used by programmers.
|Use this expression to carry out the action||If:|
|[City]=”Florida”||Florida is the City value in the field on the form from which the macro was run.|
|DCount(“[OrderID]”, “Orders”)>20||There are more than 20 entries in the OrderID field of the Orders table.|
|DCount(“*”, “Order Details”, “[OrderID]=Forms![Orders]![OrderID]”)>2||There are more than 2 entries in the Order Details table for which the OrderID field of the table matches the OrderID field on the Orders form.|
|[ShippedDate] Between #1-Oct-2017# And #1-Nov-2017#||The value of the ShippedDate field on the form from which the macro is run is no earlier than 1-Oct-2017 and no later than 1-Nov-2017.|
|Forms![Products]![UnitsInStock]<4||The value of the UnitsInStock field on the Products form is less than 4.|
|IsNull([FirstName])||The FirstName value on the form from which the macro is run is null (has no value). This expression is equivalent to [FirstName] Is Null.|
|[Country/state]=”US” And Forms![SalesTotals]![TotalOrds]>50||The value in the Country/state field on the form from which the macro is run is US, and the value of the TotalOrds field on the SalesTotals form is greater than 50.|
|[Country/state] In (“Florida”, “California”, “Texas”) And Len([PostalCode])<>5||The value in the Country/state field on the form from which the macro is run is Florida, California, or Texas, and the postal code is not 5 characters long.|
|MsgBox(“Confirm changes?”,1)=1||You click OK in a dialog box in which the MsgBox function displays “Confirm changes?”. If you click Cancel in the dialog box, Access database ignores the action.|
|[TempVars]![MsgBoxResult]=2||The temporary variable that is used to store the result of a message box is compared to 2 (vbCancel=2).|
To make Access database temporarily skip an action, input False as a condition. Temporarily skipping an action can be helpful when you are troubleshooting a macro.
Actions are the essential building blocks of macros. Access database gives many actions from which to pick, enabling a wide assortment of commands. For instance, some of the more frequently used actions can open a report, search a record, show a message box, or apply a filter to a form or report.
To view Part-2 of this series, (by Ben Beitler ), Click here >>
I am reading this book “Understanding and Using Access Macros”. Just as described I tried to open a form in macro like the example shown in the textbook. its always telling me the form name is misspelled
Please refer to the page number off the book you are working on and then present also the form object details of your Access database so I can assist. You may email directly on ben@AccessDatabaseTutorial.com.