querydef and subreports (1 Viewer)

[sha7jpm]

querydefs and subreports

Hello

I have some code which cycles through a recordset of school establishment numbers and then prints out a report for each school, I am trying to adapt that code for a report that has 3 subreports, is it purely that the sql for the querydef needs to include the underlying queries for the report to source from or will the subreport element fall over? if so, any ideas on how to overcome this?

here is an excerpt of the code below..

many thanks

John

Private Sub KS4_Click()
Dim repQuery2 As QueryDef
Dim dBase2 As Database
Dim rsRep2 As DAO.Recordset
Dim strrep2 As String
Dim data2 As Integer
Dim KS4_iresponse1 As Integer
Dim KS4_iresponse2 As Integer

Set dBase2 = CurrentDb()
Set repQuery2 = dBase2.QueryDefs("PDF_KS4_pdf_export")
Set rsRep2 = CurrentDb.OpenRecordset("1_KS4_PerformanceForms")

Do While Not rsRep2.EOF
data2 = rsRep2.Fields("DFES NO").Value
repQuery2.sql = "SELECT SCHOOL_BASE_DATA_SCHOOL_BASIC_DATA.DFES, * FROM 2_KS4_report_query INNER JOIN SCHOOL_BASE_DATA_SCHOOL_BASIC_DATA ON [2_KS4_report_query].estab = SCHOOL_BASE_DATA_SCHOOL_BASIC_DATA.DFES WHERE ((([2_KS4_report_query].estab)= " & data2 & "));"
repQuery2.Close
DoCmd.Rename "KS4_" & data2, acReport, "KS4_report"
DoCmd.OpenReport "KS4_" & data2, acViewPreview
DoCmd.RunCommand acCmdPrint
DoCmd.RunCommand acCmdClose
DoCmd.Rename "KS4_report", acReport, "KS4_" & data2
rsRep2.MoveNext
Loop

Set rsRep2 = Nothing


End Select
End Select

Exit_KS4_Click:
    Exit Sub

Err_KS4_Click:
    MsgBox Err.Description
    Resume Exit_KS4_Click
End Sub