Results 1 to 8 of 8
  1. #1
    mdnikki is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2012
    Posts
    63

    Form to export pdf's

    So I have created a form to export multiple pdf's.

    This report needs to export each pdf separately based on the location (in this case its fund id)



    What is happening is it is running each location into a separate report but each report has the exact same data. All the data from each location.

    I need each report to only contain the data for that location.

    The VB code I am using now is. What am I missing?

    Private Sub Report_PDFs_Click()
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim qdf As QueryDef

    Set db = CurrentDb
    Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT [OPENPLEDGES].[FundID] FROM OPENPLEDGES AND ((OPENPLEDGES.FundID) Like '1*' Or (OPENPLEDGES.FundID) Like '2*' Or (OPENPLEDGES.FundID) Like '3*')));", dbOpenSnapshot)
    Do While Not rst.EOF
    strRptFilter = "[FundID] = " & Chr(34) & rst![FundID] & Chr(34)
    DoCmd.OutputTo acOutputReport, "Open Pledges", acFormatPDF, "H:\Development\AOB Capital Campaign planning\Stewardship & Cultivation\Parish Update Reports\Report Destination" & "" & rst.Fields("FundID") & " " & "Open Pledge" & " " & Format$(DateAdd("m", -1, Now()), "mmmyyyy") & ".pdf"
    DoEvents
    rst.MoveNext
    Loop

    rst.Close
    Set rst = Nothing
    Set qdf = Nothing
    Set db = Nothing
    End Sub

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    the form has a listbox of all locations
    the code will cycle thru a listbox (lstLoc) of every Location, creating the pdf file
    the query uses the listbox to pull only those items, ex:
    select * from table where [location]=forms!fMyForm!lstLoc

    Code:
    sub btnGo_click()
    Dim i As Integer
    dim vLoc
    
    vDir = "c:\temp\"
    For i = 0 To lstLoc.ListCount - 1
       vLoc= lstLoc.ItemData(i)  'get next item in list data
       lstLoc =vLoc     'set listbox to the state
    
       vFilename = "rMyRpt_" & vLoc & ".pdf"
       vFile = vDir & vFileName
    
       docmd.OutputTo acOutputQuery ,"qsMyQuery",acFormatPDF,vFile
        'or
       docmd.OutputTo acOutputReport ,"rMyReport",acFormatPDF,vFile
    Next
    end sub

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101

    Post

    Can you please try this (outputto doesn't allow filters):
    Code:
    Private Sub Report_PDFs_Click()
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim qdf As QueryDef
    
    
    Set db = CurrentDb
    
    
    Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT [OPENPLEDGES].[FundID] FROM OPENPLEDGES AND ((OPENPLEDGES.FundID) Like '1*' Or (OPENPLEDGES.FundID) Like '2*' Or (OPENPLEDGES.FundID) Like '3*')));", dbOpenSnapshot)
    
    Do While Not rst.EOF
        'strRptFilter = "[FundID] = " & Chr(34) & rst![FundID] & Chr(34)
       'DoCmd.OutputTo acOutputReport, "Open Pledges", acFormatPDF, "H:\Development\AOB Capital Campaign planning\Stewardship & _Cultivation\Parish Update Reports\Report Destination" & "" & rst.Fields("FundID") & " " & "Open Pledge" & " " & Format$(DateAdd("m", -1, Now()), "mmmyyyy") & ".pdf"
       
     'Open the report hidden using the WhereCondition argument and window mode hidden - note FundID assumed to be string
    DoCmd.OpenReport "Open Pledges",acViewPreview,,[FundID]= '" & rst![FundID] & "'",acHidden
     'outputto without report name to export the currently open report
     DoCmd.OutputTo acOutputReport, , acFormatPDF, "H:\Development\AOB Capital Campaign planning\Stewardship & _Cultivation\Parish Update Reports\Report Destination" & "" & rst.Fields("FundID") & " " & "Open Pledge" & " " & Format$(DateAdd("m", -1, Now()), "mmmyyyy") & ".pdf" 
        
        DoEvents
        'Close the report
        Docmd.Close asReport,"Open Pledges",acSaveNo
        rst.MoveNext
    Loop
    
    
    rst.Close
    Set rst = Nothing
    Set qdf = Nothing
    Set db = Nothing
    End Sub
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #4
    mdnikki is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2012
    Posts
    63
    Thanks Gicu?
    Do I copy the above in its entirety?

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Yes, make sure the commented out sections are fully green (especially your original docmd.outputto that spans multiple lines).

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  6. #6
    mdnikki is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2012
    Posts
    63
    Sorry for the delay, got pulled onto another project. So I just gave this a shot and one of the docmd lines is red, see below.

    Private Sub Report_PDFs_Click()
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim qdf As QueryDef


    Set db = CurrentDb


    Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT [OPENPLEDGES].[FundID] FROM OPENPLEDGES AND ((OPENPLEDGES.FundID) Like '1*' Or (OPENPLEDGES.FundID) Like '2*' Or (OPENPLEDGES.FundID) Like '3*')));", dbOpenSnapshot)

    Do While Not rst.EOF
    'strRptFilter = "[FundID] = " & Chr(34) & rst![FundID] & Chr(34)
    'DoCmd.OutputTo acOutputReport, "Open Pledges", acFormatPDF, "H:\Development\AOB Capital Campaign planning\Stewardship & _Cultivation\Parish Update Reports\Report Destination" & "" & rst.Fields("FundID") & " " & "Open Pledge" & " " & Format$(DateAdd("m", -1, Now()), "mmmyyyy") & ".pdf"

    'Open the report hidden using the WhereCondition argument and window mode hidden - note FundID assumed to be string
    DoCmd.OpenReport "Open Pledges",acViewPreview,,[FundID]= '" & rst![FundID] & "'",acHidden
    'outputto without report name to export the currently open report
    DoCmd.OutputTo acOutputReport, , acFormatPDF, "H:\Development\AOB Capital Campaign planning\Stewardship & _Cultivation\Parish Update Reports\Report Destination" & "" & rst.Fields("FundID") & " " & "Open Pledge" & " " & Format$(DateAdd("m", -1, Now()), "mmmyyyy") & ".pdf"

    DoEvents
    'Close the report
    DoCmd.Close asReport, "Open Pledges", acSaveNo
    rst.MoveNext
    Loop


    rst.Close
    Set rst = Nothing
    Set qdf = Nothing

  7. #7
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    PMFJI,

    Try
    Code:
    "[FundID]= " & rst![FundID],acHidden

  8. #8
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Looks like it is missing a double quote in front of the first [FundID].
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. Replies: 9
    Last Post: 07-26-2018, 03:56 AM
  2. export using form
    By riantoaji in forum Forms
    Replies: 2
    Last Post: 12-18-2015, 08:35 AM
  3. Export Form And Subform Data To Excel Form Same Worksheet
    By tomtheappraiser in forum Import/Export Data
    Replies: 6
    Last Post: 08-12-2013, 10:39 AM
  4. Auto Export Form
    By Juan4412 in forum Programming
    Replies: 6
    Last Post: 07-14-2011, 07:54 AM
  5. Export Form but Keep Information?
    By SpeedyApocalypse in forum Forms
    Replies: 4
    Last Post: 04-09-2010, 07:30 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