CODEWITHSUNDEEP
ms-accesstriggers
Farid
Farid

Reputation: 181

MS Access trigger?

I have two tables named [Insert_Record] and [Delete_Record] in MS Access. Both tables have the same fields but one table has records whereas another table has no record.

Question: I want, whenever I delete any record from the table [Insert_Record] that entire record should automatically insert into another table, i.e: [Delete Record].

How can I accomplish this?

Upvotes: 18

Views: 49519

Answers (4)

Xyloz Quin
Xyloz Quin

Reputation: 178

I imagine this 'Delete' is button driven...

So program the button to first Insert the record into one table before deleting it.

Just add the VBA for the insert above the delete.

Another method which nullifies your need for another table entirely is just one column that is a boolean... Active Yes/No (Default to yes.)

If Active then it is not actually deleted when they hit delete, just set the flag to False then nothing is actually deleted, no SQL code is required and there is no risk, you can even have a column with the user who updated it print in another column

   Me.Active = False
   Me.UserName = 'CurrentUser Location here
   Me.RecordSet.Requery

Then there is no risk of actually losing anything is there and you have a record of who flagged it last.

In the continuous form just add the Where flag Active = True then no false flags will show and it looks to the user base as if it was deleted.

Upvotes: 0

Gord Thompson
Gord Thompson

Reputation: 123829

Access 2010 introduced event-driven Data Macros that are similar to triggers. The process described in the question can easily be done with an After Delete data macro on the [Insert_Record] table:

AfterDelete.png

Upvotes: 19

RQDQ
RQDQ

Reputation: 15579

As I understand it, Access doesn't have triggers.

The best you can probably do is put this sort of logic into the forms that edit the table. In other words, handle the deleted event at the form level and put your insert logic there.

If you want triggers, you'll want to use a proper RDMS that supports them (MySQL, MS SQL, Oracle, many others).

EDIT: Another way to do this (which may or may not work for you) would be to add a boolean column 'IsDeleted'. That way, you can just logically delete a record instead of moving it to another table. The downside of this approach is the deleted records stay in the main table which could cause performance woes if there are lots of deletes.

Upvotes: 1

FSBarker
FSBarker

Reputation: 76

Create an append query, to add records into the second table, that is executed in the On Delete Confirm event of the form you are using to delete the record from the first table.

Upvotes: 0

Related Questions