Report loop doesn't work

[Evon]

I have a button on a form that should allow me to open a report,
filter it to a single supervisor's information, output it to a pdf
file and then it SHOULD move to the next record and do it all over
again. What it's really doing, however, is opening the report for a
single supervisor and then outputting that same report 35 times (there
are 35 supervisors, hence 35 iterations in the loop). Can someone
help me understand why the rs.MoveNext isn't working? Many thanks for
any help!

Option Compare Database
_____________________________________________________________________________________________
Private Sub Command8_Click()

Dim strReport As String
Dim strWhere As String
Dim strSave As String
Dim strPath As String
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("qry_Convert_Supervisor")


strReport = "Recall Roster"
strWhere = "Supv ='" & Sup_ID & "'"
strPath = Left(CurrentDb.Name, InStrRev(CurrentDb.Name, "\", ,
vbTextCompare))
strPDF = strPath & "Reports\" & strPDF
strPDF = Replace(strPDF, "\", "\\")
strSave = strPDF & Me.Sup_LName & "_" & Format(Date, "yymmdd") &
".pdf"

'create the directory to save reports to if it doesn't exist
If Dir(strPath & "Reports\", vbDirectory) = "" Then
MkDir strPath & "Reports\"
End If

'move to the beginning of the recordset
rs.MoveFirst

'begin loop
Do Until rs.EOF
'create temporary registry entry to allow saving pdf files
SaveSetting "Dane Prairie Systems", "Win2PDF", "PDFFileName",
strSave
'set the printer to the Win2PDF application **THIS MUST BE SET AS THE
DEFAULT PRINTER**
Application.Printer = Application.Printers("Win2PDF")
'open / print the report
DoCmd.OpenReport strReport, acViewNormal, , strWhere
'close the report
DoCmd.Close acReport, strReport
'move to the next record
rs.MoveNext
'if the loop is complete then exit
If rs.EOF Then Exit Do
'repeat the loop if not end of file
Loop

'close the recordset
rs.Close

Exit Sub

End Sub

 

[Klatuu]

You are never repopulating strWhere with a different supervisor. Also, your
control logic could be better:

Are you sure this has to be done each time? It seems to me they could be
before the loop starts:

'create temporary registry entry to allow saving pdf files
SaveSetting "Dane Prairie Systems", "Win2PDF", "PDFFileName",
strSave
'set the printer to the Win2PDF application **THIS MUST BE SET AS THE
DEFAULT PRINTER**
Application.Printer = Application.Printers("Win2PDF")

I don't think you need to close the report, it should close when it is done
printing:
'close the report
DoCmd.Close acReport, strReport


Do While Not rs.EOF
strWhere = "Supv ='" & Sup_ID & "'"
DoCmd.OpenReport strReport, acViewNormal, , strWhere
'move to the next record
rs.MoveNext
Loop

 

[Evon]

You are never repopulating strWhere with a different supervisor. Also, your
control logic could be better:

Are you sure this has to be done each time?  It seems to me they could be
before the loop starts:

'create temporary registry entry to allow saving pdf files
    SaveSetting "Dane Prairie Systems", "Win2PDF", "PDFFileName",
strSave
'set the printer to the Win2PDF application **THIS MUST BE SET AS THE
DEFAULT PRINTER**
    Application.Printer = Application.Printers("Win2PDF")

I don't think you need to close the report, it should close when it is done
printing:
'close the report
    DoCmd.Close acReport, strReport

    Do While Not rs.EOF
        strWhere = "Supv ='" & Sup_ID & "'"
        DoCmd.OpenReport strReport, acViewNormal, , strWhere
'move to the next record
        rs.MoveNext
    Loop

--
Dave Hargis, Microsoft Access MVP















- Show quoted text -

Hi, Dave, thanks for the quick follow up.

According to the Win2PDF documentation the SaveSetting method is valid
for only a single report and then the registry entry is removed. I
moved it outside the loop and it quit working for all but the first
time it printed the report. I also moved the strWhere definition
inside the loop but I'm still only getting the first report 35
times.

Do you see any other errors?

Thanks again for your help.

 

[Klatuu]

Well, maybe you need to add the recordset qualifier to the assignment:
strWhere = "Supv ='" & rs!Sup_ID & "'"

Also, I missed this part:
strSave = strPDF & Me.Sup_LName & "_" & Format(Date, "yymmdd") &
".pdf"

Since you are running a different report for each supervisor, you need to
change the filename you are saving to for each report.

But, Why not just run the report one time?
You could use a group header for the supervisor, and force a page break, or
is it you need to distribute a copy to each supervisor?

 

[Evon]

Well, maybe you need to add the recordset qualifier to the assignment:
strWhere = "Supv ='" & rs!Sup_ID & "'"

Also, I missed this part:
strSave = strPDF & Me.Sup_LName & "_" & Format(Date, "yymmdd") &
".pdf"

Since you are running a different report for each supervisor, you need to
change the filename you are saving to for each report.

But, Why not just run the report one time?
You could use a group header for the supervisor, and force a page break, or
is it you need to distribute a copy to each supervisor?

Hi, Dave,

The strSave should be inside the loop, too, huh? Doh! And it should
be

strPDF & rs.Fields("Sup_LName").Value & "_" & Format(Date, "yymmdd") &
".pdf"

Right?

The next step will be to set up a procedure to email each of these
reports to the appropriate supervisor using an SMTP program. So
you're right - each supervisor needs just their own information and
should not receive any other supervisor's info.

I'm trying to break down the coding into managable steps, though - get
each one working right and then move on to the next step. Kind of
like eating an elephant, lol! Right now I need to get the loop
moving.

Once again, thanks for your help!

 

[Evon]

Woohoo!!! Success!!! Thanks a million!!!

 

[Klatuu]

I would code it like this:
strSave = strPDF & rs![Sup_LName] & "_" & Format(Date, "yymmdd") & ".pdf"

 

[Evon]

I would code it like this:
 strSave = strPDF & rs![Sup_LName] & "_" & Format(Date, "yymmdd") & "..pdf"

--
Dave Hargis, Microsoft Access MVP

Hi, Dave,

The strSave should be inside the loop, too, huh?  Doh!  And it should
be

strPDF & rs.Fields("Sup_LName").Value & "_" & Format(Date, "yymmdd") &
".pdf"

The next step will be to set up a procedure to email each of these
reports to the appropriate supervisor using an SMTP program.  So
you're right - each supervisor needs just their own information and
should not receive any other supervisor's info.

I'm trying to break down the coding into managable steps, though - get
each one working right and then move on to the next step.  Kind of
like eating an elephant, lol!  Right now I need to get the loop
moving.

Once again, thanks for your help!- Hide quoted text -

- Show quoted text -

Why would that be a better way to code it? I've done some C++
programming but don't really know anything about VBA so I don't
understand the syntax and grammar very well.

 

[Klatuu]

There is nothing wrong with the way youave it coded. It is just a matter of
readability and in one case, not necessary.

In this part:
rs.Fields("Sup_LName")

You are referring to the fields collection of the recordset using the field
name as an index. It is the same as:
rs.Sup_LName

The .Value property is unnecessary. It is the default property.

I never said it was wrong. I just said I would code it differently. I think
you would find most VBA developers would code it more like I would.

Ususally, if I address the Fields collection for a recordset, it would be
looking for a field name or enumerating the fields in a recordset.

Glad you got it working.
--
Dave Hargis, Microsoft Access MVP

I would code it like this:
strSave = strPDF & rs![Sup_LName] & "_" & Format(Date, "yymmdd") & "..pdf"

--
Dave Hargis, Microsoft Access MVP

Well, maybe you need to add the recordset qualifier to the assignment:
strWhere = "Supv ='" & rs!Sup_ID & "'"

Also, I missed this part:
strSave = strPDF & Me.Sup_LName & "_" & Format(Date, "yymmdd") &
".pdf"

Since you are running a different report for each supervisor, you need to
change the filename you are saving to for each report.

But, Why not just run the report one time?
You could use a group header for the supervisor, and force a page break, or
is it you need to distribute a copy to each supervisor?

Hi, Dave,

The strSave should be inside the loop, too, huh? Doh! And it should
be

strPDF & rs.Fields("Sup_LName").Value & "_" & Format(Date, "yymmdd") &
".pdf"

The next step will be to set up a procedure to email each of these
reports to the appropriate supervisor using an SMTP program. So
you're right - each supervisor needs just their own information and
should not receive any other supervisor's info.

I'm trying to break down the coding into managable steps, though - get
each one working right and then move on to the next step. Kind of
like eating an elephant, lol! Right now I need to get the loop
moving.

Once again, thanks for your help!- Hide quoted text -

- Show quoted text -

Why would that be a better way to code it? I've done some C++
programming but don't really know anything about VBA so I don't
understand the syntax and grammar very well.

 

[Evon]

Dave, thanks for the lesson.

I didn't take it as though you were telling me I had done something
wrong, I was just trying to understand why one way was better than the
other. Your explanation was very clear and succinct. I really do
appreciate it!