recordset - printing access report pdf

[sha7jpm]

hi,

I am trying to find a way to avoid printing many access reports (about 800) to pdf through a recordset, I have used recordsets in the past but this one is more complicated. Basically i have a query that uses a school reference number and a year field as criteria for the report. then the report is printed to pdf and the recordset moves to the next one...

the problem i have is how to link to the base table of school numbers to cycle through.

when the report starts to print, acrobat prompts me for a save location & then I have a circular reference error highlighting the row "DoCmd.OpenReport "school_summary_scores"

any ideas?!
ta
john


Private Sub Command1_Click()
Dim repQuery As QueryDef
Dim dBase As Database
Dim rsRep As DAO.Recordset
Dim strrep As String
Dim data1 As String


Set dBase = CurrentDb()
Set repQuery = dBase.QueryDefs("school_report_profile")

repQuery.sql = "Select * From [school_report_profile] WHERE (([year] = 2005) AND ([dfes]=3103));"

repQuery.Close

Set rsRep = CurrentDb.OpenRecordset("SCHOOL_BASE_DATA_SCHOOL_BASIC_DATA")

Do While Not rsRep.EOF
DoCmd.OpenReport "school_summary_scores"
'DoCmd.PrintOut acPrintAll
DoCmd.Close acReport, "school_summary_scores"
MsgBox "done"
rsRep.MoveNext
Loop

Set rsRep = Nothing

End Sub

 

[richary]

You don't need VBA to cycle through the school numbers, just re-design your report.

Add a grouping level to the report which uses the school id ([dfes]???) and access will basically print a report for a single school 'n' times, where 'n' is the number of schools that you have.

The VBA code that attempts to cycle through each report printing each one is not necassary.

I'm not sure about the error that you get, but its possible this will go away when you remove the Loop.