looping over reports

[lxh]

Hi All

I've got about 50 reports to print out in one go, currently I'm using the following code:

Private Sub Print_Click()
On Error GoTo Err_Print_Click

    Dim stDocName As String

    stDocName = "Average Length Of Time"
    DoCmd.OpenReport stDocName, acNormal

Exit_Print_Click:
    Exit Sub

Err_Print_Click:
    MsgBox Err.Description
    Resume Exit_Print_Click
    
End Sub

But I'd prefer to just loop over all reports and print them out - does anyone know how to do this? I've been looking at 'recordset' but to no avail...
Thanks for any help you can give
Lex

 

[ChrisO]

G’day Lex.

You could create a table with the following fields and use something like this: -

Option Explicit
Option Compare Text


Private Sub cmdPrint_Click()
    Dim strSQL     As String
    Dim rstReports As DAO.Recordset
    
    On Error GoTo ErrorHandler

    strSQL = "SELECT ReportName, " & _
                    "PrintRequired, " & _
                    "DatePrinted, " & _
                    "Collate " & _
             "FROM tblReports " & _
             "WHERE PrintRequired = Yes " & _
             "ORDER BY Collate"

    Set rstReports = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
    
    Do Until rstReports.EOF
        DoCmd.OpenReport rstReports!ReportName, acViewNormal
        rstReports.Edit
        rstReports!DatePrinted = Now()
        rstReports.Update
SkipUpdate:
        rstReports.MoveNext
    Loop

ExitProcedure:
    Exit Sub

ErrorHandler:
    MsgBox Err.Description
    Resume SkipUpdate

End Sub

Hope that helps or at least gets you started.

Regards,
Chris.

 

[lxh]

Thanks Chris

Thanks Chris, I'll give a go

Lex