Microsoft Access 2010 tutorial:create macro

Create a macro

II. Create a macro

To create a macro, you need to use the Macro Builder. To open the Macro Builder, do the following:

- Click Create tab

- Click Macro in the Macro & Code group

Access displays the Macro Builder as the figure:

Access the Macro Builder 

The Macro Builder displays only one Action column (a combo box) with the Action Catalog pane. The combo box contains program flow and all macro actions; and the Action Catalog pane displays the program flow, macro actions by categories, and the created macro actions in the current database.

To create a macro action, you can type the name of macro action in the combo box or click the drop-down arrow of combo box to select the macro action. Then you have to fill in the required argument of the action. Finally,  you need to save the macro.

In order to make you easier to create and use macro, you should know the following tools in the Design tab of macro. See the figure:

Access the Macro Builder's design tab

+ The Tools group

    - Run: It is a command use to run the macro actions.

    - Single Step: It is used to execute the macro only one at one time when the Single Step is selected.  When you click the Single Step and then click Run, the Macro Single Step dialog box displays. On this dialog box has three command buttons: Step, Stop All Macros, and Continue. Once you click Step command button, the macro executes from one macro to another. If you want to stop all the macro actions, click Stop All Macros command button. Click Continue command button when you want to continue to run the macro.

    - Convert Macro to Visual Basic: It is used to convert a macro to Visual Basic code.

+ The Collapse/Expand group

    - Expand Actions: When you want to expand the actions of macro, click Expand Actions in the Collapse/Expand group of the Design tab.

    - Collapse Actions: It works opposite to the Expand Actions tool.

    - Expand All: It expands the macro actions and the collapsed block in the Macro Designer.

    - Collapse All: It collapses the macro actions and expanded block in the Macro Designer.

+ The Show/Hide group

    - Action Catalog: When you want to display the Action Catalog pane, click Action Catalog.

    - Show All Actions: When It is selected, all the macro actions are displayed in the drop-down list of the Action column and some actions are not trusted by database.

II.1. Create a stand-alone macro

A stand-alone macro is a macro that displays under the Macro in the Navigation pane. To create the stand-alone macro, follow the steps:

- Click Create tab

- Click Macro in the Macro & Code group

Access displays the Macro Builder as the figure:

Access the Macro Builder stand-alone macro 

- Type the macro action or click drop-down arrow of the Action column to select to the macro action you want.

 For example, you want to create a stand-alone macro to open a form.

- Enter OpenForm in the Action column

- Complete the action arguments of the macro action as the figure:

Access open form macro 

Form Name: Type the name of the form.

View: It allows you to select form's view types: Form, Design, Print Preview, Datasheet, PivotTable, PivotChart, and Layout.

Filter Name: It can be a name of query or filter saved as query. It use to restrict and/or sort record to display on the form.

Where Condition: Type an SQL WHERE clause without the word WHERE or expression that selects records for the form from the underlying table or query. Click the Build button to use the Expression Builder to set this argument. For a Web macro the Where Condition argument cannot begin with "=" sign. For example, BookID=[Form]![FrmBook]![BookID]

Data Mode: You can select the data entry mode for your form such as Add, Edit, and Read Only.

      + Add: Once your form open, it displays the new blank records.

      + Edit: The form opens with the existing records that allows you to edit.

      + Read Only: You can only read the records.

Window Mode: It is used to select the mode for the form window. The form window mode include Normal, Hidden, Dialog, and Icon.

      + Normal: The form is in the mode that you set in the form's property. For example, you set the Default View property of the Form1 as Datasheet, so when you open the form, it will display in Datasheet view.

      + Hidden: The form is hidden.

      + Dialog: The modal and pop up properties of the form are set to yes.

      + Icon: The form is minimized.

- Click Save button to save the macro

- Click Run to execute the macro

To create other macro actions, follow the steps of creating the open form action above and fill in action arguments according to their actions. If you are not clear with any action argument, you can read its description. To its description, you just place the mouse on box in front the action argument. For instance, you want to read the description of the Save argument of the CloseWindow action. You can read its description, just placing the mouse on the Save box as the figure below:

Access argument's description 

Note: The Icon and Hidden modes are not available in Web mode.

II.2. Create a macro group

When you use the actions that carry out the similar actions, you should put them in group. So the macro group should be created. To create the macro group, follow the steps:

- Click Create tab

- Click Macro in the Macro & Code group

Access displays the Macro Builder.

- Double-click Group in the Catalog pane ( if the Catalog is not displayed, click Catalog pane in the Show/Hide group of the Design tab)

- Enter the macro group's name in the Group box

For example, you intend to create a macro group call MaOpenTables.

- Type MaOpenTables in the Group box

Access setting name of the macro group 

-Enter action in the Action column or click drop-down arrow of the Action column to select action from the list.

- Fill in the required information for the action

For adding more actions, follow the two steps above ( under the MaOpenTables figure).

- Click Save to save the macro

- Click Run to execute the macro

II.3. Create an embedded macro

An embedded macro is a macro that does not display in the Navigation Pane beneath the Macro. Embedded macro is embedded with forms, reports or controls. To create it, follow the steps below:

- Open form or report in Design view

- Click any section of form or report or click control on the form or report

- Press F4 if the Property Sheet is not displayed

- Click Event tab

- Click the box in front any event you want the macro do action

- Click Access show Choose Builder dialog box

Access displays Choose Builder dialog box as the figure:

Access Choose Builder dialog box 

- Click Macro Builder

- Click Ok

Access opens the Macro Builder.

- Enter the action you want for your form

Access embedded macro 

- Click Save to save the macro

- Click Run to execute the macro


Jan Aubrey comment

 Jan Aubrey

This is great and easy to learn.


This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.