MS-Access / Getting Started

---

Working with After Events

Access 2010 supports three After events where you can attach data macros. The AfterInsert, AfterUpdate, and AfterDelete events are designed for more extensive operations. In After events, you can use data blocks that take longer to run than the simpler LookupRecord data block available to Before events. For example, in After events, you can iterate over a collection of records, modify other records in other tables, and call named data macros.

After Insert

The After Insert event fires whenever Access completes the operation of committing a new record to a table. In the Back Office Software System sample web database, employee termination records are stored in the tblTerminations table. Whenever a new termination record is created for an employee, we want to mark the employee as inactive automatically. To accomplish this, we can use the After Insert event to look up the employee's record in the tblEmployees table and set the Boolean Active field to False for that specific employee.

If you've closed it, open the Back Office Software System backup copy sample web database (BOSSDataCopy.accdb), and then open the tblTerminations table in Datasheet view. Next, click the Table contextual tab under Table Tools, and then click the After Insert button in the After Events group to open the Logic Designer.

In the Action Catalog on the right side of the Logic Designer, you can see three options under Program Flow, four options under Data Block, and 13 options under Data Actions. The three program flow options-Comment, Group, and If-are available in all data macro events. Table-3 summarizes the Data Blocks and Data Actions you can use in the After Events group.

Table-3 Data Blocks and Data Actions Available to After Events

Element	Block or Action	Description
Data Blocks	CreateRecord	Creates a new record in a table.
	EditRecord	Allows Access to edit a record. This data block must be used in conjunction with a ForEachRecord or LookupRecord data block.
	ForEachRecord	Iterate over a recordset from a table, query, or SQL statement.
	LookupRecord	Instructs Access to look up a record in the same table, a different table, or a query.
	CancelRecord-Change	Cancels any current record changes currently in progress. You can use this action to break out of CreateRecord or EditRecord changes.
	ClearMacroError	Clears any information stored in the MacroError object, including the error number, error description, macro name, action name, condition, and any arguments. Access resets the error number to 0 after you run this action.
	DeleteRecord	Deletes the current record from the table. Access determines the current record based on the scope of where the action is called. If, for example, you are inside a LookupRecord data block, Access deletes the record found in the Where condition argument.
	ExitForEachRecord	Exits the innermost ForEachRecord loop. You can use this action when you want to break out of a long-running loop if a condition is met.
	LogEvent	Logs an event to the USysApplicationLog table. You can use this action for the purpose of debugging data macros.
	OnError	Specifies how Access should handle an error when running your data macro. In After events, you can have the data macro halt execution if an error occurs, or you can skip to the next action.
	RaiseError	Displays a custom message to the user interface level and cancels the event changes. You can use this action to manually throw an error and cancel an insert, update, or delete.
	RunDataMacro	Runs a saved named data macro attached to any table. You can optionally pass parameters to the named data macro and return values.
	SendEmail	Sends an email message. If you are using an unpublished database, Access sends the email through your local email program. If you are using a published database, the SharePoint server sends the message.
	SetField	Changes the value of a field. You can use the SetField action, for example, to change values in other table using a ForEachRecord or LookupRecord data block.
	SetLocalVar	Creates a temporary local variable and lets you set it to a value that you can reference throughout the data macro execution. The value of the variable stays in memory as long as the data macro runs or until you change the value of the local variable by assigning it a new value. Once the data macro completes, Access clears the local variable.
	StopAllMacros	Stops all macros currently executing.
	StopMacro	Stops the current data macro.

You'll notice there is also an additional node listed at the bottom of the Action Catalog called In This Database. When you expand this node, Access lists all the named data macros attached to the tables in the database. We'll discuss named data macros later in this tutorial.

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

Comment Block: After creating a new termination record for an employee, we need to
mark this employee 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.
Comment Block: For this LookupRecord, we are using a saved query instead of directly
using the table. We only need to be using the ID and Active field so we just use
those two fields.
LookupRecord In qryDMTermQuery
 Where Condition = [EmployeeID]=[tblTerminations].[EmployeeID]
 Alias
 EditRecord
    Alias
    Comment Block: 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 a LookupRecord data block to look up a record in a saved query called qryDMTermQuery. This query returns only two fields from the tblEmployees table- EmployeeID and Active. The tblEmployees table has many fields in it, but we really don't need most of those fields for the purposes of this data macro, so we created just a simple query to return the fields we need. 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 into the EditRecord block. Whenever you want to change data in another table, 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 they are no longer an active employee in the database. The SetField action takes two 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 Access found inside the query so we qualify the Active field name with the saved query name.

To test this After Insert 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. Begin a new record in this table and select Jeff Conrad from the drop-down list of employee names displayed in the EmployeeID field. The LastDayWorked, DateTerminated, and ReasonInDetail fields are all required fields in this table, so enter dates for the two Date/Time fields and then enter some text into the ReasonInDetail field. Now, click or tab off the record and Access saves the record.

To see the effects of the After Insert event that we defined, you need to open the tblEmployees table. Open the tblEmployees table in Datasheet view and scroll down to the employee record for Jeff Conrad. You'll notice that the Active field for Jeff Conrad is now set to False.

In previous versions of Access, you would need to write Visual Basic code to accomplish the same task as the data macro that we defined for the After Insert event using Access 2010. By using a data macro attached to an After Insert event, we are guaranteed that the logic works no matter where the entry point is for creating new records in the tblTerminations table.