report in a loop

I

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,
 
T

tkelley via AccessMonster.com

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

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
 
T

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
 
I

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,
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top