Results 1 to 4 of 4
  1. #1
    vianda is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    6

    Access Report to multiple PDFs FILES

    I have a report consisting of providers with their # of cases. The number of providers varies as the # of cases. I need to separate them by provider to a pdf file.
    At this moment the code I have it is exporting 98 providers and creating 98 pdf files with providers names. But each file contains all the providers. I have 90 pdf files with 98 providers in each pdf file.


    Something obviously is not working.

    This goes in button to export file to PDF
    Code:
     
    Private Sub Export_to_PDF_Click()
    Dim rst As DAO.Recordset
    Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT [Provider'sName] FROM [TotalPayrollProviders] ORDER BY [Provider'sName];", dbOpenSnapshot)
    Do While Not rst.EOF
        strRptFilter = "[Provider'sName] = " & Chr(34) & rst![Provider'sName] & Chr(34)
        
        DoCmd.OutputTo acOutputReport, "TotalPayrollProviderStatementsPDF", acFormatPDF, "F:\PAYROLL\PDF" & "\" & rst![Provider'sName] & ".pdf"
        DoEvents
        rst.MoveNext
    Loop
    rst.Close
    Set rst = Nothing
    End Sub
    Also in Close and Open events of Report to be exported
    Code:
    Private Sub Report_Close()
    strRptFilter = vbNullString
    End Sub
    Private Sub Report_Open(Cancel As Integer)
    If Len(strRptFilter) <> 0 Then
         Me.Filter = strRptFilter
         Me.FilterOn = True
    End If
    End Sub
    And a Public Variable
    Code:
    Public strRptFilter As String
    My report also has a break on Provider'sname footer (yes) and on Page header (no)

    I would appreciate if anybody look the code I have and give me some guidance. VBA Code is not my strength.

    Thanks

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Make a form,on the form is a list of providers. Make the report query look at this list in the report as criteria.
    the report will only show data for the provider selected in the list.
    Scan the list,print the report.

    Code:
    sub btnPrint_click()
    For I = 0 to lstProv.listcount-1
      Itm = lstProv.itemData(I)    'Get next item in list
      lstProv = itm         'Set list to next item
          'Print report for that provider
        Docmd.outputTo.....
    Next
    end sub

  3. #3
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 8 Access 2013
    Join Date
    Jul 2010
    Location
    Oklahoma, USA
    Posts
    702
    Quote Originally Posted by vianda View Post
    I have a report consisting of providers with their # of cases. The number of providers varies as the # of cases. I need to separate them by provider to a pdf file.
    At this moment the code I have it is exporting 98 providers and creating 98 pdf files with providers names. But each file contains all the providers. I have 90 pdf files with 98 providers in each pdf file.
    Something obviously is not working.

    This goes in button to export file to PDF
    Code:
     
    Private Sub Export_to_PDF_Click()
    Dim rst As DAO.Recordset
    Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT [Provider'sName] FROM [TotalPayrollProviders] ORDER BY [Provider'sName];", dbOpenSnapshot)
    Do While Not rst.EOF
        strRptFilter = "[Provider'sName] = " & Chr(34) & rst![Provider'sName] & Chr(34)
        
        DoCmd.OutputTo acOutputReport, "TotalPayrollProviderStatementsPDF", acFormatPDF, "F:\PAYROLL\PDF" & "\" & rst![Provider'sName] & ".pdf"
        DoEvents
        rst.MoveNext
    Loop
    rst.Close
    Set rst = Nothing
    End Sub
    Also in Close and Open events of Report to be exported
    Code:
    Private Sub Report_Close()
    strRptFilter = vbNullString
    End Sub
    Private Sub Report_Open(Cancel As Integer)
    If Len(strRptFilter) <> 0 Then
         Me.Filter = strRptFilter
         Me.FilterOn = True
    End If
    End Sub
    And a Public Variable
    Code:
    Public strRptFilter As String
    My report also has a break on Provider'sname footer (yes) and on Page header (no)

    I would appreciate if anybody look the code I have and give me some guidance. VBA Code is not my strength.

    Thanks
    You're m close.

    The DoCmd.OutputTo does not allow a filter to be applied.

    The trick is to open the report in preview mode first to apply the WHERE parameter to filter the records. Only need two additioanl lines lines of code.

    You will not need a global variable or code in the reporet's On Open Event.

    If yu already havce a report aht will print for multilpe providers to paper now, you shoudl be able to use that oe. You should not need a special version of the report jsut to save to PDF. I have not had to so far.

    I did add some other code that I normally use.


    Code:
     
    Private Sub Export_to_PDF_Click()
    Dim rst As DAO.Recordset
    
    Public strRptFilter As String   ' not need to use a public variable
    
    
    Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT [Provider'sName] FROM  [TotalPayrollProviders] ORDER BY [Provider'sName];", dbOpenSnapshot)
    
    ' make sure that we have data
    If  rst.RecordCount > 0 then 
    
        rst.MoveFirst
    
    Do While Not rst.EOF
        strRptFilter = "[Provider'sName] = " & Chr(34) & rst![Provider'sName] & Chr(34)
        
         ' open the report hidden in preview mode setting the where parameter 
    
         DoCmd.OpenReport "TotalPayrollProviderStatementsPDF",acViewPreview,,strRptFilter,acHidden
    
        ' save the opened report
    
         DoCmd.OutputTo acOutputReport, "TotalPayrollProviderStatementsPDF",  acFormatPDF, "F:\PAYROLL\PDF" & "\" & rst![Provider'sName] &  ".pdf"
    
    
        ' close the report
    
          DoCmd.Close acReport, ""TotalPayrollProviderStatementsPDF"
    
        DoEvents
        rst.MoveNext
    Loop
    
    End if ' rst.RecordCount > 0
    
    rst.Close
    Set rst = Nothing
    End Sub


    It it were mine, I would use the Profivder's Priamry key to select the reocrds not the name.

    Something like
    Code:
    "SELECT DISTINCT [ProviderUniqueID], [Provider'sName] FROM  [TotalPayrollProviders] ORDER BY [Provider'sName];"
    Code:
    strRptFilter = "[ProviderUniqueID] = " & rst![ProviderUniqueID]

  4. #4
    tai783 is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Posts
    1
    You are a genius.

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

Similar Threads

  1. Saving Report as multiple .pdfs
    By Eskoraczewski in forum Programming
    Replies: 4
    Last Post: 03-31-2016, 05:50 PM
  2. Attach Multiple PDFs to Access via Script
    By dimitrius20 in forum Access
    Replies: 2
    Last Post: 03-06-2015, 02:31 PM
  3. Replies: 10
    Last Post: 12-07-2012, 01:57 PM
  4. Print linked pdfs within an Access Report II
    By cjwagner in forum Reports
    Replies: 3
    Last Post: 05-30-2011, 04:25 PM
  5. Print linked pdfs within an Access Report
    By alpruett in forum Reports
    Replies: 6
    Last Post: 05-20-2011, 05:25 AM

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