Save A Report As a Landscape PDF

[oakleaf8]

I successfully used the "Save A Report As a PDF" FAQ and a Do Until Loop to generate several hundred PDF reports for each record in a table. Thanks for the FAQ!!!

A question: How do I generate PDFs in landscape orientation since that is what the Access report must be?

Thanks.

 

[bblekfeld]

Could you tell me where the "Save A Report As a PDF" FAQ is you mentioned?

 

[oakleaf8]

FAQ703-2657 ("Save A Report As a PDF" FAQ)

The landscape issue I'm having is turns out to be an issue with how the Access Report Page is set up. When I printed from a different PC, it was set up to print portrait, where mine was landscape with different margins.

Is there an FAQ that defines the VBA code to set up report page setup so that no matter where I print a report from, the page properties are set up the same? Thanks.

 

[PHV]

Do a keyword search in this forum for page setup

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SgtJarrow]

The landscape/portrait issue is something you will encounter over and over....Basically, you design a report on your PC, with printer A attached, and you set page margins and orientation. Another user opens the report from their PC and they have the same printer with the same drivers, you should be okay. But if the other user does not have ther same printer as default, or even if their driver is different, the report is opened based on their default printer settings....I haven't found a successful way around it yet that didn't involve A LOT of coding to first check for a printer, do they match, if not install printer, etc....or let the user know they may not see the right view. If everyone uses the same printers in the office, you can also open the report page setup and set a default printer for the report.....this can help a lot if it is feasible.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[oakleaf8]

Thanks! Your explanation helps.

 

[BobJacksonNCI]

oakleaf8,

You didn't say what flavor of Access you're running...

It's my understanding this problem was addressed in 2003, but I don't have it.

If you're using something older, you can force the settings with some work.

See thread702-560490



HTH,
Bob

 

[kmclane]

Hi Oakleaf8, I am trying to accomplish the same task. I have a report that has 66 records this month and need to create a separate pdf file for each record, using a field from the record, "name1" as the file name. I looked at the faq, and I have no problem with that part, but I need some help with the looping and file naming. Can you point me in the direction, since you've been there?
Thanks, Ken.

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[oakleaf8]

Create a module with this function:

Function fncSaveReports()

    Dim qdfTemp As DAO.QueryDef
    Dim strSQL As String
    Dim rst As DAO.Recordset
    Dim fld As DAO.Field
    Dim stDocName As String

    stDocName = "rptYourReport"
        
    Set rst = CurrentDb.OpenRecordset("qryUsedByReport")
    Set fld = rst("[fldUsedToRunEachPDFReport]")

    rst.MoveFirst

    Do Until rst.EOF
    
    'create the SQL string to select the record for each report
    strSQL = "SELECT* FROM tblContainingYourRecords WHERE [fldUsedToRunEachPDFReport] = '" & fld & "'"

    'open the query that runs the report
    Set qdfTemp = CurrentDb.QueryDefs("qryUsedByReport")
    
    'assign strSQL to the SQL property of the Query. 
    qdfTemp.SQL = strSQL

    'Set qdfTemp to nothing so you don't have open objects
    Set qdfTemp = Nothing
    
    'Call the SaveReportAsPDF from the Tek-Tips FAQ function saved in a module
    Call SaveReportAsPDF(stDocName, "C:\YourDirectory" & fld & ".pdf")
      
    rst.MoveNext
    
    Loop

End Function

 

[kmclane]

I'm getting a "circular refererence" error. I think it may have to do with this code:
strSQL = "SELECT * FROM tblContainingYourRecords WHERE [fldUsedToRunEachPDFReport] = '" & fld & "'"

I have no table, just a query. To be precise my query pulls from about 30 tables. And it keeps trying to name the pdf file the title of the report.
Ken

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[kmclane]

I see that you have the query opening twice, is there a reason for this? I believe this is causing the circular reference error.
Ken

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[oakleaf8]

You are right, I have an error. Set rst = CurrentDb.OpenRecordset("qryUsedByReport") should have read Set rst = CurrentDb.OpenRecordset("tblContainingYourRecords")

Let's start over. Try this:

First create a new query with all the fields from your original query that produced the 66 records. Set your monthly report to run from this. Let's call the new query qryForReport, the original query qryOriginal, and the report rptMonthly.

You want one report per each record per the unique field name1. Each report will have the file name: value in name1.pdf

Your code should be:

Function fncSaveReports()

    Dim qdfTemp As DAO.QueryDef
    Dim strSQL As String
    Dim rst As DAO.Recordset
    Dim fld As DAO.Field
    Dim stDocName As String

    stDocName = "rptMonthly"
        
    Set rst = CurrentDb.OpenRecordset("qryOriginal")
    Set fld = rst("[Name1]")

    rst.MoveFirst

    Do Until rst.EOF
    
    'create the SQL string to select the record for each report
    strSQL = "SELECT* FROM qryOriginal WHERE [Name1] = '" & fld & "'"

    'open the query that runs the report
    Set qdfTemp = CurrentDb.QueryDefs("qryForReport")
    
    'assign strSQL to the SQL property of the Query.
    qdfTemp.SQL = strSQL

    'Set qdfTemp to nothing so you don't have open objects
    Set qdfTemp = Nothing
    
    'Call the SaveReportAsPDF from the Tek-Tips FAQ function saved in a module
    Call SaveReportAsPDF(stDocName, "C:\YourDirectory" & fld & ".pdf")
      
    rst.MoveNext
    
    Loop

End Function

 

[kmclane]

Well, I got my query running again, I made the changes you suggest above, but it is still outputting to the My Documents folder and naming each file "Settlement Report. It is also opening each one as it is created. I thought the Save as report function turned that off. I wonder if I need that function, I am the only one running this report and I have it set to print to the pdf creator by default. Couldn't I just use the DoCmd.output in the loop?
Ken

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg