Email Notification When Field Updates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there any way to send an email alert to all users when a field (any field)
has been updated?
 
In
SLY said:
Is there any way to send an email alert to all users when a field
(any field) has been updated?
Click to expand...

Define "all users". Given a list of user e-mail addresses, you can
program your Access database to send an e-mail to those addresses when
someone uses an Access form to modify the data. HOWEVER that will not
stop someone from modifying the data in some other way -- via a table or
query datasheet, or by executing an update query, for example. So it
can't be 100% bulletproof.

What is the actual problem you're trying to address?
 
Fantastic. I would like to be able to update a key group of users (6 people
maximum) when data in our main form is updated. Most users will only have
access to the form to view/modify data so 100% bulletproof isn't necessary.

Now, how do I accomplish the above?
 
In
SLY said:
Fantastic. I would like to be able to update a key group of users (6
people maximum) when data in our main form is updated. Most users
will only have access to the form to view/modify data so 100%
bulletproof isn't necessary.

Now, how do I accomplish the above?
Click to expand...

You'd use the form's AfterUpdate event, which fires any time a modified
record is saved. If the form also allows deletions, and you want to
notify the users about that action, then you'll also need to code for
the form's AfterDelConfirm event (and probably need code in the form's
Delete event if you want to capture which records were deleted). But
let's look at update notifications for now.

I'll assume for this example that you have a table named UsersToNotify
that has the e-mail addresses (in a field named Email) of the users to
be notified. Your code for the form's AfterUpdate event would look
something like this:

'----- start of example code -----
Private Sub Form_AfterUpdate()

On Error GoTo Err_Handler

Dim rsUsers As DAO.Recordset
Dim strAddresses As String

' Make a delimited list of e-mail addresses.
Set rsUsers = CurrentDb.OpenRecordset("UsersToNotify")
With rsUsers
Do Until .EOF
strAddresses = ";" & !Email
.MoveNext
Loop
End With

' If the list isn't empty, send an e-mail to those users.
If Len(strAddresses) > 0 Then

DoCmd.SendObject acSendNoObject, _
To:=strAddresses, _
Subject:="Data Modification", _
MessageText:="Data in table xxxxxxx has been modified.",
_
EditMessage:=False

End If

Exit_Point:
On Error Resume Next
rsUsers.Close
Set rsUsers = Nothing
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point

End Sub
'----- end of example code -----

It would be possible to capture information from the form about which
record was modified, and even what the old and new values are (by
comparing .OldValue and .Value for the various controls on the form),
and then put that information into the message text.

The only hitch is that I believe the user will get a security warning
telling them that "A program is trying to send e-mail on your behalf,"
and asking them if they want to allow it or not. If you need to
suppress that message, it's not trivial. See the discussion (with
respect to automating Outlook to send mail) on this page:

http://www.granite.ab.ca/access/email/outlook.htm
 
Back
Top