Results 1 to 6 of 6
  1. #1
    catluvr is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Kansas
    Posts
    87

    Question Report saved to multiple PDFs and then e-mailed to different people

    Hello! I know I just posted another thread, but I know not to post multiple topics in one thread ...



    This is along the same lines. I do the reporting for a summer Day Camp. Attendees are grouped into multiple units.

    I run a Unit Roster report that gets saved as one PDF ... there are 20+ units. I send the individual rosters to the respective unit leaders. I do this by pulling up the PDF and extracting the units pages and saving as "Unit#.pdf" I then look up the unit leader's e-mail address and send them the appropriate roster.

    Hopefully, when I work out how to do the other thing I posted about (sending the roster to Excel with one unit on each tab and the tabs named by unit), it will lead me to exporting the unit roster to PDF with one PDF file per unit, named with the unit number.

    The unit numbers range from 0 to 23 or so and then 40 and 50. I can change the 0 unit number if I need to.

    I know I can export data to Excel or other formats and then send it attached to an e-mail to someone. I don't do this, but I can probably work it out.

    Is there a way to export the Unit Roster to PDF, with one file per unit and then send those files to the appropriate unit leader? My ULs are denoted by a position code, so they are easy to pick out ... it would be [position]="UL" ... and these change each year, so I don't want to "hard code" anything, I want the VBA code to automatically pick out the ULs' e-mail addresses.

    This would be such a time saver! The ULs get a preliminary roster, a new roster once busing information is received, and a final roster the weekend before camp starts.

    Thank you!

    Susie

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    A common topic. Here is one thread for a start https://www.accessforums.net/showthread.php?t=25042
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    catluvr is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Kansas
    Posts
    87
    I didn't want you to think I was ignoring you!

    I am working through the code for the other topic I posted about ... spreadsheets with one unit roster per tab. I will post more when I either make it work ... or ... more likely ... Have questions!

    Thank you,
    Susie

  4. #4
    catluvr is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Kansas
    Posts
    87
    Alrighty, I have my Excel code worked out ... YAY!

    Now on to the pdf code.

    I can post my code here, if needed, but I'll start with a run down in case it's something super obvious (to all except me!) Nevermind, I posted the code below.

    I have a unit roster report. I really don't want to duplicate this report because I don't want to have to remember to make changes to two reports.

    The unit roster report uses VBA in the detail section and in the footer section. It also opens in print preview when I click on the "Unit Roster" button.

    I put together code that creates a recordset of UnitIDs and then cycles through those UnitIDs and creates a pdf in a specific location with a filename based on the type of unit.

    The code that creates and cycles through the recordset is a click event on a form while the code for the unit roster report is behind the format event of the detail and the footer.

    When I click the "export to PDF" button I created, the Unit Roster report hangs on a line referencing the UnitID. the error I get is Run-time error 2427: You entered an expression that has no value. I'm pretty sure it was because of where the variables were declared. I then move the declarations to a different part of the code and now the Unit Roster report doesn't work at all ... before it worked on its own and hung when using the recordset. Clear as mud, right???

    Here is my Unit Roster code:
    The footer section VBA is setting up a "page x of y" that changes on each change of unitID
    Code:
    Option Compare DatabaseOption Explicit
    'The following were moved down. Unit roster as a stand alone report worked when the declarations were up here
    'Dim GrpArrayPage(), GrpArrayPages()
    'Dim GrpNameCurrent As Variant, GrpNamePrevious As Variant
    'Dim GrpPage As Integer, GrpPages As Integer
    '************ Code Start *************
    ' This code was originally written by James H Brooks.
    ' It is not to be altered or distributed,
    ' except as part of an application.
    ' You are free to use it in any application,
    ' provided the copyright notice is left unchanged.
    '
    ' Code Courtesy of
    ' James H Brooks
    '
    
    
    
    
    
    
    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    
    
    If [Type] = "A" Then
    txtCampCount.Visible = False
    ElseIf [Type] = "DA" Then
    txtCampCount.Visible = False
    Else
    txtCampCount.Visible = True
    End If
    
    
    If [Type] = "G" Then
    txtTroop.Visible = True
    Else
    txtTroop.Visible = False
    End If
    
    
    If [Type] <> "G" Then
    txtAge.Visible = True
    Else
    txtAge.Visible = False
    End If
    
    
    End Sub
    
    
    Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As Integer)
    
    
    Dim i As Integer
    Dim GrpArrayPage(), GrpArrayPages()   'was at top, under Option Explicit
    Dim GrpNameCurrent As Variant, GrpNamePrevious As Variant 'was at top
    Dim GrpPage As Integer, GrpPages As Integer   ' was at top
      
      If Me.Pages = 0 Then
        ReDim Preserve GrpArrayPage(Me.Page + 1)
        ReDim Preserve GrpArrayPages(Me.Page + 1)
        GrpNameCurrent = Me![UnitID]     'when trying the pdf export, this is where it hangs with error 2427:expression with no value
        If GrpNameCurrent = GrpNamePrevious Then
            GrpArrayPage(Me.Page) = GrpArrayPage(Me.Page - 1) + 1
            GrpPages = GrpArrayPage(Me.Page)
                For i = Me.Page - ((GrpPages) - 1) To Me.Page
                    GrpArrayPages(i) = GrpPages
                Next i
        Else
            GrpPage = 1
            GrpArrayPage(Me.Page) = GrpPage
            GrpArrayPages(Me.Page) = GrpPage
        End If
      Else
        Me!ctlGrpPages = "Page " & GrpArrayPage(Me.Page) & " of " & GrpArrayPages(Me.Page)  'when running the unit roster in a standard way,
                        'the report hangs here with an error 9: subscript out of range
      End If
      GrpNamePrevious = GrpNameCurrent
    
    
    '************ Code End *************
    End Sub
    The Export to PDF code is:

    Code:
    Private Sub cmdRoster2pdf_Click()
    
    Dim strPath As String
    Dim FileName As String
    Dim UnitID As String
    Dim rstUnitPDF As DAO.Recordset
    Dim strRST_SQL_PDF As String        'SQL statement to set up the recordset
    Dim dbs As DAO.Database
    
    
    If Dir("C:\Users\Susie\Documents\Day Camp 2018 Laptop", vbDirectory) <> "" Then
     strPath = "C:\Users\Susie\Documents\Day Camp 2018 Laptop\"
        Else
            strPath = "C:\Users\Gilsons\Documents\DayCamp 2018\"
    End If
    
    
    Set dbs = CurrentDb
    'set up the SQL to extract the recordset … pull unique unit IDs from RegInfo
    strRST_SQL_PDF = "SELECT DISTINCT UnitID from RegInfo WHERE Delete = False;"
    'open the Unit ID recordset
    Set rstUnitPDF = dbs.OpenRecordset(strRST_SQL_PDF, dbOpenDynaset, dbReadOnly)
    'check to see where in the recordset has data in it
    If rstUnitPDF.EOF = False And rstUnitPDF.BOF = False Then
    'move to the beginning of the recordset
        rstUnitPDF.MoveFirst
        'Start the loop and run as long as the unit ID isn't at the end of the recordset
        Do While rstUnitPDF.EOF = False
            'pull unit title using UnitID; this will be used to name the sheet
            FileName = DLookup("Unit", "qry UnitInfo", "UnitID=" & rstUnitPDF!UnitID.Value)
            Debug.Print FileName   'for testing purposes, print the sheet name
            'Send the Unit Roster report to pdf based on Unit ID
            Debug.Print strPath & FileName & ".pdf"   'for testing purposes, print the full file name
            DoCmd.OpenReport "rpt Unit Roster", acViewPreview, , [UnitID] = rstUnitPDF!UnitID.Value
            DoCmd.OutputTo acOutputReport, "rpt Unit Roster", acFormatPDF, strPath & FileName & ".pdf", False
            DoCmd.Close acReport, "rpt Unit Roster", acSaveNo
            
            rstUnitPDF.MoveNext
         'go back to start of loop with new UnitID
         Loop
    'Exit out of the If Then when the recordset is at the end of the file
    End If
    
    
    'close the recordset and erase it
    rstUnitPDF.Close
    Set rstUnitPDF = Nothing
    dbs.Close
    Set dbs = Nothing
    
    
    MsgBox "The PDF Files were created!", vbInformation Or vbOKOnly, "PDF Files Created"
    
    
    End Sub
    Any thoughts?

    Thank you!

    Susie
    Kansas

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Susie,
    Can you try this?

    Code:
    Private Sub cmdRoster2pdf_Click()
    
    
    Dim strPath As String
    Dim FileName As String
    Dim UnitID As String
    Dim rstUnitPDF As DAO.Recordset
    Dim strRST_SQL_PDF As String        'SQL statement to set up the recordset
    Dim dbs As DAO.Database
    
    
    
    
    If Dir("C:\Users\Susie\Documents\Day Camp 2018 Laptop", vbDirectory) <> "" Then
     strPath = "C:\Users\Susie\Documents\Day Camp 2018 Laptop\"
        Else
            strPath = "C:\Users\Gilsons\Documents\DayCamp 2018\"
    End If
    
    
    
    
    Set dbs = CurrentDb
    'set up the SQL to extract the recordset … pull unique unit IDs from RegInfo
    strRST_SQL_PDF = "SELECT DISTINCT UnitID from RegInfo WHERE Delete = False;"
    'open the Unit ID recordset
    Set rstUnitPDF = dbs.OpenRecordset(strRST_SQL_PDF, dbOpenDynaset, dbReadOnly)
    'check to see where in the recordset has data in it
    If rstUnitPDF.EOF = False And rstUnitPDF.BOF = False Then
    'move to the beginning of the recordset
        rstUnitPDF.MoveFirst
        'Start the loop and run as long as the unit ID isn't at the end of the recordset
        Do While rstUnitPDF.EOF = False
            'pull unit title using UnitID; this will be used to name the sheet
            FileName = DLookup("Unit", "qry UnitInfo", "UnitID=" & rstUnitPDF!UnitID.Value)
            Debug.Print FileName   'for testing purposes, print the sheet name
            'Send the Unit Roster report to pdf based on Unit ID
            Debug.Print strPath & FileName & ".pdf"   'for testing purposes, print the full file name
            DoCmd.OpenReport "rpt Unit Roster", acViewPreview, , "[UnitID] = " & rstUnitPDF!UnitID.Value
            DoCmd.OutputTo acOutputReport, , acFormatPDF, strPath & FileName & ".pdf", False
           'DoCmd.Close acReport, "rpt Unit Roster", acSaveNo
            
            rstUnitPDF.MoveNext
         'go back to start of loop with new UnitID
         Loop
    'Exit out of the If Then when the recordset is at the end of the file
    End If
    
    
    
    
    'close the recordset and erase it
    rstUnitPDF.Close
    Set rstUnitPDF = Nothing
    dbs.Close
    Set dbs = Nothing
    
    
    
    
    MsgBox "The PDF Files were created!", vbInformation Or vbOKOnly, "PDF Files Created"
    
    
    
    
    End Sub
    Cheers,
    Vlad

  6. #6
    catluvr is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Kansas
    Posts
    87
    Turns out I had some quotation marks missing or in the wrong place.

    All works great now!

    Thanks!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Access Report to multiple PDFs FILES
    By vianda in forum Reports
    Replies: 3
    Last Post: 08-11-2020, 08:18 PM
  2. Creating multiple PDFs from 1 report
    By ManipulatoRX in forum Reports
    Replies: 1
    Last Post: 01-10-2018, 05:09 PM
  3. Saving Report as multiple .pdfs
    By Eskoraczewski in forum Programming
    Replies: 4
    Last Post: 03-31-2016, 05:50 PM
  4. Replies: 1
    Last Post: 08-06-2014, 02:22 PM
  5. Replies: 10
    Last Post: 12-07-2012, 01:57 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums