VBA Iteration to create individual reports and save to individual files in PDF.

Question

I want to check each record in a table. If a report has been created and saved, then go to the next record. If not, then create a report for that record and save it.

rs.MoveFirst
    Do While Not rs.EOF
        If rs!RegRptCreated = False Then
            firstname = rs!First
            lastname = rs!Last
            filepath = "C:\Users\Mary\Documents\1 - Girlfriend Gathering\Registration Reports\" & lastname & firstname
            DoCmd.OpenReport "Registration Response", acViewNormal, , "[First] = '" & firstname & "' And [Last] = '" & lastname & "'"
            DoCmd.OutputTo acOutputReport, "Registration Response", acFormatPDF, filepath
            
            
            rs.Edit
            rs!RegRptCreated = True
            rs.Update
            rs.MoveNext
        Else
            rs.MoveNext
        End If
      
    Loop

 Thanks for any help.

---

MS - Teach me to fish

Answer 1

Hi,

So, what exactly is the problem with your code (other than I don't see you closing the report)? Are you getting any errors?

Answer 2

I am testing with two records, Mary and Jane.  

No errors.  I find two files in the folder, Mary and Jane.  Jane's file is labeled correctly but the contents contain Mary's data, not Jane's data.  Mary's report is fine.  Mary's is the first record.

---

MS - Teach me to fish

Answer 3

I am testing with two records, Mary and Jane.  

No errors.  I find two files in the folder, Mary and Jane.  Jane's file is labeled correctly but the contents contain Mary's data, not Jane's data.  Mary's report is fine.  Mary's is the first record.

---

MS - Teach me to fish

Just a guess but maybe it's because you didn't close the report before moving to the next record? What happens if you add a line to close the report before MoveNext?

Answer 4

Thanks.  That didn't really make a difference.

But I did go back to my OpenReport command and changed it to acViewReport instead of acViewNormal.  I have no idea why that would make the difference.

Appreciate your input though.  Just having someone else looking at the code and not telling me my syntax was bad helps. :)

---

MS - Teach me to fish

Answer 5

Oh that acViewReport did make it work.

---

MS - Teach me to fish

Answer 6

Hi,

Congratulations! Glad to hear you got it sorted out. Good luck!