Access 2007 how use VBA to loop through Query and loop through print reports.

Question

Hi, I have an existing module that prints a series of reports based on a query (CAP classroom Report gr 5) by looping through another table (School_room_grade). The reports were printed based on a WhereCondition in the module that matched the rows in the School_room_grade table. This process is used so that each school_room_grade combo has their reports all together and resorting of the reports is not required.

One of the reports (CAP Math Gr 5) has been modified and now includes 7 linked subreports which are making the process slow. Each of the subreports is based on the same query but are sorted using different fields. I thought it might be better to unlink the subreports and limit the query to just one school_room_grade combination at at time. When I first made the report without linking the subreports it seemed to run faster. But of course when it is not linked the sorting of reports does not work.

I was thinking that I should make a nested loop with the query on the outside and then loop through the printing of reports. Do you think this would improve the speed?

Here's the original report printing code that worked prior to adding in the linked report.

Option Compare Database

'------------------------------------------------------------
' Print Grade 5 & 6 CAP homeroom reports
'
'------------------------------------------------------------
Sub PrintReports()

    Dim rs As DAO.Recordset
    Dim rptArr As Variant
    Dim rpt As Long

    rptArr = Array("CAP MATH GR 5", "CAP ELA GR 5")

    Set rs = CurrentDb.OpenRecordset("school_room_query_table_5")

    With rs
        .MoveFirst
        Do While Not .EOF
            For rpt = LBound(rptArr) To UBound(rptArr)
                DoCmd.OpenReport ReportName:=rptArr(rpt), View:=acViewNormal, _
                    WhereCondition:="[school_room_grade] = '" & rs!school_room_grade & "'"
            Next
            .MoveNext
        Loop
        .Close
    End With

    MsgBox "Done"

End Sub

The Query that all the reports are based on: [cap classroom report gr 5] - criteria field is [school_room_grade]. The table used for the loop is [school_room_query_table_5] and the matching field is [school_room_grade]

I have never done a loop through a named query - can it be done?