Help with Looping thru RS to Output PDF

[ElvisR]

I am now learning recordsets and how they can be used.
My code below is supposed to open a table and loop thru it and create a PDF of the 2-page report for each record. The only part that is not working a intended is that the PDF being generated is for all the records that comes before the rs edit.

So the first PDF will have all records in the rs, then next PDF will have all except the first record, etc etc

Please review for guidance.

Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim strINTCODE As String
Dim strNewPDF as string
Dim strSQL as string

strSQL = "SELECT * FROM Employees WHERE PerformanceRating IS NULL"
strNew PDF = DoCmd.OutputTo acOutputReport, "Performance Plan", acFormatPDF, "PATH\" & strEMPID & " Performance Plan " & Format(Date, "mmddyyyy") & ".pdf", False

Set db = CurrentDb
'Open the recordset for use (table, Query, SQL Statement)
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

'If the table is empty, exit.
If rs.EOF Then
'Display message to user
MsgBox "There are no pending plans to be generated.",,"No Pending Plans"
Exit Sub

'Initialize counter
counter = 1

If Not (rs.BOF And rs.EOF) Then
rs.MoveFirst
Do Until rs.EOF
strINTCODE = rs("INTCODE")
strNewPDF
rs.Edit
rs!PerformancePlan = "EmpID-" & strEmpID & "-2014"
rs.Update
rs.MoveNext
counter = counter + 1
Loop
End If
rs.Close

 

[PeterF]

You need to filter your report in preview, then save your PDF and close the report, like:

Do Until .EOF = True

        DoCmd.OpenReport "Performance Plan", acPreview, , "ID =" & rs!ID        
        DoCmd.OutputTo acOutputReport, "Performance Plan", acFormatPDF, "PATH\" & strEMPID & ".pdf", True
        DoCmd.Close acReport, "Performance Plan"
        .MoveNext
Loop

 

[ElvisR]

Thanks. I changed the where statement to a string, but getting error on the SQL statement.

strSQL = "SELECT * from 'Missing Performance Plan' WHERE EmpID = " & strEmpID

When I used your where statement, I get a parameter popup for ID

If Not (rs.BOF And rs.EOF) Then
rs.MoveFirst
Do Until rs.EOF = True
DoCmd.OpenReport "PerformancePlan", acPreview, , strSQL

 

[ElvisR]

Great news, it works; to a point. It only generated the PDF for the first record. As it loops thru all the names show up in the immediate window. Kindly review the code for me:

Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim strINTCODE As String
Dim strSQL As String
strSQL = "SELECT * From Employee WHERE [BLANKET TVL ORDER] is Null"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset) 'open the recordset for use (table, Query, SQL Statement)
rs.MoveLast
rs.MoveFirst
'Debug.Print rs.RecordCount
strINTCODE = rs!INTCODE
'Initialize counter
counter = 0
If Not (rs.BOF And rs.EOF) Then
rs.MoveFirst
Do While Not rs.EOF
DoCmd.OpenReport "BlanketTvlOrder", acPreview, , "[INTCODE]='" & strINTCODE & "'"
DoCmd.OutputTo acOutputReport, "BlanketTvlOrder", acFormatPDF, "M:\Admin\Travel Orders\Approved Blanket Travel Orders\FY2014\" & strINTCODE & " Blanket Travel Order " & Format(Date, "mmddyyyy") & ".pdf", False
DoCmd.Close acReport, "BlanketTvlOrder"
Debug.Print rs!FIRSTNAME & " " & rs!LASTNAME
'rs.Edit
'rs![BLANKET TVL ORDER] = "22-" & strINTCODE & "-14"
'rs.Update
rs.MoveNext
counter = counter + 1
Loop
End If
MsgBox "Travel order " & counter & " of " & rs.RecordCount & " completed sucessfully." & vbCr & "Location: Admin\Travel Orders\Approved Blanket Travel Orders", vbOKOnly, rs.RecordCount & " Travel Order(s) Generated"
rs.Close

Error_Handler_Exit: On Error Resume Next
Set rs = Nothing
Set db = Nothing
End Sub

 

[ElvisR]

Resolved! Thanks.

 

[sonjuraec]

Same Problem here!

Bear with me folks- I am just an English teacher so a lot of this is beyond me. My code is creating a pdf file that includes everybody's reports instead of just that faculty member's reports.

Once I get it to generate the correct reports, I then have to figure out how to automatically email them.:banghead:

Public Sub something3()
Dim db As Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT DISTINCT tblsection.Faculty, left(vtblfaculty.firstname,1)&vtblfaculty.lastname AS fn, vtblfaculty.email FROM vtblfaculty INNER JOIN tblsection ON tblsection.faculty=vtblfaculty.faculty WHERE term=" & Forms!frmimport!cbxTerm)

With rs
While Not rs.EOF
DoCmd.OpenReport "Backgrounds", acViewPreview, , , , "faculty =" & rs!Faculty
DoCmd.OutputTo acOutputReport, "Backgrounds", acFormatPDF, "C:\Users\Sonjurae\Desktop\" & rs!fn
rs.MoveNext

Wend
End With

One other thing is that I can't get the &".pdf" to work on the end of the filename, but that is no big deal right now.