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

    export of report in separated pdf pages is not working

    I was exporting a report to pdf format but separating it by names ( each name can have one to several pages) I don't know what is going on. I have a Run-time error time 2501

    Code:

    Private Sub Command50_Click()
    Dim rsPayroll As DAO.Recordset
    Set rsPayroll = CurrentDb.OpenRecordset("SELECT DISTINCT [Provider'sName] FROM [TotalPayrollProviders]")
    With rsPayroll
    Do Until rsPayroll.EOF
    DoCmd.OpenReport "TotalPayrollProviderStatements", acViewPreview, WhereCondition:="[Provider'sName] = " & Chr(34) & ![Provider'sName] & Chr(34), WindowMode:=acHidden
    DoCmd.OutputTo acOutputReport, "TotalPayrollProviderStatements", acFormatPDF, "F:\PAYROLL\PDF" & "" & ![Provider'sName] & ".pdf"
    DoCmd.Close acReport, "TotalPayrollProviderStatements", acSaveNo
    rsPayroll.MoveNext
    Loop
    rsPayroll.Close



    End With
    End Sub


    According to debugger the blue text is the problem.
    Hope somebody can help.

    Viviana

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    put listbox in a form with all the names. Cycle thru exporting each 1 at a time.
    the query in the report looks at the listbox:
    select * from table where [ClientID]=forms!myForm!lstBox

    Code:
    sub btnPrintRpts_click()
    dim vID, vDir , vFile<vCustName
    dim i as integer
    
    
    vDir = "c:\temp\"
    for i = 0 to lstBox.listcount -1
        vID = lstBox.itemdata(i)      'get next item in listbox
        lstBox = vID		  'set the listbox to this item
        vCustName = lstBox.column(1)  'name in col2 (in vb, columns begin with zero)
    
    
            'get vitals off the listbox
    
    
          vFile = vDir & "Invoice_" & vCustName & ".pdf"
          docmd.OutputTo acOutputReport ,"rMyReport",acFormatPDF,vFile   
    next
    end sub

  3. #3
    Eugene-LS's Avatar
    Eugene-LS is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Dec 2018
    Location
    Murmansk
    Posts
    17
    Quote Originally Posted by vianda View Post
    I was exporting a report to pdf format but separating it by names ( each name can have one to several pages) I don't know what is going on. I have a Run-time error time 2501
    Try that way:
    Code:
    Private Sub Command50_Click()
    Dim rsPayroll As DAO.Recordset
    Dim sSQL As String
    Dim sReportName As String
    Dim sExportPathWP As String
    Dim sExportName As String
    Dim sFilePath As String
    
        sReportName = "TotalPayrollProviderStatements"
        sExportPathWP = "F:\PAYROLL\PDF"
    
        sSQL = "SELECT DISTINCT [Provider'sName] FROM TotalPayrollProviders"
        Set rsPayroll = CurrentDb.OpenRecordset(sSQL)
    
        With rsPayroll
            Do Until .EOF
                sExportName = .Fields(0) 'Value of First field in collection (We have only ONE)
                
                sFilePath = sExportPathWP & sExportName & ".pdf"
                'Debug.Print sFilePath
                
                sSQL = "[Provider'sName] = '" & sExportName & "'" 'Filter (Where condition)
                DoCmd.OpenReport sReportName, acViewPreview, , sSQL, acHidden
    
                'Export:
                DoCmd.OutputTo acOutputReport, sReportName, acFormatPDF, sFilePath
                DoCmd.Close acReport, sReportName, acSaveNo
            .MoveNext
            Loop
            
        End With
    
    On Error Resume Next
        rsPayroll.Close
        Set rsPayroll = Nothing
        Err.Clear
        
    End Sub

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Post 3 was moderated, I'm posting to trigger email notifications. I deleted the duplicate post.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 2
    Last Post: 03-23-2017, 02:55 PM
  2. [Page] of [Pages] not working
    By halabis in forum Reports
    Replies: 2
    Last Post: 11-16-2015, 09:46 AM
  3. Replies: 1
    Last Post: 05-25-2013, 11:19 AM
  4. Export Report to Single PDF Pages
    By 02kev02 in forum Import/Export Data
    Replies: 2
    Last Post: 05-23-2012, 07:03 AM
  5. Blank pages between report pages
    By jonsuns7 in forum Reports
    Replies: 2
    Last Post: 10-01-2009, 05:06 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