Want To Get Started With Macros In Microsoft Access?

Once you’ve created Access databases containing tables, forms, queries and reports, you may be interested in creating and using Access macros. A macro is a set of one or more instructions which can carry out a great variety of customisable tasks. This article explains how to get started.

Once you've created Access databases containing tables, forms, queries and reports, you may be interested in creating and using Access macros. A macro is a set of one or more instructions which can carry out a great variety of customisable tasks. When you create a macro in Access you're not actually writing the software coding - Access does that for you behind the scenes. What you are doing is building a series of instructions from a pop down selector (instructions are in plain English) within the Create New Macro panel and then saving the macro with its own name. So macro creation in Access is not difficult.

You can use macros in lots of ways - here are some typical examples. A macro can be used to navigate between Access items, for example you can assign a macro to a button on a form, so that when the button is clicked the macro opens a particular form, table, query or report. Or a macro can be used for data validation, for example you could use a macro to check that the value in a box on a form is within a certain range. Or a macro can be used to process data, for example you could use a macro in conjunction with a query to load a combo box with values from another query. And you can even use a macro to run another macro.

We'll look at an introductory example in this article to build a macro to open a particular form and then we'll assign the macro to a button on another form. First you'll need to have created an Access database. The content doesn't particularly matter but best to have at least one table, query, form and report already built. Then create another form - this time a blank one, and call it FormHome. We'll use this form as the base form to navigate from. We'll need a button to run our macro, so in form design view add a command button. If the button wizard starts just cancel it. We'll do the work ourselves instead. Then save and close the FormHome.

Now we'll create a macro to open the other form in the database. So select the options to create a new macro and the Macro builder opens. Remember Access macros consist of a series of successive actions chosen from a pop down menu.

In the macro design window, under the Action heading click in the first cell, and then click on the pull down menu - you'll see more than 50 available actions (starting with AddMenu) - choose OpenForm. Then under "Action Arguments" in the lower part of the panel, right of "Form Name" click into the white box and then click the pop down menu to show all the current form names in the database. Select the desired form. Then click on the Save button at the top left, give the macro a name such as McrOpenFormHome.

It's good practice not to use spaces in a macro name - hence the capitals at the start of each word, so you can read it more easily. Alternatively you can use an underscore between the words. That's it, you've created and saved the macro. You can of course add more than one action to your macro, but for this example we'll stick to just one.

Next job is to assign the macro to your button, so when you click the button the macro will run. To do this open FormHome, switch to design view and click on the button to select it. Then click the Properties toolbar button to display the properties sheet for the button. In the Properties sheet choose the Event tab and click in the white box to the immediate right of "On Click". In this white box click the pop down and choose your macro you just created.

Close the property sheet, save the revised form and switch back to form view. Then click the button and your form should open.

You can add more buttons on FormHome to open other Access items. The process is much the same as before but when you create the macro you can choose the action OpenReport or OpenReport or OpenQuery. In this way you can build up a database navigation system based on a home form with lots of clickable buttons to take you to different parts of the database.

Hopefully this article gives you an idea of how to go about creating your own Access macros. If you remember that the macros you create can be created using the pull down action selection when you create the macro you'll soon discover the power of Access macros. If you really want to find out to use Access macros effectively consider attending one of the many instructor lead courses available. That's possibly the best way to learn to fully use Access. Good luck in learning more about Access!