Need Help with Loop - Please

[newone]

I am in need of help with a loop problem.

I have a form that contains a control button to email reports. The code is below. It worked great as long as I used the DoCmd.SendObj that is commented out. I was forced to go to new code (not mine, but came from MS website) due to a known bug in Access that caused a GPF. After inserting the new code, the code will not loop. Instead it sends the same report to the same person each time. I need it to loop through each record and send the appropriate report to the appropriate email address (FirstSPEmail).

If anyone can give me guidance as to why the loop won't work now BUT did with the commented section, I would greatly appreciate.


Dim db As Database
Dim RstTmp As Recordset
Dim Loop1 As Long
Dim SqlStr As String
Dim txtSPID As Control
Dim clsSendObject As accSendObject

Set db = CurrentDb()

SqlStr = "SELECT DISTINCTROW First(qryMergedEmail.SPName) AS [First Of SPName], First(qryMergedEmail.SPID) AS [FirstOfSPID], First(qryMergedEmail.CustomerID) AS FirstOfCustomerID, First(qryMergedEmail.AcctID) AS FirstOfAcctID, First(qryMergedEmail.SPEmail) AS FirstSPEmail, First(qryMergedEmail.Customer) AS FirstOfCustomer, FROM qryMergedEmail GROUP BY qryMergedEmail.SPName;"
Set RstTmp = db.OpenRecordset(SqlStr, dbOpenSnapshot)

DoEvents
On Error Resume Next
RstTmp.MoveLast
RstTmp.MoveFirst
On Error GoTo 0

If RstTmp.RecordCount = 0 Then
MsgBox "There are no events"
Exit Sub
End If

For Loop1 = 1 To RstTmp.RecordCount

Me!txtSPID = RstTmp![FirstOfSPID]

'Old SendObject that worked except for 3000 character GPF starts here
' DoCmd.SendObject acSendReport, "rptWorksheetRentalEmail", acFormatSNP, _
' RstTmp!FirstSPEmail, , , "Account Revenue Worksheet", "Please complete and fax to me", False
'RstTmp.MoveNext
'Old SendObject that worked except for 3000 character GPF ends here

'New Begins here that doesn't loop properly
Set clsSendObject = New accSendObject
clsSendObject.SendObject acSendReport, "rptWorksheetRentalEmail", accOutputSNP, _
RstTmp!FirstSPEmail, , , "Account Revenue Worksheet", "Please complete and fax to me", False
Set clsSendObject = Nothing

Next Loop1

MsgBox (Loop1 - 1) & " Messages sent"

db.Close

Exit_cmdEmail_Click:
Exit Sub

Err_cmdEmail_Click:
MsgBox Err.Description
Resume Exit_cmdEmail_Click

End Sub

 

[Bushido121]

You commented out the line that moves the recordset to the next field.

'RstTmp.MoveNext

You need to move to the next field each time you go through the loop or your code will always send the first entry.

Jerid

 

[newone]

Well that was pretty stupid of me and a very easy fix. I don't know how I did that and didn't notice.

Thanks very much!