Print reports to pdf

A

abhay_547

Registered User.
Local time
Tomorrow, 04:11
Joined
Dec 4, 2010
Messages
32
Hi All,

I have form in one of my mdb file which gets opened automatically when I open the mdb file. Now I have two combo boxes on that form first one is Region and second one is country and then I have command button to print.
The first combo box gets populated with regions like USA, Asia, Europe etc.. and second combo box is linked to the first one .i.e. it gets populated with the countries which fall under above region for e.g. if I select USA in combobox1 then it will get populated with Brazil, Argentina etc.. that means every time when the region gets changed in the combobox1 the values in combobox2 get changed. Now I use this file for printing some pdf reports, Actually there is only one report in the mdb file but it's used for generating hundred of reports that is I need to print the report for each country of all regions so I will select the region in combobox1 and country in combobox2 and then click on the command button which will preview the "Report1" with the selected country's data same thing will happen for all countries that means the "Report 1" pulls the data from the data according to the country selected in the combobox2. Now as I have hundreds of reports to print in this mdb file I want to automate this process of printing to pdf file with a macro. I have thought of something like below to automate this process :

I got stuck with VBA code. Below is the code which I have so far. I am facing a error .i.e. Run-time error '3061': Too few parameters. Expected 1. in the below code:

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 It highlights the below line from code:

Code: 
Set rst = CurrentDb.OpenRecordset("Query1", dbOpenSnapshot)
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
Can you please look into the above codes and help me with the same.
I am facing this difficulty as I haven't worked on ms access much earlier. Please help..

Thanks for your help in advance :)
 
Hi again - you forgot to post the sql from the query.

Also, what is in your combos? More than one column or just one in each?
 
If Query 1 has a parameter, which it would appear to have based on the error, then try removing the parameter from the query and then pass it in the code:
Code: 
Dim strSQL As String
 
strSQL = "Select * From Query1 WHERE [FieldNameHere] = " & Forms!SomeForm.SomeControl
 
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
 
Hi Bob,

I tried to incorporate the change suggested by you in the my code but still I am getting a syntax error on the below line

Code: 
strSQL = "Select * From Query1 WHERE [FullReportname] = " & FullReportname
Below is the SQL code of query1

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. :)
 
Because FullReportName is text you need quotes:


Code: 
strSQL = "Select * From Query1 WHERE [FullReportname] = " & [B][COLOR=red]Chr(34) & [/COLOR][/B] FullReportname [B][COLOR=red]& Chr(34)[/COLOR][/B]
[B][COLOR=#ff0000]
[/COLOR][/B]
 
Hi Bob,

Thanks a lot for your quick reply, I have incorporated the change posted by you in my code but unfortunately it's showing a syntax error in FROM Clause.
Please expedite.

Thanks a lot for your help in advance. :)
 
abhay_547 said:
Hi Bob,

Thanks a lot for your quick reply, I have incorporated the change posted by you in my code but unfortunately it's showing a syntax error in FROM Clause.
Please expedite.

Thanks a lot for your help in advance. :)
Click to expand...

Can you post the code you now have which is causing the error?
 
Unfortunately I have not (I had a few things come up at home the past few days I had to deal with instead).
 
boblarson said:
Unfortunately I have not (I had a few things come up at home the past few days I had to deal with instead).
Click to expand...

Ok. No worries. Please let me know once you get the chance to look into the same.

Thanks a lot for your help in advance.

Regards,
Abhy
 
Hey Bob,

Apologies to bother you again but did you get the chance to look at my mail in this weekend.

Regards,
Abhy

abhay_547 said:
Ok. No worries. Please let me know once you get the chance to look into the same.

Thanks a lot for your help in advance.

Regards,
Abhy
Click to expand...
 
Okay, first up - here's code which will print all of the reports. The part about printing to PDF is going to need some more work. But one thing I did notice is that in the code you were trying to start working with the PDF's is that you opened the report and then tried setting some parts of the report. You can't do that. You need to either have the report refer to some things on a form that may be visible or hidden and then also in order to modify the report name to pdf you will need to use that code I gave you which makes the change in the Windows Registry for each report as it prints. OR you need to use the Stephen Lebans code which you have in Module1. With the code that Stephen Lebans take a look at my second set of code below.
Code: 
Private Function PrintAllReports()
    Dim rst    As DAO.Recordset
    Dim strSQL As String
 
    strSQL = "SELECT [Local - RAG Status].SILO, [2010 CTB Program Data].PROGRAM_NAME, [Local - RAG Status].ProgramID " & _
             "FROM [2010 CTB Program Data] INNER JOIN [Local - RAG Status] ON [2010 CTB Program Data].ID = [Local - RAG Status].ProgramID " & _
             "ORDER BY [Local - RAG Status].SILO, [2010 CTB Program Data].PROGRAM_NAME;"

    Do Until rst.EOF

        DoCmd.OpenReport "rptStatus Report - Printable - Main", acViewNormal, WhereCondition:="REPORT_MONTH=" & Chr(34) & Forms![frmSelect Program]![SELECT_REPORT_MONTH] & Chr(34) & _
                                                                                              " AND ProgramID=" & rst!ProgramID & " AND Silo =" & Chr(34) & rst!SILO & Chr(34)
        rst.MoveNext
    Loop

End Function

I will email your database to you tonight since there is a bit more to it than I can post here. I have created a function in the form frmSelectProgram and have created a new module "basSQLTools" which has code from Access MVP Armen Stein which will let you do a ReplaceWhereClause so we can iterate through. So this is the function:
Code: 
Private Function PrintAllReportsToPDF()
    Dim rst    As DAO.Recordset
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
    strSQL = "SELECT [Local - RAG Status].SILO, [2010 CTB Program Data].PROGRAM_NAME, [Local - RAG Status].ProgramID " & _
             "FROM [2010 CTB Program Data] INNER JOIN [Local - RAG Status] ON [2010 CTB Program Data].ID = [Local - RAG Status].ProgramID " & _
             "ORDER BY [Local - RAG Status].SILO, [2010 CTB Program Data].PROGRAM_NAME;"
    
    
    
    Do Until rst.EOF
    Set qdf = CurrentDb.QueryDefs("qryStatus Report - Printable")
    qdf.SQL = ReplaceWhereClause(qdf.SQL, "Where REPORT_MONTH=" & Chr(34) & Forms![frmSelect Program]![SELECT_REPORT_MONTH] & Chr(34) & _
                                                                                              " AND ProgramID=" & rst!ProgramID & " AND Silo =" & Chr(34) & rst!SILO & Chr(34))
    qdf.Close
        ConvertReportToPDF "rptStatus Report - Printable - Main", , "rptStatus Report - Printable - Main", False, False
    
        rst.MoveNext
    Loop
    Set qdf = Nothing
End Function

And the module basSQLTools I created by pasting the code from:
http://www.jstreettech.com/files/basJStreetSQLTools.zip
 
boblarson said:
Okay, first up - here's code which will print all of the reports. The part about printing to PDF is going to need some more work. But one thing I did notice is that in the code you were trying to start working with the PDF's is that you opened the report and then tried setting some parts of the report. You can't do that. You need to either have the report refer to some things on a form that may be visible or hidden and then also in order to modify the report name to pdf you will need to use that code I gave you which makes the change in the Windows Registry for each report as it prints. OR you need to use the Stephen Lebans code which you have in Module1. With the code that Stephen Lebans take a look at my second set of code below.
Code: 
Private Function PrintAllReports()
    Dim rst    As DAO.Recordset
    Dim strSQL As String
 
    strSQL = "SELECT [Local - RAG Status].SILO, [2010 CTB Program Data].PROGRAM_NAME, [Local - RAG Status].ProgramID " & _
             "FROM [2010 CTB Program Data] INNER JOIN [Local - RAG Status] ON [2010 CTB Program Data].ID = [Local - RAG Status].ProgramID " & _
             "ORDER BY [Local - RAG Status].SILO, [2010 CTB Program Data].PROGRAM_NAME;"

    Do Until rst.EOF

        DoCmd.OpenReport "rptStatus Report - Printable - Main", acViewNormal, WhereCondition:="REPORT_MONTH=" & Chr(34) & Forms![frmSelect Program]![SELECT_REPORT_MONTH] & Chr(34) & _
                                                                                              " AND ProgramID=" & rst!ProgramID & " AND Silo =" & Chr(34) & rst!SILO & Chr(34)
        rst.MoveNext
    Loop

End Function
I will email your database to you tonight since there is a bit more to it than I can post here. I have created a function in the form frmSelectProgram and have created a new module "basSQLTools" which has code from Access MVP Armen Stein which will let you do a ReplaceWhereClause so we can iterate through. So this is the function:
Code: 
Private Function PrintAllReportsToPDF()
    Dim rst    As DAO.Recordset
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
    strSQL = "SELECT [Local - RAG Status].SILO, [2010 CTB Program Data].PROGRAM_NAME, [Local - RAG Status].ProgramID " & _
             "FROM [2010 CTB Program Data] INNER JOIN [Local - RAG Status] ON [2010 CTB Program Data].ID = [Local - RAG Status].ProgramID " & _
             "ORDER BY [Local - RAG Status].SILO, [2010 CTB Program Data].PROGRAM_NAME;"
    
    
    
    Do Until rst.EOF
    Set qdf = CurrentDb.QueryDefs("qryStatus Report - Printable")
    qdf.SQL = ReplaceWhereClause(qdf.SQL, "Where REPORT_MONTH=" & Chr(34) & Forms![frmSelect Program]![SELECT_REPORT_MONTH] & Chr(34) & _
                                                                                              " AND ProgramID=" & rst!ProgramID & " AND Silo =" & Chr(34) & rst!SILO & Chr(34))
    qdf.Close
        ConvertReportToPDF "rptStatus Report - Printable - Main", , "rptStatus Report - Printable - Main", False, False
    
        rst.MoveNext
    Loop
    Set qdf = Nothing
End Function
And the module basSQLTools I created by pasting the code from:
http://www.jstreettech.com/files/basJStreetSQLTools.zip
Click to expand...


Hi Bob,

I have encountered some issues which I have mailed to you. Can you please look into the same.

Thanks,
Abhy
 
Are you using Stephen Lebans' ConvertToPDF or are you just printing to a PDF writer ("PDFCreator")? I am finding that if you print to PDFCreator then you can't change the report name programmatically and it comes up with a dialog each time to name each report, which I'm sure you don't want. But to start with I am enclosing some screenshots of some things that need to be changed:

attachment.php


attachment.php


attachment.php


attachment.php


attachment.php
 

Attachments

  • TestDbToFix01.png
    TestDbToFix01.png
    64.8 KB · Views: 342
  • TestDbToFix02.png
    TestDbToFix02.png
    59.4 KB · Views: 366
  • TestDbToFix03.png
    TestDbToFix03.png
    74.4 KB · Views: 332
  • TestDbToFix04.png
    TestDbToFix04.png
    55 KB · Views: 344
  • TestDbToFix05.png
    TestDbToFix05.png
    17 KB · Views: 342
And some more:

attachment.php


attachment.php


attachment.php
 

Attachments

  • TestDbToFix06.png
    TestDbToFix06.png
    68.2 KB · Views: 187
  • TestDbToFix08.png
    TestDbToFix08.png
    51.4 KB · Views: 202
  • TestDbToFix09.png
    TestDbToFix09.png
    54.4 KB · Views: 207
Hi Bob,

I have made the changes as suggested by you in your earlier posts but I am facing the below error now post making those changes whenever I open the file.

Thanks a lot for your help in advance.:)
 

Attachments

  • Error Screenshot.JPG
    Error Screenshot.JPG
    29.5 KB · Views: 134
abhay_547 said:
Hi Bob,

I have made the changes as suggested by you in your earlier posts but I am facing the below error now post making those changes whenever I open the file.

Thanks a lot for your help in advance.:)
Click to expand...

Hold the shift key down when opening it (and hold it down until it has opened) and then go to the VBA window and select DEBUG > COMPILE and it should take you to any errors that exist. You will need to fix them.
 
You must log in or register to reply here.

Similar threads

B
Solved Error 6 Overflow
2
Replies
26
Views
2,257
Gasman
Gasman
S
Export Report to PDF with Index to Records
Replies
7
Views
440
Trevor G
Trevor G
S
Solved Report Sent to Printer?
2 3
Replies
46
Views
2,392
arnelgp
arnelgp
amorosik
How to display a 'running total' on a report preview AND on pdf/print?
Replies
8
Views
1,190
Ken Sheridan
Ken Sheridan
Technics
OK, now to my report for my order form
Replies
6
Views
638
Ken Sheridan
Ken Sheridan

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom