Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Cronsen is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    24

    Save report as PDF for each section (location)

    Good day,

    Thanks to some help regarding my initial report problem, IŽd now like make the whole report more comfortable for the user.
    I basically have a stock report which creates for each location a separate page, including header information (location name, person in charge, address, etc.)
    Instead of saving the report into one big .pdf file, would it be possible to create a macro (?) in order to create .pdfs for each location with a designated file name (for example: location_Name.pdf).

    Any hint is appreciated!

    Thanks and regards,
    Mike

  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
    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
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    377
    I use fSaveReportsAsPDFs...

    https://www.access-diva.com/vba3.html

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    1) Make a copy of the report/query.

    -- Manual method--
    2) Change the report record source to have a where clause for the location. Hard code the location in the code. Test the new report by saving the report as a PDF. Modify the report until it saves properly/correctly.
    3) Create a new form. Add a text box (name = txtLocations??).
    4) Change the report record source where clause to get the location name from the text box (txtLocations).
    5) Test the new report by saving the report as a PDF. Modify the report until it saves properly/correctly.
    6) Add a list box (name = lstLocations??) - the source should be the table that has the locations. If you don't have a table of locations, you can get a list of the unique locations by using a query. When you select a location from the list box, the location should be put in the text box (txtLocations??).
    7) Add a button (name it cmd_exportPDF) to the form with the list box. NOTE: DO NOT put the button on the report!!
    Get the code from https://www.datanumen.com/blogs/expo...-file-via-vba/ Look at the pale yellow section.This code has the path and the file name hard coded.
    8) Change the variable "fileName" to get the value from the text box. Change the filepath variable to point to the folder where you want to save the PDF.
    9) In the line "DoCmd.OutputTo" Change the hard coded "objectName:=Me.Name" to the name of your report.
    10) Click the button. Hopefully, the report still saves as a PDF.


    Now to get tricky.

    You can use the "FileDialogPicker" the pick the FOLDER where you want to save the PDF.
    11) Add a text box (name = txtSavLoc??) to the form for the save location. Add code to pick the save location. Change the code to have the variable "fldrPath" get the location from the save location text box.
    12) You could change the list box to be a multi-select list box. Then change the code to loop through the list box to get the different locations and put the location in the text box. I would add a few delays to allow Access time to save the PDFs.


    That should get you started......



    Drat!! Got in behind June and Gina. I've got to learn to type faster!!

  5. #5
    Cronsen is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    24
    Thanks, IŽll try that after solving my other issue!

  6. #6
    Cronsen is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    24
    Quote Originally Posted by GinaWhipp View Post
    I use fSaveReportsAsPDFs...

    https://www.access-diva.com/vba3.html
    Thanks!
    I tried this solution and so far it seems to work.
    I still need to adjust it to my main report, but my first testing was successful. Thanks!

  7. #7
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    377
    Glad that worked. If you run into any issues, we'll be here!

  8. #8
    Cronsen is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    24
    Thanks Gina!
    I think I run into an issue, and I might know what might be the cause already, but would like to ask for your advice.

    Here is the code:

    Code:
    Function fSaveReportsAsPDF() As String
    On Error GoTo Error_Proc
    ' From https://www.access-diva.com/
    ' https://www.access-diva.com/vba3.html
     
        DoCmd.Hourglass True
     
        Dim strSQL As String
        Dim rs As Recordset
        Dim strPath As String
     
        strPath = "D:\Endeavor SBC\Consulting\16 - Mephisto\01 - IT\01 - DB\Reports\"  
        strSQL = "SELECT Location_Name FROM 20181018_Stock_Report"                                     
    
     
        Set rs = CurrentDb.OpenRecordset(strSQL)
     
        With rs
            .MoveFirst
                Do While Not .EOF
                     DoCmd.OpenReport "20181018_Location_Report", acViewPreview, ,  "[Location_Name] = '" & rs!Location_Name & "'" 'Select Report,  Search Field
                    DoCmd.Minimize
                     DoCmd.OutputTo acOutputReport, "20181018_Location_Report", acFormatPDF,  strPath & !Location_Name & ".pdf"
                    DoCmd.Close acReport, "20181018_Location_Report", acSaveNo
     
            .MoveNext
            Loop
        End With
     
        rs.Close
        Set rs = Nothing
     
    Exit_Proc:
        DoCmd.Hourglass False
        Exit Function
    Error_Proc:
        Select Case Err.Number
            Case 287:
              Resume Exit_Proc 'ignore the error
            Case Else:
              MsgBox "Error encountered fSaveReports: " & Err.Description, vbExclamation, Err.Number
              Resume Exit_Proc 'display a message then exit
        End Select
     
    End Function
    20181018_Stock_Report is, as the name suggest, the report I use as the basis of the actual report in Access. Just a plain table listing each store + product sold + size.
    20181018_Location_Report is my Access report, based on the table I just mentioned (+ 2 other tables), organized via crosstab and grouped by Location Name in order to generate a separate page for each location.
    If I now execute the function, is works - up to a certain point where (I assume) it gets stuck in a loop. My question is: " strSQL = "SELECT Location_Name FROM 20181018_Stock_Report"" indicates the search term, correct? The item "Location_Name" in the Stock_Report table is not a unique name, meaning it is mentioned several times during the document. So, my guess would be that due to this the function is not properly finishing, as there are several hundred locations with the same name.

    My guess would be that I add a unique identifier to my report (like location_ID) and set strSQL to the location master table instead of the report. In that case my values would be unique...
    ... but of course, this is just a guess as I donŽt have any clue in the end ;-)

    Thanks and regards,
    Mike

  9. #9
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    377
    Sorry for delay but *day job* got in the way.

    The strSQL is the Recordsource of the Report so not really a *Search Term* though used in the Criteria section. You are right about Location_Name not being unique. Drop the Location PK in the Recordsource and include in the strSQL.

  10. #10
    Cronsen is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    24
    Hi Gina,

    Many thanks!
    Alright, so strSQL would need to point to my query the report is based on, correct?
    Still, in the query I do have multiple rows with the same Location PK. I tried putting the PK in the record source but still get the same result as before.
    Can I adjust the strSQL in order to only take each ID once?

    Thanks and regards,
    Michael

  11. #11
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    In the code from https://www.access-diva.com/vba3.html there is a line
    Code:
    strSQL = "SELECT UniqueField FROM tblYourTable"
    You don't have a unique field, but you could try using the DISTINCT keyword
    Code:
    strSQL = "SELECT DISTINCT Location_Name FROM 20181018_Stock_Report"
    This would only allow one location of a particular name.

  12. #12
    Cronsen is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    24
    Thanks Steve,
    IŽll try that!

    Mike

  13. #13
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    377
    Hmm, Steve gave you something to try but I'm more curious why your RecordSource as more than one Location Name. If it's duplicating you might want to switch to a Report\Subreport scenario which would definitely give you the results you want.

  14. #14
    Cronsen is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    24
    And it worked perfectly!
    Many thanks for the hint!

    Mike

  15. #15
    Cronsen is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    24
    Quote Originally Posted by GinaWhipp View Post
    Hmm, Steve gave you something to try but I'm more curious why your RecordSource as more than one Location Name. If it's duplicating you might want to switch to a Report\Subreport scenario which would definitely give you the results you want.
    Thanks Gina - IŽll have a look into the report/Subreport scenario! For now, the function does what it is supposed to do.

    Mike

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 06-18-2016, 09:06 AM
  2. Replies: 1
    Last Post: 03-31-2016, 08:31 AM
  3. User pick location to save expoerted data
    By MrDummy in forum Import/Export Data
    Replies: 6
    Last Post: 03-29-2016, 03:04 PM
  4. Replies: 4
    Last Post: 08-26-2015, 02:30 PM
  5. Replies: 6
    Last Post: 03-26-2013, 12:17 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