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.
We will need to Specify a Condition for Automating the Data Entry:
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. |
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.
To create the macro, we perform the following steps:
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
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.
Images added to article courtesy of databasedev.co.uk