Before Change macro event
Applies to: Access 2013, Office 2013
The Before Change event occurs when a record changes, but before the change is committed.
Note
The Before Change event is available only in Data Macros.
Remarks
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.
Command Type |
Command |
|---|---|
Program Flow |
|
Program Flow |
|
Program Flow |
|
Data Block |
|
Data Action |
|
Data Action |
|
Data Action |
|
Data Action |
|
Data Action |
|
Data Action |
To create a Data Macro that captures the Before Change event, use the following steps:
Open the table for which you want to capture the Before Change event.
On the Table tab, in the Before Events group, click Before Change.
An empty data macro is displayed in the macro designer.
Example
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.
/* 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.
Open the table for which you want to capture the Before Change event.
On the Table tab, in the Before Events group, click Before Change.
Select the code in the following code example and then press CTRL+C to copy it to the Clipboard.
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]<>"3 - Resolved" And [Status]<>"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