help with VBA on "assigned to" (1 Viewer)

rainbows

Registered User.
Local time
Today, 06:51
Joined
Apr 21, 2017
Messages
425
hi

This code was kindly create for me. when I tested it, it seemed to work great
now I have added more contacts in the contact table I see it is not working correctly


what this does is prepare to send a report from the "open issues" to ALL the people in the contacts file who have a report "assigned to" them.

what i really need it to do is prepare to send an e-mail to the people who have "active" reports in the "open issues" and it is assigned to them

any help will be apricated


file attached , if yousee the assigned to field you will see 2 names .when you select the report " open issues assigned to " you will see it will send a report to all 3 people in the contacts list which is not correct

thanks steve



[CODEPrivate 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:=acViewPreview, 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(.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

ElseIf cboReports = "Issues By Assigned To" Then

With CurrentDb.OpenRecordset("SELECT DISTINCT [Assigned To] FROM [Issues]")
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(.Fields(0).Value, 0))
'* close report if already open
If SysCmd(acSysCmdGetObjectState, acReport, cboReports.Value) <> 0 Then
DoCmd.Close acReport, cboReports.Value
End If
thisCriteria = strCriteria & " AND "
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, .Fields(0).Value, , , "Issues By Assigned To", _
"your message here"
DoCmd.Close acReport, cboReports.Value
.MoveNext
Wend
End With

Else

'* add Title and Message below
'*
Select Case cboReports.Value
Case Is = "Closed Issues"
'Title =
'Message =
Case Is = "Contact Address Book"
Case Is = "Contact Phone Book"
Case Is = "Issue Details"
Case Is = "Open Issues By Category"
Case Is = " Open Issues By Status"
End Select


With CurrentDb.OpenRecordset("SELECT DISTINCT [E-mail Address] FROM [Contacts];")
If Not (.BOF And .EOF) Then .MoveFirst
i = 0
While Not .EOF
i = i + 1
'* 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, .Fields(0).Value, , , Title, Message
DoCmd.Close acReport, cboReports.Value
.MoveNext
Wend
End With

End If
End Select
End If
End Sub
Code:
 

Attachments

  • action request.zip
    906.9 KB · Views: 130
Last edited:

mike60smart

Registered User.
Local time
Today, 13:51
Joined
Aug 6, 2017
Messages
1,899
Hi

See if the attached does what you need.

I looked at the query that the Report was based on and modified the criteria to only show those records which did not have a Date Completed.

View attachment action request.zip
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:51
Joined
May 7, 2009
Messages
19,175
Test also this
 

Attachments

  • action request.zip
    865.8 KB · Views: 138

rainbows

Registered User.
Local time
Today, 06:51
Joined
Apr 21, 2017
Messages
425
hi arnelgp
thanks for your help the other day .the open issues report (e.mail is working ok)

the others e-mail reports has an error appear


Code:
With CurrentDb.OpenRecordset("SELECT DISTINCT [E-mail Address] FROM [Contacts] WHERE " & strCriteria & ";")
there seems to be a couple of problems

also the closed issues report (view) don't show any items even if there are some closed
the

all other view reports are ok
hi
mike60smart

I also want to thank you for your help

I really need the reports to work on STATUS " active " and "assigned to " I will automate the closed date etc when the user selects active "closed "

also when I run the e-mail reports the only one working ok , is the open issue one as the others are also sending the reports to all the contacts not just the ones in the report/quires

thanks both for your help



later

I have added the latest version of the database , I think other than the e.mail reports it is all working now

the main e.mail report £ open issues " is working ok but the open issues assigned to is not .as it goes thro the whole contacts list . of which I don't want

steve
 

Attachments

  • PPI Action Request Form_Backup - Copy.zip
    729.6 KB · Views: 113
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:51
Joined
May 7, 2009
Messages
19,175
Please chk again. It was late for me to notice your new attachment.
 

Attachments

  • action request.zip
    866.2 KB · Views: 116

rainbows

Registered User.
Local time
Today, 06:51
Joined
Apr 21, 2017
Messages
425
hi arnelgp

thanks for help

we are getting there .
2 issues

if you change one of the "active" to "closed" you should have 2 records showing but

1. on the main screen we can see the closed forms not just the active one when we change it to closed
2 the other reports the preview shows the only the 2 active ones which is correct . but the e-mail ones still shows all 3 . active and closed , which is not correct and the e.mails don't just have their own assigned to on them it has all the active ones which is not correct . the open issues ones is correct



thanks for trying to help me

steve
 
Last edited:

Users who are viewing this thread

Top Bottom