MS-Access / Getting Started

---

Before Delete

The Before Delete event fires whenever Access attempts to delete a record. There are many entry points for deleting a record when you are working with Access. For example, you can delete a record in a table or query datasheet, you can run a delete action query, you can delete a record when using a form, or you can delete records using user interface macros or Visual Basic code. When you attach a data macro to the Before Delete event, Access runs the data macro logic no matter where the entry point is for deleting a record.

In the previous sections, you created a data macro attached to the Before Change event of the tblWeekDays system table for the Back Office Software System sample web database data copy (BOSSDataCopy.accdb). The data macro you created prevents any additions or changes to the existing data. You can also lock this table down further by preventing any records from being deleted using a data macro attached to the Before Delete event.

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

We should first add a Comment block to this data macro so anyone looking at it can understand the purpose of the logic in this Before Delete event. You should now be familiar with the different methods of adding a new Comment block to the macro design surface. Drop a new Comment block onto the macro design surface and enter the following text:

We do not want to allow the user to delete any records from this system table. If the user attempts to delete any records from this table, raise an error and inform them of the mistake.

Now add a RaiseError data action below the Comment block. In the Error Number argument, enter 104. For the Error Description argument, enter the following text:

You cannot delete any records from this system table; they are used in other areas of the application.

Seems almost too simple doesn't it? Simple, yes, but completely effective. We don't need to test for any special conditions for our scenario; we just need to throw an error if this event ever occurs. To try this, save the changes to this data macro by clicking the Save button in the Close group or the Save button on the Quick Access Toolbar. Next, close the Logic Designer window by clicking the Close button in the Close group. Finally, click the record selector next to any of the records in the tblWeekDays table in Datasheet view and press Delete. Access displays the custom message in the RaiseError data action.

Note In the After Delete event example we just discussed, you might be asking why this is even necessary if a Restrict Delete relationship is enforced on any related tables. You are correct that Access prevents deletes in this case; however, it is possible that for a specific record in tblWeekDays, no related records exist in the other tables. In that case, a user could still delete a record from a static table that you don't want modified. Also, you might have other tables in your database that do not have relationships with other tables and want to prevent any records from being deleted. The tblCompanyInformation and tblSettings tables in the Back Office Software System sample web database are two such examples where no relationships exist with other tables, but we want to prevent any record deletions.

The Back Office Software System sample web database includes Before Delete events attached to other web tables that use this same technique to prevent records from being deleted. You can explore the data macros attached to these events for additional examples.

• tblCompanyInformation Prevents deletion of existing records.
• tblSettings Prevents deletion of existing records.
• tblTimeLookups Prevents deletion of existing records.
• tblWeekDays Prevents deletion of existing records.