Access 2007 VBA Do While Loop Repeats Same Output 2

[DataHog2]

I have a control table with various reports that I want to output to PDF. In Access 2007, I wrote a Do While Loop to go through the local table (30 records) and print out each report. The problem is that it just prints out the same report 30 times. Can anyone tell me what is wrong with this code? I have used the Do While Loop for similar purposes many times. This is the first time I have encountered this error. I am very frustrated and would appreciate some advice.

Sub mod_TestPrint()
On Error GoTo mod_TestPrint_Err

Dim dbs As DAO.Database, rst As DAO.Recordset, StrRptName As String, strPath As String

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblEmailControlPrc", dbOpenTable, dbReadOnly)

StrRptName = rst!ReportName
strPath = rst!FilePath & Format(Date, "mmmm") & rst!FileName

rst.MoveLast
rst.MoveFirst

Do While Not rst.EOF
DoCmd.OutputTo acOutputReport, StrRptName, "PDFFormat(*.pdf)", strPath, False, "", 0, acExportQualityPrint
rst.MoveNext
Loop 

rst.Close

Set rst = Nothing
Set db = Nothing

MsgBox Prompt:="Pricing Reports have been saved to pdf."

mod_TestPrint_Exit:
Exit Sub

mod_TestPrint_Err:
MsgBox Error$
Resume mod_TestPrint_Exit
End Sub

 

[randy700]

You never change the value in strRptName.
You need to update it within the Do Loop - after the MoveNext statement.


Randy

 

[DataHog2]

Randy,
I appreciate your time on this, but I still don't get it. The value in strRptName is supposed to be coming from the table/recordset, so rst.MoveNext is supposed to send the loop to the next record in the table. The report name will be different in that record.

I don't understand what I'm missing. Can you please explain it more specifically? Do I need to clear the values so the next record will load? ???

Thanks again.

 

[SkipVought]

[s]
StrRptName = rst!ReportName
strPath = rst!FilePath & Format(Date, "mmmm") & rst!FileName
[/s]
rst.MoveLast
rst.MoveFirst

Do While Not rst.EOF
[b]
  StrRptName = rst!ReportName
  strPath = rst!FilePath & Format(Date, "mmmm") & rst!FileName
[/b]
  DoCmd.OutputTo acOutputReport, StrRptName, "PDFFormat(*.pdf)",   strPath, False, "", 0, acExportQualityPrint
  rst.MoveNext
Loop

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[randy700]

Once you've set the variable value it will not change just because you moved on to the next record.
You have to populate it again.

Skip's example will work.


Randy

 

[DataHog2]

Skip and Randy,
Thanks so much! I had to change it a little, but it worked! I know this seems like a "duh", but I could not see the forest for the trees. Thanks again! You guys are the best! This is what I ended up with:

    Do While Not rst.EOF
            strRptName = rst!ReportName
            strPath = rst!FilePath & "_" & Format(Date, "mmmm") & rst!FileName
           DoCmd.OutputTo acOutputReport, strRptName, "PDFFormat(*.pdf)", strPath, False, "", 0, acExportQualityPrint
         rst.MoveNext
    Loop