databasedev.co.uk - database solutions and downloads for microsoft access

Microsoft Access Macros

Using Macros to Automate Data Entry:

As you will have seen in the previous topic, we can use Macros to Require Data Entry and to inform users of any errors made in the data entry routine. We can also use Microsoft Access Macros to speed up the process of data entry. We can use a macro to remove the need for a user to enter the same data over and over for each record, we can use a macro to automate this process.

A Microsoft Access macro is a database object that allows us to automate repetative tasks without having to write complex programming routines. In Access, these tasks that the macro performs are know as macro actions.

Whilst automating the entry of data, we can also ensure that we remove the possibility of errors in our database data, which can have an impact on the accuracy of the data available for reports and queries. If we need to run reports detailing listings of all customers in a specific county or state, what if our records contain misspellings of the data?

Creating a macro to perform the task of automatically entering the name of a county based upon the town that the user enters, will not only save time for the user, but will ensure the accuracy of the data entered.

How to Automate Data Entry with a Macro

We will need to Specify a Condition for Automating the Data Entry:

  1. We will need to select the field to which we want to attach the condition.
  2. From the Properties window, we will need to select the appropriate event to execute the macro from.
  3. Start the Macro Builder.
  4. Click on the Conditions button The Conditions button in the Macro design window., or choose Conditions from the View menu.
  5. Apply the appropriate conditions to automate data entry.

Events and Actions for Automating Data Entry with Microsoft Access Macros

Microsoft Access macros can help in reducing errors in data entry and by making data entry easier and more efficient. Creating a macro that sets the value of a field based upon the value contained in another, you can reduce the amount of typing that a user needs to perform when entering data into the database. Although you can set a fields default value in the table or form, using a macro gives you more control and flexibility when the validation of data involves more that one value on a form. You can also use macros to compare values from different tables, values from different tables from other databases or unbound values not held in any table.

You can attach a macro to the form or to individual controls on the form. When a macro is attached to a control, the macro will take effect against the control.

Event Event Property The Macro Will Execute
A control is selected On Enter Upon arriving on a control, but before the control has focus
Control data is changed Before Update Before the control data is updated
A changed control is updated After Update After the changed control data is updated
A control is left On Exit Upon leaving a control, but before the focus is removed.

When you create a macro to set values in a field based upon a particular condition, you are likely to use the macro actions as follows:

Action Use this Action to:
SetValue To enter a specified value in the field. In the Action Arguments pane, you will specify the field name in which you would like to enter the value (Item) and the actual value that you want entered (Expression)
GoToControl To specify where on the form the insertion point should move to.

Creating the Macro that will Run Under Specific Conditions

As an example, imagine that most of your customers live in the county where your business is located. We will show how to create a macro that will automatically enter the County Name if we enter a specific value in the Town field, and then the insertion point will move into the next field on the form.

Below shows the data entry form that we will use. You will see that it includes the Town field, that once populated with specific data will mean that the County field will automatically update.

The data entry form that we will create the macro for to automate data entry when specific conditions are met.
The data entry form that we will create the macro for to automate data entry when specific conditions are met.

To create the macro, we perform the following steps:

  1. In design view of the form, we select the Town text box control, and view the Properites for the control.

    On the Event tab of the Properties page, we will select the On Exit event and choose the Build (...) button.

    Select the Macro Builder, and create a new macro, which we will name mcrDefaultValue.
  2. In the macro, click on the Conditions button The Conditions button in the Macro design window. to display the Conditions column. In the first row, in the Comment column, we will enter "To enter a value in the County field based upon the value entered into the Town field."
  3. In the second row of the Conditions column, type in [Town] in ("Derby", "Chesterfield", "Ilkeston")

    In the second row of the Action column, we choose SetValue

    In the Action Arguments pane, we enter the following:
    • Item: [County]
    • Expression: "Derbyshire"

    In the second row of the Comment column, enter "If Town is Derby, Chesterfield or Ilkeston, set the County value to Derbyshire"

    The image below shows the initial steps of the macro, including the first Conditions and Actions in place:
    Building the macro, showing the SetValue Action based upon a specified condition.
  4. In the third row of the Conditions column, enter an ellipsis (...) to indicate that you are attaching another action to the original condition.

    In the third row of the Action column, choose GoToControl

    In the Action Arguments pane, enter [PostalCode] in the Control Name text box. This is the name of the control that we want to move the insertion point to. We include the square brackets around the name of the specified control.

    In the third row of the Comment column, enter "...go to the PostalCode field."
  5. The macro is now complete, and can be saved. The macro has been attached to the On Exit event of the Town field. The completed macro design should be the same as the image below:
    The completed macro, including the SetValue and GoToControl Actions.

Finally, to test the macro, we need to enter a new record into our data entry form. If this record contains one of the values contained in out macro condition (Derby, Chesterfield or Ilkestone), when we exit out of the field the County should automatically update, and the insertion point will go to the specified control:

The completed form, which will automate data entry if data meets a specified condition
The completed form, which will automate data entry if data meets a specified condition

Microsoft Access macros will allow you to add interaction and automation for your users. Using macros in your database application in this way will allow you better control of the information that your users enter into the database.

The Author

Ramesh Gupta originally from New Delhi, India is now working as a freelance developer and lives in Boston, MA. He has designed and worked on numerous databases for previous companies and has experiences in many Microsoft technologies including Microsoft Access, Microsoft Excel, SQL Server and VB.NET.

Images added to article courtesy of databasedev.co.uk