Re: [MS_AccessPros] VBA Code assistant | Recordset issues

 

Hello all,

I forgot to let you all know the error that I see

Run-time error '3420';

Object invalid or no longer set

Jim Wagner

---

On ‎Thursday‎, ‎August‎ ‎03‎, ‎2017‎ ‎09‎:‎35‎:‎31‎ ‎AM, luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

 

    

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 (2)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• New Members 2

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___