Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-12-2018, 03:06 AM   #1
rainbows
Newly Registered User
 
Join Date: Apr 2017
Posts: 65
Thanks: 11
Thanked 0 Times in 0 Posts
rainbows is on a distinguished road
help with VBA on "assigned to"

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]
Attached Files
File Type: zip action request.zip (906.9 KB, 13 views)


Last edited by rainbows; 06-12-2018 at 03:32 AM.
rainbows is offline   Reply With Quote
Old 06-12-2018, 07:26 AM   #2
mike60smart
Newly Registered User
 
mike60smart's Avatar
 
Join Date: Aug 2017
Location: Dunbar, Scotland
Posts: 215
Thanks: 21
Thanked 43 Times in 43 Posts
mike60smart is on a distinguished road
Re: help with VBA on "assigned to"

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.

action request.zip

Last edited by mike60smart; 06-12-2018 at 07:51 AM.
mike60smart is offline   Reply With Quote
Old 06-12-2018, 07:46 AM   #3
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,839
Thanks: 55
Thanked 2,183 Times in 2,094 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: help with VBA on "assigned to"

Test also this
Attached Files
File Type: zip action request.zip (865.8 KB, 7 views)

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 06-13-2018, 07:22 AM   #4
rainbows
Newly Registered User
 
Join Date: Apr 2017
Posts: 65
Thanks: 11
Thanked 0 Times in 0 Posts
rainbows is on a distinguished road
Re: help with VBA on "assigned to"

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
Attached Files
File Type: zip PPI Action Request Form_Backup - Copy.zip (729.6 KB, 4 views)

Last edited by rainbows; 06-13-2018 at 10:08 AM.
rainbows is offline   Reply With Quote
Old 06-13-2018, 10:36 AM   #5
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,839
Thanks: 55
Thanked 2,183 Times in 2,094 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: help with VBA on "assigned to"

Please chk again. It was late for me to notice your new attachment.
Attached Files
File Type: zip action request.zip (866.2 KB, 5 views)
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 06-13-2018, 11:02 AM   #6
rainbows
Newly Registered User
 
Join Date: Apr 2017
Posts: 65
Thanks: 11
Thanked 0 Times in 0 Posts
rainbows is on a distinguished road
Re: help with VBA on "assigned to"

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 by rainbows; 06-13-2018 at 11:46 AM.
rainbows is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Input " Like A* " in Validation Rule Property but it shows " ALike "A*" " AccessPractice Tables 2 06-24-2016 05:37 AM
If "TableA.FieldA" exists in "TableB.FieldB" then return "1" else "2" shdale Queries 8 10-29-2014 05:20 AM
Query to Convert "Name" field to "Last Name" "First Name" "Middle Name"? derekbeck Queries 5 03-26-2012 03:23 PM
Getting rid of "not assigned" in a field bowes884 Queries 1 05-27-2005 04:46 AM




All times are GMT -8. The time now is 08:45 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World