How to 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 drop-down 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.

My name is Ben Beitler (based in London, UK) and I have been building, consulting & training with Microsoft Access databases for over 20 years.