Send email when table is updated

[TomH]

Hi all. I've been asked to add this feature and need some help.

In the form in question, a series of approvals are required regarding personnel adds/edit/reduction submission. The approval levels are, in order: Director, VP, HR.

What I've been asked to do is make it so that when then a supervisor or manager submits a change to personnel, the entrance of the submission will trigger an email to his director. Once his director approves with a selection in the proper form control, an email is triggered to his VP. Once the VP makes an entry, an email is triggered to HR. Then, once HR makes an entry (approve or deny), the submitter, director and VP are emailed.

Also, I've been told that we can use either the Database Mail on the SQL Server or go directly to the Mail client.

The only time I've written that an email gets sent is when using a button prompt. I'm not sure how to do this. Any help is greatly appreciated.

 

[jamesmor]

What do you have them use to approve/deny?

Is it on a form that they specifically have to open/close?

What I'd do, is check on form close whether your value has been updated or not, and generate the email on the form close.

basically the same thing as doing on a button, just in a different part of the code.

 

[DJkarl]

You mention SQL Server as your backend, you could create a trigger to send the email. Pretty decent tutorial here http://blog.netnerds.net/2008/02/create-a-basic-sql-server-2005-trigger-to-send-e-mail-alerts/

 

[TomH]

What I'd do, is check on form close whether your value has been updated or not, and generate the email on the form close.

James: Nice idea. I'll look into that.

karl: Thanks. I'll definitely go through that tutorial this weekend.