Tutorial 10: Creating and testing a simple Access macro

This tutorial takes you through creating a simple macro using a database called ‘Get Fit’(get_fit_ot.mdb). You’ll need to download this (from the list of tutorials in Research) first. Note that there are compatibility issues with different versions of Microsoft Access. The file will only work in more recent versions of Access, from 2000 and upwards.

The Get Fit database

The Get Fit database is from a small fitness club. It records membership details of the clients and the results of fitness tests. It also records entries in the club's triathlon events.

Creating the macro

  • Open the Get_Fit database (get_fit_ot.mdb).
  • Click on Macros in the Objects list that is displayed when the database opens.
  • Click on the New menu item in this dialog.
  • Select OpenForm from the Action column.
  • In the Action Arguments section at the bottom, Click in the Form Name field then Click on the drop down arrow that appears at the right of the field to display a list of all the forms available as in the figure below.

Selecting a form from Action Arguments

  • Select frmNewClient. This macro will open the NewClient form.
  • Set the Data Mode to Add in the Action Arguments. This makesthe form open in ‘data entry’ mode (it will be a blank form).
  • Save the macro as mcrNewClient by using the Access File/Save menu option or the Save icon on the toolbar. Then close the Macro window to return you to the Database window which first appeared.
  • Double Click on mcrNewClient and ensure that the form opens.

Why use prefixes?

You’ll notice that prefixes frm and mcr have been used in this database. These are not necessary, but are a convention often used by Access developers to help the organisation of objects. They are part of the Leszynski Naming Conventions (LNC) which has become an unofficial standard.

Many of the drop down menus you’ll find in Access don’t distinguish different objets. You’ll also find the use of these prefixes important if you go on to write Access modules. The prefixes for other Access objects include tbl for table, qry for query and rpt for report.

Why set the data mode to Add?

Setting the Data Mode to Add will prevent a user accidentally over-typing existing data and destroying that record. . It is a good idea since you only want users to be adding new data when they use that particular macro. Another macro may be for viewing the existing data and you may set the Data Mode of it to Read Only to prevent accidental modifications.

In-built events

You don’t actually have to create separate macros to carry out simple automated actions like opening a form or report. You are able to easily assign such simple ‘events’ when you create buttons on forms. You’ll see this in another section.

What next?

Now that you have a tested macro you will be able to combine it with other macros and also assign it to menus and other objects for quick access. You were already able to open the form from the initial Database window. However it is important to build protective features into your databases. For example, you can hide the database window so that users can’t manipulate your forms directly, making mistakes and wreaking havoc!

1

2005