Introduction to Macros in MS Access

A macro is a collection of actions that Access executes automatically when the macro is run or when an associated event occurs.

Example

Suppose you have a student management database. You want to open the Student Details form whenever you click a button. Instead of performing the action manually each time, you can create a macro to open the form.

Common Uses of Macros

• Open forms and reports.
• Run queries.
• Display messages.
• Apply filters to records.
• Navigate between database objects.
• Automate repetitive database operations.
• Respond to form and control events.

1. Standalone Macro

A standalone macro is saved as a separate database object. You can run it manually or call it from another macro or an event where appropriate.

Example: A macro named OpenStudentForm.

2. Embedded Macro

An embedded macro is stored directly in a form or report event property instead of being saved as a separate named macro object.

Example: An embedded macro attached to the Click event of a command button.

3. Data Macro

Data macros are associated with table events or named data operations. They can automate actions when records are inserted, updated, or deleted, depending on the event and supported actions.

Follow these steps to create a standalone macro:

1. Open your database in Microsoft Access.
2. Go to the Create tab.
3. Click Macro.
4. The Macro Builder opens.
5. Select an action from the action list.
6. Set the required arguments for the action.
7. Add more actions if required.
8. Save the macro with a meaningful name.
9. Run the macro and check its result.

Example Macro Name

OpenStudentForm

The exact options shown in Macro Builder can vary depending on your Access version and database settings.

Suppose your database contains a form named frmStudents. You want a macro to open it.

Steps

1. Open Macro Builder from the Create tab.
2. Add the OpenForm action.
3. Set Form Name to frmStudents.
4. Choose the required view and other arguments if needed.
5. Save the macro as OpenStudentForm.
6. Run the macro.

Expected Result

Access opens the frmStudents form when the macro runs, provided the form exists and can be opened.

Macro Name: OpenStudentForm

Action: OpenForm
Form Name: frmStudents

You can use the MessageBox action to display information or instructions to a user.

Steps

1. Create a new macro.
2. Add the MessageBox action.
3. Enter the message text.
4. Set the title and other available options.
5. Save and run the macro.

Example Configuration

Action: MessageBox

Message: Welcome to Student Management System
Title: Welcome
Type: Information

When the macro runs, Access displays a message box containing the configured text.

You can associate a macro with the Click event of a command button on a form.

Steps

1. Open your form in Design View.
2. Add a command button or select an existing button.
3. Open the Property Sheet.
4. Select the Event tab.
5. Locate the On Click property.
6. Select the macro or embedded macro option available for your setup.
7. Save the form.
8. Switch to Form View and click the button to test it.

Example

A button named btnOpenStudents can run a macro that opens the student form.

Button: btnOpenStudents
Event: On Click
Action: OpenForm
Form Name: frmStudents

The button name is an example. Use the names of your actual form and controls.

Macro conditions let you execute actions only when specified criteria are satisfied. They are useful for controlling database workflows.

Example

Suppose a form contains a control named txtFee. You want to show a warning when the fee is zero.

Condition:
[Forms]![frmStudents]![txtFee]=0

Action:
MessageBox

Message:
Please enter the student fee.

This is an illustrative macro condition. The form and control names must match your database, and the condition must be configured using Macro Builder.

1. Create a sample student management database.
2. Create a form named frmStudents.
3. Create a standalone macro named OpenStudentForm.
4. Configure the macro to open the student form.
5. Create another macro that displays a welcome message.
6. Add a command button to a form.
7. Set the button's On Click event to run the macro.
8. Test the button in Form View.
9. Try creating a macro with a condition.
10. Write down the difference between macros and VBA.

1. What is a macro in Microsoft Access?
2. Why are macros used?
3. What is a standalone macro?
4. What is an embedded macro?
5. What is a data macro?
6. What does the OpenForm action do?
7. How can a macro be associated with a command button?
8. What is the purpose of a macro condition?
9. What is the difference between a macro and VBA?
10. How can you troubleshoot a macro that does not run?