Create loop to open and export reports

Question

Background: I have a database I am in the process of making for the school board. I am in the final stages and have never had a loop that I couldn't figure out...except for this one.

Setup: Here is the SQL on the query that runs the report "rpt_SeatingChart_AMFirst"

SELECT tbl_Students.SchoolName, tbl_Students.StudentCalculated, tbl_Stops.[Stop Location], tbl_Stops.[Stop Time], tbl_Students.AssignSeat, tbl_Students.DateOB, tbl_Students.Address, tbl_Students.Zip, tbl_Students.Phone1, tbl_Students.Phone2, tbl_Students.Grade, tbl_Students.PhysicalLimitations, tbl_Students.ParentReq, tbl_Schools.[School Name], tbl_Students.ActiveRider
FROM tbl_Schools INNER JOIN (tbl_Stops INNER JOIN tbl_Students ON tbl_Stops.ID = tbl_Students.[Stop LocationAM]) ON (tbl_Schools.ID = tbl_Stops.School) AND (tbl_Schools.ID = tbl_Students.SchoolName)
WHERE (((tbl_Schools.[School Name])=[Forms]![frm_SchoolPickerAM]![Combo113]) AND ((tbl_Students.ActiveRider)="Active"));

The form that it is asking for the input from, is what school the report should display. So if I pick School1 from combo113, the report would show only students assigned to that school. I have a table with schools in it found here: [tbl_Schools]![SchoolName].

Execution: On the click event of a button, I would like to have [rpt_SeatingChart_AMFirst] open with the first [School Name] from the [tbl_Schools] table. Export that report as a PDF, close and reopen as the second [School Name] int he table. Export and repeat until all the schools opened in that report from the table.

Answer 1

Edit your code as follows:

Private Sub GreenBinderReport_Click() 

    Dim OP1 As String
    Dim RPT As String
    Dim rs As DAO.Recordset

    Set rs = CurrentDb.OpenRecordset("select [School Name] from tbl_Schools")
    OP1 = "C:\Users\Joel Dahl\Desktop\ExportTest\"
    RPT = "rpt_SeatingChart_TEST"

    Do While Not rs.EOF
        DoCmd.OpenReport "rpt_SeatingChart_TEST", acViewPreview, , "[School Name] = '" & rs(0) & "'"
        DoCmd.OutputTo acOutputReport, RPT, acFormatPDF, OP1 & rs(0) & ".pdf"
        DoCmd.Close acReport, RPT
        rs.MoveNext
    Loop

    Set rs = Nothing
End Sub

Answer 2

I cant add a comment, but try this edit to what Skippy posted.

While Not rs.EOF
    DoCmd.OpenReport "rpt_SeatingChart_AMFirst", , , "[School Name] = '" & rs.fields("[School Name]").value & "'"
    rs.MoveNext
Loop

Set rs = Nothing 

Answer 3

I would take the [School Name] condition out of the WHERE clause in the report's query so that you can specify this when you open the report. Your button event needs to open a recordset

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("select [School Name] from tbl_Schools")

You can then loop through this recordset and pass the school name to the OpenReport command

While Not rs.EOF
    DoCmd.OpenReport "rpt_SeatingChart_AMFirst", , , "[School Name] = '" & rs(0) & "'"
    rs.MoveNext
Loop

Set rs = Nothing 

This is completely from memory so you might need to check the number of commas in the OpenReport command - it's the Where condition parameter that you're after. You can use the same Where condition trick if you want to open the report from the form with the selected combo box value.

Hope this helps.

[Edit] I just spotted the PDF requirement. What I do for this is create a specific PDF version of the report where you set the report's properties to print to a specific printer, namely a PDF printer driver. Within the loop you can rename the PDF file with the school name so it doesn't get overwritten by the next school's report.

[Another edit!] Just remembered, you need to make sure the report has finished creating the PDF file before you try and rename it. I can't remember the exact code for this so unless anyone else posts a better answer in the meantime you'll need to wait until I get back in the office before I can give you this.