MS-Access / Getting Started

---

Defining Multiple Actions

You can define more than one action within a data macro, and you can specify the sequence in which you want the actions performed. In the preceding section, you designed a data macro in the Before Change event of the tblWeekDays table to prevent new records from being added to the table. Currently, it is still possible for users to change the values in the WeekDayText field for the existing seven records. (Note that you cannot change the data in the WeekDayID field because it is the ID field of the web table.) We can prevent users from changing data in the existing records using data macros by adding additional logic and actions into the Before Change event. Open the tblWeekDays table in Datasheet view, click the Table contextual ribbon tab under Table Tools, and then click the Before Change button in the Before Events group. You should now see the data macro that you created previously for preventing new records from being added to this table.

Before we start adding additional logic to this data macro, let's add a new Comment block and Group block to differentiate the new functionality that we are adding to this Before Change event. Click inside the Add New Action box at the bottom of the macro design surface, type Comments, and then press Enter to create a new Comment block. Type the following text into the Comment block to identify easily the logic that we are going to add to this data macro:

We also don't want the user changing any of the existing values in this table. We can easily check to see if they are changing data by seeing if the WeekDayText field was changed using the Updated property. If it is, raise an error, stop the update, and inform the user.

To add a new Comment block onto the macro design surface quickly, you can simply type two backslashes (//) when you are in any Add New Action combo box and press Enter. Alternatively, you can type a single apostrophe (') when you are in an Add New Action combo box and press Enter. In both cases, Access creates a new Comment block on the macro design surface.

Next, tab down to the Add New Action combo box at the bottom of the macro design surface, type Group, and then press Enter to create a new Group block. Access creates a new Group block on the macro design surface below the Comment block that you just created. Now type PreventDataChanges in the text box to name this new Group block.

To determine if data in a specific field has changed during a Before Change event, you can use the Updated function. The Updated function takes one argument, a field name, and returns True if the field is dirty and returns False if the field is not dirty during the record update. For the Before Change data macro that you have been building, we can use the Updated function in a conditional expression to test whether a user is attempting to change the value of the WeekDayText field.

To start, we first need an If block inside the Group block that you just completed. In the Add New Action combo box, between the Group and End Group keywords, type If and press Enter to create a new If block. In the conditional expression text box in the If block, type the expression Updated("WeekDayText")=True. When using the Updated function, you must surround the field name with quotation marks even if the field name has no spaces. You cannot put brackets around field names using the Updated function; otherwise, you'll receive a run-time error when the data macro executes.

The last step that we need to take is to include another RaiseError data action inside the If block to stop the field update if the condition is met. Tab or click inside the Add New Action box between the If and End If keywords, type RaiseError, and press Enter to add the new RaiseError data action to the macro design surface. In the Error Number argument, enter 103. (Remember, you can use any whole number you want between the range of -2,147,483,648 to 2,147,483,647.) For the Error Description argument, we'd like to display a simple message to users informing them that they cannot edit any of the data in this table. Type the following message into the Error Description argument:

You cannot change the existing data in this system table. Please contact the system administrator.

Remember, if you do not provide an error description in your RaiseError data action, Access displays a generic message if the RaiseError condition is hit. We recommend you always include a helpful message in any RaiseError data actions you create.

Let's try out the additional logic and actions you've added to the Before Change event. Click the Save button in the Close group on the Table tab or click the Save button on the Quick Access Toolbar to save your changes. Now, click the Close button in the Close group to close the Logic Designer window and return to the Datasheet view of the tblWeekDays table. Tab into the WeekDayText field for any of the seven records, change the value to something other than one of the existing seven weekday names, and then move to a different record. Access displays the custom error that you created in the RaiseError data action.

When you click OK in the message box, Access keeps the focus on the record that you are trying to edit. You need to press Esc to undo your changes to the existing record. With the completed data macro that you created and attached to the Before Change event, you've now prevented any new records from being added to this table as well as prevented any changes to existing records. The only way to make changes to the data in this table is to remove the data macro that you defined in the Before Change event of the table. Access enforces these restrictions no matter what the entry point is for creating new records or editing existing records.