Introduction to Macros – Part 2
Creating a standalone macro A standalone macro is a macro that is displayed under the Macro in the Navigation Pane. To create a standalone macro, take the following steps: 1. Click the Create tab. 2. Click Macro in the Macros & Code group. 3. You should be able to see the Macro Designer by now. To add an action, type in the macro action or click on the drop-down menu to display the list from which you choose the action that you want to use. 4. To add more actions to the macro, move to another action row, and then repeat the previous step. Access carries out the actions in the order in which you list them. 5. Click Save when your done. Creating a macro group Create a macro group for several related macros. 6. Click the Create tab. 7. Click Macro in the Macros & Code group. 8. Once the designer is displayed, select Group from the drop-down list.
4. Input the name you want to use for the macro group in the text box.
5. Start adding the actions that you want to use. Input the macro action or use the drop-down list to select an action. 6. Fill in the required information for the action. 7. To add more actions, repeat steps 5 and 6. 8. Click Save.
Creating an embedded macro An embedded macro is a macro that is not displayed in the Navigation Pane under Macros; it is stored in the event properties of forms, reports, or controls. This can make your database easier to manage because you don't need to monitor separate macro objects that contain macros for a form or a report. To create an embedded macro, follow the steps below: 1. Open the form or report that will contain the macro in Design view or Layout view. To open a form or report, right-click it in the Navigation Pane, and then click Design View or Layout View.
2. If the property sheet is not yet displayed, press F4 to display it. 3. Click the section that contains the event property in which you want to embed the macro. To select the entire form or report, choose Form or Report from the dropdown list at the top of the property sheet.
4. In the property sheet, click the Event tab.
5. Click the event property in which you want to embed the macro, and then click next to the box.
6. In the Choose Builder dialog box, click Macro Builder, and then click OK.
7. Choose an action from the drop-down list. 8. Fill in any required information for the action. 9. Repeat steps 7 and 8 until your macro is complete. 10. Click Save to save your macro. The macro will run each time the event is triggered. Access lets you create a macro group as an embedded macro. However, only the first macro in the group runs when the event is triggered. Succeeding macros are disregarded unless they are called from within the embedded macro itself (for example, by the OnError action).
Example: Embedding a macro in the On No Data event of a report When you run a report and its data source does not have any records, the report shows an empty page. You may prefer a message box shown instead, and that the report not be displayed at all. An embedded macro is the perfect answer for this circumstance. 1. Open the report in Design view or Layout view. 2. Press F4 to display the Property Sheet. 3. Click the Event tab on the property sheet. 4. Click On No Data. 5. Click . 6. In the Choose Builder dialog box, click Macro Builder, and then click OK. 7. Add MessageBox as the first action. 8. Enter the following arguments.
9. Add CancelEvent as the second action.
10. Close the macro. You should be able to see [Embedded Macro] in the On No Data event.
11. Save and close the report. When you run the report and no records are found, the message box will be shown. If you click OK in the message box, the report will cancel without displaying the empty page. Edit a macro To insert an action Click the action below which you want to insert the new action, then select from the dropdown option an action that you want to be inserted. To delete an action Click the action you want to delete, then click . To move an action Click the action that you want to position, drag and drop it at the desired place or you may position it by clicking or .
Run a macro Standalone macros can be run in any of the following ways: directly, in a macro group, from another macro, from a VBA module, or in response to an event that occurs on a form, report, or control. Embedded macros can be run by clicking the Run Macro button which can be found on the Design tab while the macro is in Design view; otherwise, the macro will only run when its associated event is triggered. Run a macro directly To run a macro directly, do one of these steps: • Locate the macro in the Navigation Pane, then double-click the name. • On the Database Tools tab, in the Macro group, click Run Macro, then select the macro from the list, and then click OK.
Run a macro that is in a macro group To run a macro that is in a macro group, do one of the following: • On the Database Tools tab, in the Macro group, click Run Macro button, and then select the macro from the list. Access includes an entry for each macro in each macro group, in the format macrogroupname.macroname • Click OK. • Specify the macro either as an event property setting on a form or report or as the Macro Name argument of the RunMacro action. Refer to the macro by using this syntax: macrogroupname.macroname. • Run a macro that is in a macro group from within a VBA procedure by using the RunMacro method of the DoCmd object, and by using that syntax to refer to the macro. Run a macro from another macro or from a VBA procedure Add the RunMacro action to your macro or VBA procedure.
• To add the RunMacro action to a macro, select RunMacro from the action list, then set the Macro Name argument to the name of the macro that you want to run. • To add the RunMacro action to a VBA procedure, add the RunMacro method of the DoCmd object to your procedure, and then specify the name of the macro that you want to run. Run a macro in response to an event on a form, report, or control While you can now insert macros directly into event properties of forms, reports, and controls, you can also still create stand-alone macros and then bind them to events. 1. After building your stand-alone macro, open the form or report in Design view or Layout view. 2. Open the property sheet for the form or report, or for a section or control on the form or report. 3. Click the Event tab. 4. Click the event property for the event that you want to trigger the macro. 5. In the drop-down list, click the name of the standalone macro. 6. Save the form or report.
Contact us Ben Beitler ben@accessdatabasetutorial.com (+44) 7881 502400 United Kingdom London
https://www.accessdatabasetutorial.com/