MS Access - Macros

In this chapter, we will cover the basics of Macros in Access. A Macro is a tool that allows you to automate tasks and add functionality to your forms, reports, and controls.

• Macros in Access work a bit different from Macros in Word or Excel, where you essentially record a series of keystrokes and play them back later.

• Access Macros are built from a set of predefined actions, allowing you to automate common tasks, and add functionality to controls or objects.

• Macros can be standalone objects viewable from the Navigation pane, or embedded directly into a Form or Report. Once you have created database objects like tables, forms and reports, Macros can provide a quick and easy way to tie all those objects together to create a simple database application that anyone can use or even modify, with relatively little training.

• Macros provide a way to run commands without the need to write or even know VBA code, and there is a lot that you could achieve just with Macros.

Creating a Macro

Let us start be creating a very simple Macro that opens a form when a command button is clicked. For this, we need to open your database and frmEmployeeData form in which we have created two tabs.

In this form, we can add a button allowing users to open up all of the job information.

Let us now go to the Design View of this form and add button form the Controls menu. When you release your mouse, you will see the Command Button Wizard dialog box.

There is a couple of ways to build that Macro action, but the simplest way is to simply use the Command Button Wizard.

For common actions like opening a form, select Form Operations from the Categories list and then select Open Form from the Actions list and click Next as in the above screenshot.

You need to specify which form you would like to open with the command button. For now, let us select frmJobs and click Next.

In this screen we have two options, we can open the form and display a very specific record, or we can open the form and show all the records. Let us select the second option and click Next as in the above screenshot.

We could have the command button itself display a picture or you can select the Display Text. Here, we want the text View Jobs to display and now click Next.

You can now provide a meaningful name to your command button as in the above screenshot. This can be used in other codes or other Macros. Let us call this cmdViewJobs and click Finish.

Now go to the Form View.

You will now see a View Jobs button on your form. Let us click on it.

Now you have a form open, but you will not be viewing any information. Let us go back to the frmEmployeeData form Design view. Make sure that the command button is selected and click on the Event tab on the Property Sheet.

Upon clicking, you will see an embedded Macro created by the Wizard. If you now want to modify this Macro, click on the … button to open up the Macro generated by the Wizard.

This is the Macro Designer and on the right you will see the Action Catalog. This is where all of your actions will live in folders. You have the Data Entry options, Data Import/Export and so on, and on the left in the main area you have another Macro. It only contains one action, and clicking on that one action you can view other properties for that specific action.

You will see the form name and you can hit that drop-down arrow to view the forms available in your database. You can change how that form is viewed, you can have it open to Form view, Design view, Print Preview at your choice. You can apply a filter name or a Where condition. Here we want to change the Data Mode because frmJobs is set to the Add Mode which only allows the addition of new records. We can override this here in this Macro by changing it to the Edit Mode.

Now save your Macro, and then close the Macro Designer and go back to the Form View.

Let us click on View Jobs again.

You can now see that it opens your frmJobs form and allows you to scroll through all of the available jobs in our database.