If you are using a Microsoft Access database application for data entry you will want to ensure the quality of the data that is being entered. Validating data in Microsoft Access allows you to check data whilst it is being entered into the database, and there are various ways of performing these actions.
You don't want to be trying to ship an order to a company that does not have address details associated with it. If you don't have a contact name associated either, you can't even call to find the data. Creating a Microsoft Access macro to require data entry into certain fields will prompt the database user to complete this vital information. This will in turn deem your data to be more accurate and useful to all database users.
A macro condition is an expression that enables a macro to perform certain tasks only if a specific situation exists. A condition can check the value of a field, or compare the value in the field to another value. When you use a condition, the macro will follow one of two paths, depending on whether the condition is true or false.
For example, you may want to perform certain tasks in the database only when a specified condition it true; you might want the macro to check and see if a field in your database form contained a value, if not, the macro will execute a command to prevent you from saving the record.
Instead of attaching the macro to a specific control object on the form (text box, command button, etc.), you attach the macro to the form itself. This way, it does not matter where the user adding the record moves the focus to, the macro will always run.
The most common event properties to trigger data validation are shown in the table below. This shows when a macro will execute if attached to this event:
Event | Event Property | The Macro Will Execute |
---|---|---|
Record data has changed. | Before Update | Before the entered data has been updated |
Changed record data is saved. | After Update | After the entered data has been updated |
A new record is inserted. | Before Insert | After you type into a new record |
A record is deleted. | On Delete | In response to a deletion request, but before the record has been deleted |
When you validate data entry, the macro will likely contain the following macro actions:
Action | Use this Action to: |
---|---|
Cancel Event | Prevent the user from posting a new record unless certain conditions are met. |
Go To Control | Specify where on the form the insertion point is placed. |
MsgBox | Display a custom message box to inform the user of any problems/solutions. |
Before actually creating any macros, you should plan what you will want the macro to do, and what actions you expect it to perform. When planning, ask yourself the following:
The following example flow chart illustrates the plan for a macro. Drawing up a flow chart will help make your plan clear and easy to follow:
A flow chart plan for the macro
After we have planned the macro, we can then go ahead and build it. In this example, we will build a macro that will not allow a record to be saved unless it contains data in the CustomerName field on our database form. If we don't enter data into the field, when we attempt to make the save, we will return the insertion point back to the field.
Below, you will see the Customer Details form, containing the CustomerName field. This is the field that we are going to validate with our macro.
The Customer Details form, with the CustomerName field that we are
going to validate with our macro
We need to go into the properties of the form, and view the Event tab of the property sheet, as shown below:
The Form properties, showing the BeforeUpdate event of the form
With the Before Update event of the form selected, we click on the Build button (...) and choose the Macro Builder:
The Builder window, where we will choose the Macro Builder option
We enter a name for the macro in the Save As dialog box, in this case mcrRequiredData, and click OK. We then begin to build the macro by entering the conditions and actions required.
To create the macro, we perform the following steps:
To save the record, you must enter the required data.
Images added to article courtesy of databasedev.co.uk