Before Change Macro Event

Office 2013 and later

Last modified: July 28, 2015

Applies to: Access 2013 | Office 2013

In this article
Remarks
Example
About the Contributors

The Before Change event occurs when a record changes, but before the change is committed.

Note Note

The Before Change event is available only in Data Macros.

Use the Before Change event to perform any actions that you want to occur before a record is changed. The Before Change is commonly used to perform validation and to raise custom error messages.

You can use the Updated("Field Name") function to determine whether a field has changed. The following code example shows how to use an If statement to determine whether the PaidInFull field has been changed.

If  Updated("PaidInFull")   Then 
 
    /* Perform actions based on changes to the field.   */ 
 
End If 

Use the IsInsert property to determine whether the Before Change event was triggered by a new record being created or a change to an existing record. They IsInsert property contains True if the event was triggered by a new record, False if the event was triggered by a change to en existing record.

The following code example shows the syntax for using the IsInsert property.

If   [IsInsert] = True   Then 
 
   /*  Actions for validating a new record go here.       */ 
 
Else 
 
   /* Actions for processing a changed record go here.    */ 
 
End If

You can use access a the previous value in a field by using the following syntax.

[Old].[Field Name]

For example, to access the previous value of the QuantityInStock field, use the following syntax.

[Old].[QuantityInStock]

The previous values are deleted permanently when the Before Change event ends.

You can cancel the Before Change event by using the RaiseError action. When an error is raised the changes contained in the Before Change event are discarded.

The following table lists macro commands that can be used in theBefore Change event.

To create a Data Macro that captures the Before Change event, use the following steps:

  1. Open the table for which you want to capture the Before Change event.

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

An empty data macro is displayed in the macro designer.

The following code example uses the Before Change event to validate the Status fields. An error is raised if an inappropriate value is contained in the Resolution field.

Click here to view a copy of the macro that you can paste into Macro Designer.

 
/* Check to ensure that if the bug is resloved that the user has selected a resolution      */ 
If   [Status]="3 - Resolved" And IsNull([Resolution])   Then 
 
     RaiseError 
             Error Number   1 
        Error Description   You must select a resolution. 
End If 
 
/* Check to ensure that if a bug is closed that the user has selected a resolution first     */ 
If   [Status]="4 - Closed" And IsNull([Resolution])   Then 
 
      RaiseError 
             Error Number   2 
        Error Description   An issue must be resolved before it can be closed. 
End If 
 
If   [Status]<>"3 - Resolved" And [Status]<>"4 - Closed"   Then 
 
      SetField 
             Name   Resolution 
            Value   =Null 
End If 
 

To view this example in the macro designer, use the following steps.

  1. Open the table for which you want to capture the Before Change event.

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

  3. Select the code in the following code example and then press CTRL+C to copy it to the Clipboard.

  4. Activate the macro designer window and then press CTRL+V.

<DataMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/04/application"> 
  <DataMacro Event="BeforeChange"> 
    <Statements> 
      <Comment>Check to ensure that if the bug is resloved that the user has selected a resolution </Comment> 
      <ConditionalBlock> 
        <If> 
          <Condition>[Status]="3 - Resolved" And IsNull([Resolution])</Condition> 
          <Statements> 
            <Action Name="RaiseError"> 
              <Argument Name="Number">1</Argument> 
              <Argument Name="Description">You must select a resolution.</Argument> 
            </Action> 
          </Statements> 
        </If> 
      </ConditionalBlock> 
      <Comment>Check to ensure that if a bug is closed that the user has selected a resolution first </Comment> 
      <ConditionalBlock> 
        <If> 
          <Condition>[Status]="4 - Closed" And IsNull([Resolution])</Condition> 
          <Statements> 
            <Action Name="RaiseError"> 
              <Argument Name="Number">2</Argument> 
              <Argument Name="Description">An issue must be resolved before it can be closed.</Argument> 
            </Action> 
          </Statements> 
        </If> 
      </ConditionalBlock> 
      <ConditionalBlock> 
        <If> 
          <Condition>[Status]&lt;&gt;"3 - Resolved" And [Status]&lt;&gt;"4 - Closed"</Condition> 
          <Statements> 
            <Action Name="SetField"> 
              <Argument Name="Field">Resolution</Argument> 
              <Argument Name="Value">Null</Argument> 
            </Action> 
          </Statements> 
        </If> 
      </ConditionalBlock> 
    </Statements> 
  </DataMacro> 
</DataMacros>

The following example shows how to use the RaiseError action to cancel the Before Change data macro event. When the AssignedTo field is updated, a LookupRecord data block is used to determine whether the assigned technician is currently assigned to an open service request. If this is true, then the Before Change event is cancelled and the record is not updated.

Sample code provided by: The Microsoft Access 2010 Programmer’s Reference

/* Get the name of the technician  */
Look Up A Record In tblTechnicians
    Where Condition =[tblTechnicians].[ID]=[tblServiceRequests].[AssignedTo]
SetLocalVar
    Name TechName
    Expression [tblTechnicians].[FirstName] & " " & [tblTechnicians].[LastName]
/* End LookUpRecord  */

If Updated("AssignedTo") Then
    Look Up A Record In tblServiceRequests
        Where Condition SR.[AssignedTo]=tblServiceRequests[AssignedTo] And 
            SR.[ID]<>tblServiceRequests.[ID] And IsNull(SR.[ActualCompletionDate])
        Alias SR
        RaiseError
            Error Number 1234
            Error Description ="Cannot assign a request to the specified technician: " & [TechName]

End If

Wrox Press is driven by the Programmer to Programmer philosophy. Wrox books are written by programmers for programmers, and the Wrox brand means authoritative solutions to real-world programming problems.

Show: