After Delete

The After Delete event fires whenever Access completes the operation of deleting a record from the database. As you just saw in the previous two sections, we have data macros defined in the After Insert and After Update events of the tblTerminations table to adjust the employee's Active field whenever we are creating or editing termination records. What happens, though, when you delete an existing termination record? We now have a situation where you need to decide the appropriate logic based on the workflow needs of the application. You could decide to do nothing, which means that the employee to whom the termination record was assigned remains an inactive employee. For our database requirements with this application, we decided that an employee should be changed back to an active employee if a user deletes his or her termination record. In this workflow, a user might have accidentally created a termination record when no record should have been created in the first place.

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

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

Comment Block: The user is deleting this Termination record. It's possible this
record was made completely in error. When the record was made, the employee's Active
field was set to False to make them inactive. We are now deleting this record so to
be safe, change the Active field back to True as an active employee.
Comment Block: For the Where condition in this LookupRecord, use the Old value from
the EmployeeID field and find that employee's record. You have to use the Old value
because the new value of the EmployeeID field is Null since Access just deleted the
record.
LookupRecord In qryDMTermQuery
 Where Condition = [EmployeeID]=[Old].[EmployeeID]
 Alias
 EditRecord
    Alias
    Comment Block: Now set Active field to True
    SetField
	Name: [qryDMTermQuery].[Active]
	Value: True
 End EditRecord

The first part of the data macro includes two Comment blocks to indicate the purpose of this event. Next, we use a LookupRecord data block to look up a record in the saved query called qryDMTermQuery. 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 that Access just finished deleting from the tblTerminations table. When you are using the After Delete event and need to refer to a field value in the record that Access just deleted, you must use the Old property to retrieve the value. The existing field value is Null because Access deleted the record. Our Where condition is as follows:

The first half of the Where condition expression is the EmployeeID in the saved query and the second half is the EmployeeID of the deleted termination record. Once Access finds the matching record, we enter into the EditRecord block. We then use the SetField action inside the EditRecord block to set the Active field back to True for the employee.

To see the effects of this After Delete event, open the tblTerminations table in Datasheet view and then delete the termination record that you created and edited earlier-the record currently assigned to Mike. Now open the tblEmployees table in Datasheet view and scroll down to Mike's employee record. You'll notice that the Active field for Mike is now set back to True.