MS-Access / Getting Started

---

After Update

The After Update event fires whenever After Update Access completes the operation of committing changes to an existing record in a table. As you just saw in the previous section, we have a data macro defined in the After Insert event to mark the employee's Active field to False whenever we create a termination record. What happens, though, if we accidentally select the wrong employee when we save the new termination record? We now have a situation where two employee records are inaccurate. We have one employee marked as inactive, which shouldn't be the case, and another employee still marked as active even though he or she should not be active. To fix this discrepancy manually, you would need to change the data in the existing termination record to use the correct employee, change the Active field of the employee's record to True for the employee to whom you first assigned the termination record, and also change the Active field to False for the employee that now has the termination record assigned to him or her. Instead of doing all these steps manually, we can use the After Update event to fix both employee records.

Open the tblTerminations table again in Datasheet view. Next, click the Table contextual tab under Table Tools, and then click the After Update button in the After Events group to open the Logic Designer.

The data macro logic for the After Update event is as follows:

Comment Block: If we are modifying an existing termination record, one of two
possibilities exist: 1. The Employee that this termination is for remains unchanged -
Scenario is just updating some data for the termination record.2. The Employee that
this termination is assigned to changed - Scenario here is that when the record was
first created, it might have been assigned to the wrong employee. In this case the
user is assigning this to a different employee.
Comment Block: Check to see if the Employee field was changed.
If Updated("EmployeeID") Then
 Comment Block: The Employee field changed so we'll change the existing employee's
 status back to True.
 Comment Block: For the Where condition in this LookupRecord, use the Old value from
 the EmployeeID field and find that employee's record.
 LookupRecord In qryDMTermQuery
    Where Condition = [EmployeeID]=[Old].[EmployeeID]
    Alias
	EditRecord
	Alias
	  Comment Block: Now set Active field to True for this employee since it was
	  probably initially assigned to the employee in error.
	  SetField
	    Name: [qryDMTermQuery].[Active]
     	    Value: True

	End EditRecord
 End If
 Comment Block: After modifying this termination record, make sure the employee that
 it's assigned to now is marked as an inactive employee. To do that, we look up the
 matching employee's record in the tblEmployees table and set the Active field to
 False.
 LookupRecord In qryDMTermQuery
    Where Condition = [EmployeeID]=[tblTerminations].[EmployeeID]
    Alias
	EditRecord
	Alias
	  Comment Block: Now set Active field to False.
	  SetField
	    Name: [qryDMTermQuery].[Active]
	    Value: False
	End EditRecord

The first part of the data macro includes two Comment blocks to indicate the purpose of this event. Next, we use an If condition using the Updated property to see if the EmployeeID field changed. If the EmployeeID field changed, we know the user is assigning this existing termination record to a different employee. We then go into a LookupRecord data block and use the saved query qryDMTermQuery as the source. In the Where condition argument for the LookupRecord data block, we want to look up the EmployeeID in the query that matches the EmployeeID field found in the tblTerminations table that Access just finished committing. Once Access finds the matching record, we enter it into the EditRecord block.

Whenever you want to change data in another table in After events, you must use the SetField action inside an EditRecord block. For our example, we want to change the Active field of the matching employee to False to indicate that he or she is no longer an active employee in the database. The SetField action takes two required arguments, Name and Value. In the Name argument, we use the query name that was also used for the LookupRecord block. The context inside the LookupRecord here is the employee record that Access found inside the query, so we qualify the Active field name with the saved query name. Our Where condition argument for the LookupRecord uses the Old property. The Old property returns the value of the field before Access changed its value and saved the record. Our Where condition argument is therefore the following:

[EmployeeID]=[Old].[EmployeeID]

To help understand this concept, imagine the value of the EmployeeID is currently 31, the record for Jeff Conrad in the existing termination record. If you change the EmployeeID to Mike Viescas, EmployeeID of 16, the Old value for that field is 31 and the new value after saving the record is 16. By referencing the Old value of the EmployeeID, we can determine which employee this termination record used to be assigned to. (There is no New property available when creating data macros because the new value is simply the committed value of the field, and you can refer to it by using the field name.)

After Access finds the EmployeeID that the termination record used to be assigned to, we use a SetField data action to set the Active status of that employee back to True. It's our assumption that if the user is assigning the termination record to a different employee, we'll error on the side of caution and assume this employee's status should be changed back to True.

The first part of the data macro logic is inside an If block. Based on our logic, if the user did not change the EmployeeID field, Access does not change anything in the first part of the data macro. The second part of the After Update event is outside the If block, which means this part of the data macro logic runs every time a user changes anything about a termination record. We use another LookupRecord data block to look up a different employee record in the saved query. In this case, our Where condition argument is the following:

[EmployeeID]=[tblTerminations].[EmployeeID]

This time, Access looks for the EmployeeID in the saved query that matches the nowcommitted value in the EmployeeID field in the tblTerminations table. In our previous example, this means Access looks for the EmployeeID of Mike's record, which is 16. Finally, we set the Active status of that employee's record to False because this termination record is now assigned to that employee.

To test this After Update event, close the Logic Designer window by clicking the Close button in the Close group. You should now see the tblTerminations table again in Datasheet view. Find the termination record that you created in the previous section-the one assigned to Jeff Conrad. Tab over to the EmployeeID for this record, look at the drop-down list of employee names displayed in the EmployeeID field, and change the value from Jeff Conrad to Mike Viescas. Now click or tab off the record and Access saves the record with Mike Viescas's EmployeeID number.

To see the effects of this After Update event, open the tblEmployees table in Datasheet view and scroll down to the record for the employee record for Jeff Conrad. You'll notice that the Active field for Jeff Conrad is now set to True. You'll also notice that Mike's record shows his Active status is now set to False.

With the data macro logic that we have defined in the After Update event, Access automatically maintains the Active status of the employee records. If the user assigns the termination record to a different employee, Access changes the Active status of two different employees. If the user changed information other than the EmployeeID field, Access marks that employee as inactive again just to be safe.

The Back Office Software System sample web database includes After Update events attached to two other web tables. You can explore the data macros attached to these events for additional examples.

• tblLaborPlanDetails Creates new records in the tblSchedule table if the NeedToUpdate field is set to True. This data macro uses the CreateRecord data block to create new records in tblSchedule by copying schedule data from tblLaborPlanDetails to tblSchedule.
• tblSchedule Creates new records in the tblSchedule table if the NeedToUpdate field is set to True. This data macro uses the CreateRecord data block to create additional new records in tblSchedule by copying existing schedule data from tblSchedule to the same table but putting in new schedule dates for the appended records.

Note: In After Insert and After Update events, the new record (the one that caused the event to fire) is read-only. This means you cannot have an EditRecord block by itself in these events changing data by using SetField actions on the same record. Ideally, you should be using the Before Change event if you need to be updating data on the same record based on data you are committing. This makes the data updated all at once instead of two separate updates. If you must use an After event, you need to put the EditRecord block inside a LookupRecord block and use a Where condition argument that looks up the record Access just committed in order to make the record not read-only.