none
Export Access Report to Multiple PDF files RRS feed

  • Question

  • I am trying to export a report from Access 2007 to multiple pdf files based on an identifier. I would like each teacher to receive his/her report, and not all of the other teachers' data. Is there a way to do this? This is the code that I have and run from a button, but it does not work. I receive errors on

    If Me.Dirty Then Me.Dirty = False

    CreateFolder

    I am new to VBA so any help is greatly appreciated!! Thanks!

    Private Sub Command9_Click()
        Dim qdf As DAO.QueryDef
        Dim strSQL As String
        Dim strPathName As String
        Dim blRet As Boolean
        Dim rs As Recordset
        Dim stDocName As String
        
        Dim strSavedSQL As String
        
        If Me.Dirty Then Me.Dirty = False
        stDocName = "rpt-ACT Student Level Reports Internal BM by teacher"
        
       
            
        Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
        
        
        If rs.RecordCount < 1 Then
           MsgBox "Nothing found to process", vbCritical, "Error"
           Exit Sub
        End If
        
        CreateFolder CurrentProject.Path & "\reports"
        
        
          ' store the current SQL
            Set qdf = CurrentDb.QueryDefs("qry-All test scores Teacher Level2012")
            strSavedSQL = qdf.SQL
            qdf.Close
            Set qdf = Nothing
        
        
        Do
        
            Set qdf = CurrentDb.QueryDefs("qry-All test scores Teacher Level2012")
            strSQL = Left(strSavedSQL, InStr(strSavedSQL, ";") - 1) & " and (Orders.OrderID = " & rs!OrderID & ");"
            qdf.SQL = strSQL
            qdf.Close
            Set qdf = Nothing
        
            ' put in the same folder as the database
             strPathName = CurrentProject.Path & "\reports\" & rs!Teacher & ".pdf"
            
            DoCmd.OutputTo acOutputReport, stDocName, acFormatPDF, strPathName
            rs.MoveNext
        
       Loop Until rs.EOF
       rs.Close
       
       Set rs = Nothing
          ' restore the  SQL
            Set qdf = CurrentDb.QueryDefs("qry-All test scores Teacher Level2012")
            qdf.SQL = strSavedSQL
            qdf.Close
            Set qdf = Nothing
    End Sub

    Wednesday, August 29, 2012 4:23 PM