Validating User Input With the Before Change Data Macro Event in Access 2010
Summary: Learn how to use the Before Change data macro event to validate user input in Microsoft Access 2010.
Last modified: September 12, 2012
Applies to: Access 2010 | Access Services | Office 2010
Published: January 2011
Provided by: Mike Stowe, Microsoft Corporation
This Visual How To shows how to use the Before Change data macro event to validate user input.
The Before Change data macro event occurs when a record is changed or inserted, but before the change is committed.
This Visual How To examines some of the validation rules contained in the Before Change macro attached to the Order Details table in the Northwind web database. This macro uses the functions and macro actions that are available to be used in the Before Change event.
Downloading the Northwind Web Database
Use the following procedure to download the Northwind web database.
To download the Northwind web database
Now, you should have downloaded and installed a fresh copy of the Northwind web database, and it should be loaded in Access 2010. Close the default Home form.
Opening the Before Change Macro of the Order Details Table
Use the following procedure to open the Before Change macro of the OrderDetails table.
To open the Before Change macro of the OrderDetails table
The Before Change macro is now open in a Macro Designer window.
Example 1: Using the RaiseError Action to Terminate Macro Execution
The Before Change macro contains a series of validations that use an If…Then…Else macro block to test a particular condition. If the test fails, the RaiseError macro action is used to notify the user about the problem and cancels the event.
The following code example shows the first validation rule in the Before Change macro.
If IsNull([OrderID]) Then RaiseError Error Number 2 Error Description Must first specify customer! End If
This rule ensures that the OrderID field contains an order number. If OrderID field is blank, a message box appears with the message specified in the Error Description parameter. The execution of the Before Change macro is stopped.
Example 2: Using the Updated Function and Old Object
The following code example ensures that an existing order number is not changed.
If Updated("OrderID") And Not (IsNull([Old].[OrderID])) Then RaiseError Error Number 1 Error Description Cannot modify Order # on existing order line item End If
The first condition in the If...Then...Else macro block uses the Updated function to determine whether the OrderID field was changed. The Updated function enables you to target your validation rules to particular fields.
The second condition in the If...Then...Else macro block uses the Old object to examine the previous value of the OrderID field. The Old object contains the values of the record before it was edited. You can use the syntax [Old].[File Name] to access value of the any field in the record before it was edited.
The IsNull function is used with the Old value of the OrderId field to determine whether the field contains a new or a changed order number. If the OrderId field was changed, the RaiseError Macro action warns the user and cancels the event.
Example 3: Using the SetField Macro Action
The following code example shows a validation rule that resets the status of a line item when its quantity is increased.
The first condition in the If...Then...Else macro block uses the Old object to determine whether the value in the Quantity field was increased. If the value has increased, and the StatusID field ID is larger than 10, the SetField macro action is used to set the StatusID field to 0.
The SetField macro action sets the value of a field in the current record to the specified value.
Example 4: Using Local Variables and Looking Up Records in Another Table
The following code example shows a validation rule that ensures only valid StatusID values are entered into the table.
If Updated("StatusID") Then SetLocalVar Name varStatusID Expression [StatusID] SetLocalVar Name varStatusText Expression "" Look Up A Record In OrderDetailsStatus Where Condition [StatusID]=[varStatusID] SetLocalVar Name varStatusText Expression [StatusText] If [varStatusText]="" Then RaiseError Error Number 1 Error Description Sorry, you have specified an invalid StatusID Else SetField Name StatusText Value [varStatusText] End If End If
The initial If…Then…Else macro block checks whether the StatusID field was changed. If the field has changed, the SetLocalVar macro action is used to create two variables. The first variable, varStatusID, is set to the contents of the StatusID field. The second variable, varStatusText, is set to an empty string.
A list of valid StatusID values is stored in a table name OrderDetailsStatus. The LookupRecord data block is used to find a record in the OrderDetailsStatus table that matches the StatusID in the OrderDetails table.
If a matching StatusID is found, the varStatusText variable is set to the StatusText field in the OrderDetailsStatus table.
Data macros are a new feature in Access 2010. Data macros allow you implement business rules in events that are triggered when the user edits, inserts, or deletes a record.
Once you define a data macro, it will run regardless of how the data is accessed. This provides a significant new capability for Access that enables much more application reliability than was previously available for Access tables.