Jim,
You have these lines
rs_2.Close
Duane
Sent: Thursday, August 3, 2017 11:35 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] VBA Code assistant | Recordset issues
Hello all,
I have always been stumped with creating and using recordsets. Sometimes I get it and then something comes along and I am at a loss.
I have copied some code that one our students created to email reports to supervisors. I thought I had it but I have been struggling since Monday to try and get it working. I am so close and I know that I am missing something so easy to see.
The code exports the report as a pdf to a folder and then the second part of the code emails the reports. I have the report outputting to the folder and even have the first report going into the outlook outbox and then it crashes. Below is the code that emails the reports. Can someone spot my frustrating error?
the code stops on rs_2.MoveNext
Thank You
Jim Wagner
'**********************************************************************************************************************
'Section 2 - Email the Reports
'**********************************************************************************************************************
Set db = CurrentDb()
Set rs_2 = db.OpenRecordset("SELECT DISTINCT AccrualsForReport.ReportsToName, AccrualsForReport.[Reports To], AccrualsForReport.ReportsToID, [R&D-CURRENTEMPLOYEES].[Asu Email Addr] FROM [R&D-CURRENTEMPLOYEES] INNER JOIN (AccrualsForReport LEFT JOIN tblFMExecDirSummary ON AccrualsForReport.Emplid = tblFMExecDirSummary.[Person Id]) ON [R&D-CURRENTEMPLOYEES].[Person Id] = AccrualsForReport.ReportsToID", dbOpenDynaset)
Do While Not rs_2.EOF
'[Reports To] = rs_2("[ReportsTo]")
'strSQL2 = "SELECT DISTINCT AccrualsForReport.ReportsToName,AccrualsForReport.[Reports To],[R&D-CURRENTEMPLOYEES].[Asu Email Addr] FROM [R&D-CURRENTEMPLOYEES] INNER JOIN (AccrualsForReport LEFT JOIN tblFMExecDirSummary ON AccrualsForReport.Emplid = tblFMExecDirSummary.[Person Id]) ON [R&D-CURRENTEMPLOYEES].[Person Id] = AccrualsForReport.ReportsToID;"""
'Set rs_2 = db.OpenRecordset(strSQL2)
If rs_2.RecordCount <> 0 Then
MyFileName = rs_2("[ReportsToName]") & ".PDF"
Dim objol As Object
Dim objmail As Object
Set objol = New Outlook.Application
Set objmail = objol.CreateItem(olMailItem)
With objmail
.BodyFormat = olFormatHTML
Dim EmailAddressstring
EmailAddressstring = rs_2("[Asu Email Addr]")
.To = EmailAddressstring
.Subject = "Accrual Report | Test"
.Body = "Dude or Dudett, do something with this test" _
& vbCrLf & "- See Attached Report"
'
.NoAging = True
.Attachments.Add mypath & "" & MyFileName
.Display
End With
SendKeys "%{s}", True
End If
rs_2.Close
rs_2.MoveNext 'CRASHES HERE
Loop
Set objmail = Nothing
Set objol = Nothing
rs_1.Close
rs_2.Close
DoCmd.SetWarnings True
MsgBox "All report(s) have been emailed"
Set rs_1 = Nothing
Set rs_2 = Nothing
Posted by: Jim Wagner <luvmymelody@yahoo.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (4) |
Tidak ada komentar:
Posting Komentar