MS-Access / Getting Started

---

Working with Before Events

As we mentioned in the previous section, you can attach data macros to the Before Change, Before Delete, After Insert, After Update, and After Delete events of tables. In the following sections, you'll learn about the Before Change and Before Delete events, create new data macros attached to events, and examine other data macros attached to these events in the Back Office Software System sample web database.

In Before events, you can look at the incoming values in the current record and compare them with a record in other tables using the LookupRecord data block. You can also use the SetField data action to alter data before Access commits the changes, but only on the incoming row of data, not on a record returned from the LookupRecord data block. In Before events, you can also prevent a record from being saved or deleted and display custom error messages to the user using the RaiseError data action. Before events are designed to be fast operations, which means the actions and data blocks that you can use in these events are more limited. For example, you cannot iterate over a collection of records in Before events or call named data macros.

Before Change

The Before Change event fires whenever you add new records to a table or when you update data in an existing record. Let's create a new data macro attached to the Before Change event of the tblWeekDays table to illustrate the process of creating, saving, and testing a new data macro. Open the tblWeekDays table in Datasheet view, click the Table contextual tab under Table Tools, and then click the Before Change button in the Before 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, one option under Data Block, and six options under Data Actions. The three program flow options-Comment, Group, and If-are available in all data macro events, but the options under Data Blocks and Data Actions change based on whether you are using a Before event or an After event. Table-1 summarizes the data blocks and data actions that you can use in the Before events-Before Change and Before Delete.

Table-1 Data Blocks and Data Actions Available to Before Events

Element	Name	Description
Data Blocks	LookupRecord	Instructs Access to look up a record in the same table, a different table, or in a query. In Before events, you can inspect the values in another table or query using the LookupRecord data block, but you cannot change any values in the other table or query.
Data Actions	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
	OnError	Specifies how Access should handle an error when running your data macro. In Before 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.
	SetField	Changes the value of a field. You can use the SetField action, for example, to change the value of another field in the same record before committing the changes. Note that you cannot use SetField in before events to change values in other tables using the 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.
	StopMacro	Stops the current data macro.

The tblWeekDays table contains seven records, each record listing the name of a day of the week. This table helps build a linking table between the tblVendors table and the tblOrderDays table. Each vendor in the database can have more than one order day and each weekday can be used by more than one vendor. Similarly, the tblWeekDays table also serves as a linking table between tblVendors and tblDeliveryDays. For the purposes of this database, we consider tblWeekDays to be a system table: a table used by other parts of the application, but one in which we don't ever need to add, change, or delete data. (We can't foresee the names of the weekdays changing any time soon.) To prevent new records from being added to this table, we'll create a data macro in the Before Change event that checks to see if Access is inserting a new record and then stop the insert if that condition is met.

Including Comments

To start creating your data macro in the Before Change event of the tblWeekDays table, let's first add a comment to the macro design surface. Comments are useful for documenting the purpose of your data macro and the various data actions within it. Access ignores any comments as it executes the actions within your data macro. Click on the word Comments under the Program Flow node in the Action Catalog, hold the mouse key down, drag the comment onto the macro design surface, and then release the mouse button.

Access creates a new Comment block on the macro designer surface. If your cursor is not in the Comment block and you do not have any comments typed into the Comment block, Access displays the text Click Here To Type A Comment. Access moved the Add New Action box below the Comment block. You'll also notice that Access places a delete button to the far right of the Comment block. The delete button has a symbol shaped like an X. If you want to remove the Comment block, click the delete button and Access removes the Comment block from the macro design surface. If you deleted the Comment block in error, click the Undo button on the Quick Access Toolbar to restore the Comment block.

Click inside the Comment block and type the following text:

We don't want to allow additional records into this system table. If a new record is being added, the IsInsert property will be True. In this case, raise an error and inform the user.

Click outside the Comment block onto the macro design surface. Access collapses the size of the Comment block to just fit the text you typed and displays the text in green. The /* and */ symbols mark the beginning and end of a block of comments. Access designates anything written between those symbols as a comment and is there only to provide information about the purpose of the data macro or particular action to follow.

You might be asking yourself if it's really worth your time including comments in your data macros. While it's true that it takes additional time to include comments as you're creating your data macros, the investment of your time now pays off in the future. If you need to modify your application at a later date, you'll find it much easier to understand the purpose of your data macros if you include comments. This is especially true if someone else needs to make changes to your application. Trust us; it's worth your time to include comments when you design data macros!