hi , the form above is what I use for my action requests, this form shows all active records . at this time is I run the open issues report it sends a email to the individuals in the assigned to field . can you please help me with the code below to sent all issues to all the people in the assigned field at one time
thanks steve
Code:
Private Sub cboReports_AfterUpdate()
Dim strCriteria As String
Dim thisCriteria As String
Dim eMail As String
Dim Title As String
Dim Message As String
Dim i As Integer
DoCmd.OpenForm FormName:="ReportDialog", WindowMode:=acDialog
'* check if [TextReportOption] <> 0
If Me.TextReportOption <> 0 Then
strCriteria = "(1=1)" '(1=1) means it is always True, therefore returns all records
Select Case Me.TextReportOption
Case Is = 1 'Print Preview
'* Open the report with criteria
DoCmd.OpenReport REPORTNAME:=cboReports, VIEW:=acViewReport, WhereCondition:=strCriteria
Case Is = 2 'Print
'* Open the report with criteria
DoCmd.OpenReport REPORTNAME:=cboReports, VIEW:=acViewNormal, WhereCondition:=strCriteria
Case Is = 3 'Email report
If cboReports = "Open Issues" Then
strCriteria = "[STATUS]=""ACTIVE"""
If DCount("1", "Issues", "Status=""Active""") <> 0 Then
With CurrentDb.OpenRecordset("SELECT DISTINCT [Assigned To] FROM [Issues] " & _
"WHERE [Status]=""Active""")
If Not (.BOF And .EOF) Then .MoveFirst
i = 0
While Not .EOF
i = i + 1
thisCriteria = strCriteria & " And [Assigned To]=" & Nz(.Fields(0).Value, 0)
' eMail = DLookup("[E-mail Address]", "Contacts", "[ID]=" & Nz([Text306], 0))
eMail = DLookup("[E-mail Address]", "Contacts", "ID=" & Nz(.Fields(0).Value, 0))
'* close report if already open
If SysCmd(acSysCmdGetObjectState, acReport, cboReports.Value) <> 0 Then
DoCmd.Close acReport, cboReports.Value
End If
DoCmd.OpenReport REPORTNAME:=cboReports, VIEW:=acViewPreview, WhereCondition:=thisCriteria
'* this is a test
'* DoCmd.OutputTo acOutputReport, cboReports.Value, acFormatPDF, Environ("userprofile") & "\documents\rpt" & i & ".pdf", True
DoCmd.SendObject acSendReport, cboReports.Value, acFormatPDF, eMail, , , "Open Issues", _
"Your prompt action is required for the following issues!"
DoCmd.Close acReport, cboReports.Value
.MoveNext
Wend
End With
End If