Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2015
    Posts
    3

    Unanswered: How Can I Print Multiple PDFs From A Single 2010 Access Report?

    If this has been addressed, my apologies.

    I have a student report in 2010 Access based on a single query which is based on a single table. I only run the table through the query for data manipulation (concatenation etc.)

    What I want to do is use some VBA code to print multiple PDF files from the report based on the student ID (called AlternateId) from the table. Better yet, I'd like to base the PDFs on a constructed field in the query called Alternate ID which is a concatenation of the AlternateId and the first two letters of the student last name and have each PDF called that, like '1234567 AB' for example. There are variable pages for each student.

    At any rate, I've seen tantalizing glimpses on numerous forums of how this might be accomplished but it makes my brain ache since I know just enough about VBA to get myself confused. I know there will be looping involved and something about recordsets, but beyond that I'm quite lost.

    Here are the proper names of what I am using:

    db LaserFiche Service Logs
    Tbl Weekly Billed Sessions
    QRY SY 2015 WEEKLY BILLED SESSIONS
    Rpt SY 2015 Weekly Billed Sessions

    I have Adobe Acrobat X Pro.

    Here is a copy of my data base with sample information (the actual data is confidential). Thank you in advance!
    Attached Files Attached Files
    Last edited by williedog; 01-06-15 at 14:10.

  2. #2
    Join Date
    Jan 2015
    Posts
    3

    My First Attempt

    Okay I modified this code I found elsewhere and gave it a shot:


    Code:
    Private Sub Make_PDFs()
    
    Dim MyDB As DAO.Database
    Dim MyRS As DAO.Recordset
    Dim strSQL As String
    Dim strRptName As String
    Dim count As Integer
     
    strRptName = "Rpt_SY_2015_Weekly_Billed_Sessions"
    strSQL = "Select Tbl_Weekly_Billed_Sessions.[AlternateId] From QRY_SY_2015_WEEKLY_BILLED_SESSIONS;"
    
    Set MyDB = CurrentDb
    Set MyRS = MyDB.OpenRecordset(strSQL, db_LaserFiche_Service_Logs)
    
    With MyRS
        Do While Not MyRS.EOF
            DoCmd.OpenReport strRptName, acViewPreview, , "[AlternateId]=" & ![AlternateId]
            DoCmd.OutputTo acOutputReport, strRptName, acFormatPDF, "C:\TASB\" & ![AlternateId] & " " & ".pdf"
            DoCmd.Close acReport, strRptName, acSaveNo
            .MoveNext
        Loop
    End With
    
    MyRS.Close
    Set MyRS = Nothing
    
    End Sub

    However, when I run it I get Run-time error '3001': Invalid argument and the 'Set MyRS' line is highlighted (I bolded it above). Am I close? Also, is it necessary to replace the spaces in the names of my database and Access Objects with underscores?

    Thanks in advance to anyone who can help.
    Last edited by williedog; 01-06-15 at 16:36.

  3. #3
    Join Date
    Jan 2015
    Posts
    3

    Second Attempt

    I don't know if this is better or worse -

    Code:
    Option Compare Database
    Private Sub Make_PDFs()
    
    Dim MyDB As DAO.Database
    Dim MyRS As DAO.Recordset
    Dim strSQL As String
    Dim strRptName As String
    Dim count As Integer
     
    strRptName = "[Rpt SY 2015 Weekly Billed Sessions]"
    strSQL = "Select [Tbl Weekly Billed Sessions].[AlternateId] From [QRY SY 2015 WEEKLY BILLED SESSIONS];"
    
    Set MyDB = CurrentDb
    Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)
    
    With MyRS
        Do While Not MyRS.EOF
            DoCmd.OpenReport strRptName, acViewPreview, , "[AlternateId]=" & ![AlternateId]
            DoCmd.OutputTo acOutputReport, strRptName, acFormatPDF, "C:\TASB\" & ![AlternateId] & " " & ".pdf"
            DoCmd.Close acReport, strRptName, acSaveNo
            .MoveNext
        Loop
    End With
    
    MyRS.Close
    Set MyRS = Nothing
    
    End Sub
    Now I get this error message -

    Run-time error '2103': The report name '[Rpt SY 2015 Weekly Billed Sessions]' you entered in either the property sheet or macro is misspelled or refers to a report that doesn't exist.

    The error message references the bolded text in the Do loop above. That is the real report name; I copied and pasted it to make sure.
    Last edited by williedog; 01-06-15 at 16:35.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •