report in a loop

[iccsi]

I use doCmd.SendObject to send out the reports by in email in the
loop.

I just realized that the report does not re accept new parameters from
my VBA code.

I tried to force close the report like following:

DoCmd.Close acReport, "MyReport"


DoCmd.SendObject acSendReport, stDocName, acFormatRTF,
rs.Fields("Email_ID").Value, fOSUserName, , _
" My Content", False

it still gets the first report result.

Any workaround for this issue?

Your help is great appreciated,

 

[tkelley via AccessMonster.com]

I assume you're doing a rs.movenext? Can you post all of the code in the
procedure?

 

[iccsi]

I assume you're doing a rs.movenext?  Can you post all of the code in the
procedure?

strMySQL = " SELECT Employee_ID, First_Name, Email_ID FROM tblEmployee
"



Set rs = CurrentDb.OpenRecordset(strMySQL)
rs.MoveFirst

While Not rs.EOF
Set qdfCurr = CurrentDb().QueryDefs("MyReport")
qdfCurr.Parameters("[forms]![frmCSRElementsMain]!
[cmbEmployee]").Value = rs.Fields("Employee_ID").Value


stDocName = "MyReport"

DoCmd.Close acReport, "MyReport"
DoCmd.SendObject acSendReport, stDocName, acFormatRTF,
rs.Fields("Email_ID").Value, fOSUserName, , _
" Current result from " &
FormatDateTime(CDate(Int(dtpStartDate.Value)), 2) & _
" to " & FormatDateTime(CDate(Int(dtpEndDate.Value)), 2),
rs.Fields("First_Name").Value & "," & Chr(10) & Chr(13) & Chr(10) &
Chr(13) & MailMsg & _
Chr(10) & Chr(13) & Chr(10) & Chr(13) & Signature & Chr(10) &
Chr(13) & Chr(10) & Chr(13) & _
Format(Now, "dd/mm/yyyy"), False


rs.MoveNext
Wend

 

[tkelley via AccessMonster.com]

I'm not familiar with looping without a "Do" and a "Loop". Have you put a
break in and actually watched it loop through your recordset, changing the
values as you expect?

I assume you're doing a rs.movenext?  Can you post all of the code in the
procedure?

[quoted text clipped - 23 lines]

- Show quoted text -

strMySQL = " SELECT Employee_ID, First_Name, Email_ID FROM tblEmployee
"

Set rs = CurrentDb.OpenRecordset(strMySQL)
rs.MoveFirst

While Not rs.EOF
Set qdfCurr = CurrentDb().QueryDefs("MyReport")
qdfCurr.Parameters("[forms]![frmCSRElementsMain]!
[cmbEmployee]").Value = rs.Fields("Employee_ID").Value

stDocName = "MyReport"

DoCmd.Close acReport, "MyReport"
DoCmd.SendObject acSendReport, stDocName, acFormatRTF,
rs.Fields("Email_ID").Value, fOSUserName, , _
" Current result from " &
FormatDateTime(CDate(Int(dtpStartDate.Value)), 2) & _
" to " & FormatDateTime(CDate(Int(dtpEndDate.Value)), 2),
rs.Fields("First_Name").Value & "," & Chr(10) & Chr(13) & Chr(10) &
Chr(13) & MailMsg & _
Chr(10) & Chr(13) & Chr(10) & Chr(13) & Signature & Chr(10) &
Chr(13) & Chr(10) & Chr(13) & _
Format(Now, "dd/mm/yyyy"), False

rs.MoveNext
Wend

 

[iccsi]

I'm not familiar with looping without a "Do" and a "Loop".  Have you put a
break in and actually watched it loop through your recordset, changing the
values as you expect?

I assume you're doing a rs.movenext?  Can you post all of the code in the
procedure?

[quoted text clipped - 23 lines]

- Show quoted text -

strMySQL = " SELECT Employee_ID, First_Name, Email_ID FROM tblEmployee
"

 Set rs = CurrentDb.OpenRecordset(strMySQL)
rs.MoveFirst

   While Not rs.EOF
   Set qdfCurr = CurrentDb().QueryDefs("MyReport")
   qdfCurr.Parameters("[forms]![frmCSRElementsMain]!
[cmbEmployee]").Value = rs.Fields("Employee_ID").Value

  stDocName = "MyReport"

  DoCmd.Close acReport, "MyReport"
  DoCmd.SendObject acSendReport, stDocName, acFormatRTF,
rs.Fields("Email_ID").Value, fOSUserName, , _
    " Current result from " &
FormatDateTime(CDate(Int(dtpStartDate.Value)), 2) & _
     " to " & FormatDateTime(CDate(Int(dtpEndDate.Value)), 2),
rs.Fields("First_Name").Value & "," & Chr(10) & Chr(13) & Chr(10) &
Chr(13) & MailMsg & _
    Chr(10) & Chr(13) & Chr(10) & Chr(13) & Signature & Chr(10) &
Chr(13) & Chr(10) & Chr(13) & _
    Format(Now, "dd/mm/yyyy"), False

  rs.MoveNext
 Wend

It does to change record.
I changed to modify qdfcurr.SQL at run time in setad of pass
parameters then it works.

Thanks for helping,