On not in list notification

[Pietro]

Hi,
On my form i wrote the below code so that users my be obliged to send a
notification in case they select a value that does not exist in the combobox
"Escalated by".
The process till now goes very well,the only exception is that if users
click on "Deny" in the message that appears before sending the e-mail,if
users click on "Deny" the value entered will be accepted without sending the
notification,do you have suggestions to oblige users or to select a value
from the list or,in case they want t o enter an external value,an e-mail
message should be sent?
N.B: i want the e-mail message to contain the value entered by users.

Private Sub Escalated_by_NotInList(NewData As String, Response As Integer)
Dim a As Single
Response = acDataErrContinue
a = MsgBox("The ID you entered is not found in list,would you like to notify
your TLs?", 36, "ID not found")
Response = acDataErrContinue

If a = 6 Then
[Escalated by].LimitToList = False
Form.Refresh
DoCmd.SendObject , , , "'Botros Wadie Gereas';'Hesham Mohamed Abdel Fadeel
Saleh';'Marwa Omar Aly';'Sandy Bahaa El -Din Helmy Habashy'", "Ramy Adel
Mostafa", , "You need to update the list", Chr$(10) & "Hi all," & Chr$(10) &
"" & Chr$(10) & " Kindly be informed that you need to update the agents list
from the IEX tool as a new agent with the following details was not found in
the tool:" & "" & Chr$(10) & " " & Chr$(10) & "" & "Agent ID:" & [Escalated
by] & "" & Chr$(10) & " " & Chr$(10) & "" & Chr$(10) & "This request was sent
by:" & " " & [Forms]![login]![Agent] & Chr$(10) & " " & Chr$(10) & "Best
regards," & Chr$(10) & "Customer Support Tool", False
[Sent] = "sent"
End If
End Sub

 

[Tom Wickerath]

Hi Pietro,

Why don't you just let your users add the NewData to the lookup tables, with
a little additional VBA code, rather than send an e-mail requesting that
others add this value? I really don't understand the need to have only
certain people add new data, if you are going to want this type of
functionality. Yes, occasionally, people will add bad data (for example, a
misspelled name), but this is usually pretty easy to clean up.

There are ways of sending e-mail automatically, but not with
DoCmd.SendObject. You'd need to use something like CDO, but this adds A LOT
of complexity for questionable gains. I'd say just code your NotInList event
so that your users can add the missing data themselves.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________