Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2009
    Posts
    79

    Unanswered: how to print multiple reports to a pdfcreator and save them on specific path

    Hi All,

    I have an Access report linked to records in a query. I currently print one report (to file as a PDF) at a time by filtering by an report name, but have hundreds of reports to print and save. I am looking for code or macro instructions to print one report at a time and save each report with a unique file name that is specified in a separate field but in the same record within the source query. I need to print one report for every record in the query. I also need to specify the filepath where all the reports will be saved, but that can be the same for all the reports.

    Would be grateful for any help, suggestions, references, etc.



    Regards,
    Abhy

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It mainly depends on how you can control the pdfcreator.
    Have a nice day!

  3. #3
    Join Date
    Sep 2009
    Posts
    79
    Hi Sinndho,

    Thanks for a quick reply, Here is what I have so far

    Code:
    Private Sub Command25_Click()
    Dim strDefaultPrinter As String
    
    ' get current default printer.
    
    strDefaultPrinter = Application.Printer.DeviceName
    
    ' switch to printer of your choice:
    
    Set Application.Printer = Application.Printers("PDFCreator")
    
    
    If Option_Ref_Loc_Bad_Debt_By_Paycode = True Then
    DoCmd.OpenReport "Strpt Report", acViewNormal
    DoCmd.PrintOut
    DoCmd.Close acReport, "Strpt Report", acSaveNo
    
    End If
    
    ' Switch back to original default printer
    
    Set Application.Printer = Application.Printers(strDefaultPrinter)
    
    End Sub
    Actually I have access database with a form which gets open directly when I open the file. The form has two dropdown's / list boxes on it with to select the parameters once we select the parameters we can click on the print button and then it opens the report is preview and then we need to hit ctrl + p and print it to pdf there are hundreds of such reports so I want to come up with a code which will print all reports automatically to pdf and then save them on a specific path on local drive. The main issue is there are no separate reports there is one report and then code is querying data on the same report as per the parameters selected by user on the form.

    Regards,
    Abhy

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    As far as I can understand, there are two problems to solve.

    1. Print a report for each row in a data set.

    This can be done by passing a criteria that specifies for which row (record) the report must be open or printed. One of the optional parameters of the OpenReport method of the DoCmd object, the WhereCondition, allows to do that:
    Code:
    DoCmd.OpenReport(ReportName, View, FilterName, WhereCondition, WindowMode, OpenArgs)
    If you have a report (Report1) that is based on a query (Query1) that has a [CustomerID] column, you can open Report1 for printing only the record for one customer only:
    Code:
    Dim CustomerID As Long
    
    CustomerID = 1234
    DoCmd.OpenReport "Report1", acViewNormal, , "CustomerID=" & CustomerID
    Notice that, as in your code, acViewNormal opens the report for printing. To open it in Preview mode you need to use the acViewPreview constant.

    If the combobox (what you name 'dropdown / list box') uses a Query (or a SELECT SQL statement) as its RowSource (let's suppose it's named Query1), you can open a Recordset on it, loop through all its rows and open the report for each row:
    Code:
    Dim rst As DAO.Recordset
    Dim CustomerID As Long
    
    Set rst = CurrentDb.OpenRecordset("Query1", dbOpenSnapshot)
    With rst
        Do Until .EOF
            CustomerID = !CustomerID
            DoCmd.OpenReport "Report1", acViewNormal, , "CustomerID=" & CustomerID
            .MoveNext
        Loop
        .Close
    End With
    Set rst = Nothing
    2. Create a separate .pdf file for each report.

    There is no single easy solution, it depends on what pdf generator you use. Ideally you should use one that allows to specify the name of the output file.

    In such case you can pass this value or compute it from the criteria mentionned in point1.

    If it's not possible, i.e. the name of the generated pdf file is always the same (or at least cannot easily be modified by code), you'll have to rename the resulting file after each DoCmd.OpenReport command in the loop.

    Suppose that the output file is always "Report1.pdf" and that you want each file to be named "Rpt_<CustomerID>.pdf. You could use:
    Code:
    Dim rst As DAO.Recordset
    Dim CustomerID As Long
    
    Set rst = CurrentDb.OpenRecordset("Query1", dbOpenSnapshot)
    With rst
        Do Until .EOF
            CustomerID = !CustomerID
            DoCmd.OpenReport "Report1", acViewNormal, , "CustomerID=" & CustomerID
            Name "Report1.pdf" As "Rpt_" & CustomerID & ".pdf"
            .MoveNext
        Loop
        .Close
    End With
    Set rst = Nothing
    Have a nice day!

  5. #5
    Join Date
    Sep 2009
    Posts
    79
    Hi Sinndho,

    I have go through the code provided by you but I am still struggling to incorporate the same in my main code. The code posted by me in my earlier post was what I was trying to write from my end however the code which exists in my mdb file differs from what I have posted earlier. As I had mentioned earlier I have a userform which opens directly when I open my database and there are two comboboxes in which user needs to select the parameters and then he needs to click on the print button. Now the above mentioned combo boxes gets populated with queries and they are interlinked with query for e.g. Combobox1 gets populated with region and combobox2 gets populated with the country in that region so if you select "Asia" Region in Combobox1 then Combobox2 will get populated with countries like "Singapore", "Japan", "Hongkong" etc. Now you will have to select one of the country and then print report and need to do the same exercise for all regions. Now this means that whenever the selection in combobox1 selection changes the combobox2 requeries it's data in order to populate the respective region countries in it. Pls chck your inbox. I want the code to print the files to pdf using "pdfcreator" as printer and then save the files on a specfic path like "C:\".

    Thanks a lot for your help in advance

    Regards,
    Abhy

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If you have a functional system that does not yield the results you expect you'll have to modify your system in order to reach the desired goal. I provided a path leading to a possible solution, nothing more, nothing less. The code I posted was just a skeleton you can use to adapt your database.
    Have a nice day!

  7. #7
    Join Date
    Sep 2009
    Posts
    79
    Hi Sinndho,

    Thanks for your quick reply, I have tried to incorporate the code provided by you in my main code but I am still facing a error .i.e. Run-time error '3061': Too few parameters. Expected 1. Below is the code which I have worked on :

    Code:
    Private Sub Commandbutton_Click ()
    Dim rst As DAO.Recordset
    Dim CustomerID As Long
    
    Set rst = CurrentDb.OpenRecordset("Query1", dbOpenSnapshot)
    Debug.Print rst
    With rst
        Do Until .EOF
            ProgramID = !ProgramID
            DoCmd.OpenReport "StrptReport", acViewPreview, , "CustomerID=" & CustomerID
           Reports![StrptReport].Caption = [FullReportName]
            Name "StrptReport" As [FullReportName] & ".pdf"
            .MoveNext
        Loop
        .Close
    End With
    Set rst = Nothing
    End sub
    When I click on the debug error It highlights the below line from code:

    Code:
    Set rst = CurrentDb.OpenRecordset("Query1", dbOpenSnapshot)
    Thanks a lot for your help in advance.

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    What's the SQL statement of Query1?
    Have a nice day!

  9. #9
    Join Date
    Sep 2009
    Posts
    79
    Hi Sinndho,

    Apologies, I am unable to view the sql query as it's protected by the person who had worked on that query earlier. I have struggled a lot for the same but couldn't get into it so now I have decided to create a macro instead of writing a query for this. thanks for your help I am trying to work on macro for this may be you can help me in the same.

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Sorry but I never use Macro's and won't be able to help you with them. Please consider that you won't be able to do with a macro what you cannot do with VBA, it's works just the other way.
    Have a nice day!

  11. #11
    Join Date
    Sep 2009
    Posts
    79
    Hi Sinndho,

    I have struggled a lot to come up with the macro for the below but unfortunately couldn't get any success on the same so far. Below is the sql code of Query1 which I was able to finally get from the mdb file: Can you please have a look into the same :

    Code:
    SELECT [Table1].Region, [Table2].Country, [Table1].ProgramID, Right([ReportMonth],4) AS [Year], Left([ReportMonth],Len([ReportMonth])-5) AS [ReportMonth], Replace(Replace(Replace([Table1]!Country,"&","and"),"/","-"),":"," -") AS Country1, [Year] & " " & " Monthly Report " & [ReportMonth] & " - " & [Table1]!Region & " - " & [Country1] AS FullReportName
    FROM [Table3], ([Table4] INNER JOIN [Table1] ON [Table4].ID = [Table1].ProgramID) INNER JOIN [qry2-regionselector] ON [Table1].Region = [qry2-regionselector].Region
    WHERE ((([table3].ReportMonth)="April 2011"))
    ORDER BY [Table1].SILO, [Table4].Country;
    Thanks for your help in advance

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    From this part of the query:
    Code:
    INNER JOIN [qry2-regionselector] ON [Table1].Region = [qry2-regionselector].Region
    It seems that something named [qry2-regionselector] must be available (open?) when you open Query1. The next question is: What's [qry2-regionselector]?
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •