Send alert when a record is deleted?

[bg3075]

Is there a way to have a check when any record, from any table, is deleted, and possibly send an email alert? I think I can figure out the email portion of the process from one we already have setup for another function, but have no idea how to have code check deleted records and triggered on the event.

 

[Rx_]

Do you mean the record is deleted by a user on the form (front-end) or do you mean if the record is deleted by a user who has direct access to the underlying table?
The Form object (front-end) has many events that could be used to call your e-mail notificaiton.

Table Object
In Access 2010 there is a feature. Take a table and put it into Design Mode.
On the tool bar choose Table Tools
Choose the option Create Data Macro - in the pull down - choose After Delete
From there you can for example Add New Action - one of them is Send Email

For a general video tutorial, scroll down to Data Macros at:
http://office.microsoft.com/en-us/access-help/what-s-new-in-microsoft-access-HA010342117.aspx
It doesn't answer your specific question, but my instruction along with this video should help.

 

[spikepl]

Further, if the deletion is significant enoough to warrant an email, then consider not deleting but marking the record as inactive.

 

[Rx_]

That is an excellent point. My favorite method is to include a field at the end named Actvity. Use "A" for active, "I" for inactive. Make "A" the default value for any field.
It adds extra work in reports or list box to filter on the Activity field.
It also makes it easy to 'undelete'.

I use SQL Server as the back end so I don't really have users touching it. I did not realize that Access 2010 had these triggers for a table object.

 

[bg3075]

Do you mean the record is deleted by a user on the form (front-end) or do you mean if the record is deleted by a user who has direct access to the underlying table?
The Form object (front-end) has many events that could be used to call your e-mail notificaiton.

Table Object
In Access 2010 there is a feature. Take a table and put it into Design Mode.
On the tool bar choose Table Tools
Choose the option Create Data Macro - in the pull down - choose After Delete
From there you can for example Add New Action - one of them is Send Email

For a general video tutorial, scroll down to Data Macros at:
http://office.microsoft.com/en-us/access-help/what-s-new-in-microsoft-access-HA010342117.aspx
It doesn't answer your specific question, but my instruction along with this video should help.

Nice! Thanks. I see there is also an option to log the deletes to a system table. Would be much better if it could log the username of the system user whom deletes the record.

Is it possible, at all, to either prevent record deletes (only), or create a custom log through vba on table records (opposed to form properties)?

 

[Rx_]

Oh yes.
Your question was answered, rather than carry it on with a different question that will most likely include code examples...
Let me suggest you start a new thread: Can VBA provide Custom Logging with user name and time stamp?
There will probably be a lot of great contributors to that. Mine works, but I never mind evaluting other examples. So everybody wins.

In some cases I just log events and change the Activity column from Active to Inactive. In other cases, a Customers table also has a Customer_Archive table with the same fields plus Delete_Date, UserName, and Delete_Time. The vba copies a record to the Customer_Archive - then deletes the orginal.

So, you can be somewhat specific in your requirements on the next question.
And, when people contribute on the next question, clicking the Thanks is always appreciated.

 

[Rx_]

http://www.access-programmers.co.uk/forums/showthread.php?t=44231

Was just searching before lunch in 5 minutes to see if there was an easy unanswered question.

Ended up with this link. In the post he was nice enough to post a zip file with some code.
Maybe this will help you too.

 

[bg3075]

Yes, I saw that as well. It seems that for each table to track, a near duplicate table must be created to track.