Public Sub Reviewer_Notifications(strReview_Type As String)
'*******************************************************************************************
Dim intUser_ID As Integer
Dim strUser_Status As String
Dim strUser_EMail_Address As String
Dim strMessage_Title As String
Dim strMessage_Text As String
Dim strWork_String As String
Dim strSQL_Statement As String
Dim rstUser_Property As Recordset
Dim rstUser_Account As Recordset
Set gdbCurrent_Database = CurrentDb '<<<<<TEST
'*******************************************************************************************
' Build the message title and text
strMessage_Title = ""
strMessage_Text = ""
' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -*
Select Case strReview_Type
Case "APPS"
strMessage_Title = "Material Review: Applications"
strMessage_Text = "We have completed the scanning of a batch of applications. " _
& Format(Now(), "dddd, h:nn AMPM")
Case "DOCS"
strMessage_Title = "Material Review: Documents"
strMessage_Text = "We have completed the scanning of a batch of documents. " _
& Format(Now(), "dddd, h:nn AMPM")
Case "TRANS"
strMessage_Title = "Material Review: Transcripts"
strMessage_Text = "We have completed the scanning of a batch of transcripts. " _
& Format(Now(), "dddd, h:nn AMPM")
End Select
'------------------------------------------------------------------------------------------*
strSQL_Statement = "SELECT * FROM tblCSUP_User_Property_Control " _
& "WHERE ( CSUP_property_category = " _
& Chr(34) & "MATRV-PEND" & Chr(34) & ") " _
& "AND ( CSUP_property_item = " _
& Chr(34) & Trim(strReview_Type) & Chr(34) & ");"
Set rstUser_Property = gdbCurrent_Database.OpenRecordset(strSQL_Statement, _
dbOpenSnapshot, _
dbReadOnly, _
dbReadOnly)
Do While Not rstUser_Property.EOF
' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -*
intUser_ID = Nz(rstUser_Property!CSUP_user_id, 0)
' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -*
' Access the User Record
strUser_EMail_Address = ""
strSQL_Statement = "SELECT * FROM tblAUA_User_Administration " _
& "WHERE ( AUA_user_id = " & Str(intUser_ID) & ") " _
& "AND ( AUA_user_active_flag = " & Chr(34) & "A" & Chr(34) & ") " _
& "ORDER BY AUA_user_id ASC;"
Set rstUser_Account = gdbCurrent_Database.OpenRecordset(strSQL_Statement, _
dbOpenSnapshot, _
dbReadOnly, _
dbReadOnly)
If Not rstUser_Account Then
strUser_EMail_Address = Trim(Nz(rstUser_Account!AUA_email_address, ""))
End If
rstUser_Account.Close
' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -*
' If there is NO email Address, bypass the User Account
If Trim(strUser_EMail_Address) = "" Then
GoTo Next_User_Property_Record
End If
' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -*
' Send the message
strSQL_Statement = "INSERT INTO eforms_tblXMO_Email_OutBound (" _ <---- This is where the problem is
& "XMO_Email_Address, " _
& "XMO_Email_Subject, " _
& "XMO_Email_Content" _
& ") VALUES (" _
& Chr(34) & Trim(strUser_EMail_Address) & Chr(34) & ", " _
& Chr(34) & Trim(strMessage_Title) & Chr(34) & ", " _
& Chr(34) & Trim(strMessage_Text) & Chr(34) _
& ");"
MsgBox strMessage_Text
MsgBox strSQL_Statement
gdbCurrent_Database.Execute strSQL_Statement
'------------------------------------------------------------------------------------------*
Next_User_Property_Record:
'========================
rstUser_Property.MoveNext
Loop
'------------------------------------------------------------------------------------------*
rstUser_Property.Close
'*******************************************************************************************
End Sub
