Validating User Input With the Before Change Data Macro Event in Access 2010

  • Article

Office Visual How To

Summary:  Learn how to use the Before Change data macro event to validate user input in Microsoft Access 2010.

Applies to: Access 2010 | Access Services | Office 2010

Published:  January 2011

Provided by:  Mike Stowe, Microsoft Corporation

Overview

This Visual How To shows how to use the Before Change data macro event to validate user input.

Code It

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

  1. Start Access 2010, click File and then click New.

  2. In the list of available templates, under Office.com Templates, select Samples.

  3. From the list of sample databases, select Northwind web database.

  4. Select an appropriate path, and then click the Download button to download and install a clean copy of the sample database.

  5. Accept the default logon ID in the Login Dialog form, and then click Login.

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

  1. In the Navigation Pane, expand the drop-down menu at the top of the pane, and select Object Type from the list of options.

  2. Double-click the OrderDetails table.

  3. On the Table tab, in the Before Events group, click Before Change.

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.

If  [Quantity]>[Old].[Quantity] And [StatusID]>10  Then

    SetField
         Name  StatusID
        Value  0

End If

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.

Note

When a LookupRecord data block is used outside the Before Change and Before Delete data macro events, you can add code that performs a set of actions on the record that is found. However, the Before Change and Before Delete data macro events can only perform actions on the current record.

Read It

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.

 See It

Watch the video

> [!VIDEO https://www.microsoft.com/en-us/videoplayer/embed/8e861e48-3739-431a-b3ee-361e212a20c1]

Length: 00:09:26

Explore It