By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,640 Members | 2,279 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,640 IT Pros & Developers. It's quick & easy.

Report to PDF - Leban's PDF Tool. - Need to pass parameters

P: 6
Hi all,

I am using Stephen Leban's Report To PDF tool since many months on my
Ms Access 2003 application.

I am happy to use it because it is really a very good tool.

BUT now I need some help, I have a report say rptEmployees that has recordsource as "Select * from employees..."

I need this report to be opened from several forms so I want to avoid using "where clause" in its recordsource.

Instead I use something like...
Docmd.OpenReport "rptEmployees","","EmployeeID=" & Me.EmployeeID

SO the big question is how can I create a PDF for an specific Employee,
whose EmployeeID=120 or any other value based on the form that opens rptEmployees.

Any help is highly appreciated.

Loving and Learning Ms-Access.
Ozair.
Sep 11 '08 #1
Share this Question
Share on Google+
9 Replies


NeoPa
Expert Mod 15k+
P: 31,495
I'm not familiar with the PDF Tool. Does it not run from the already-opened report?

This sounds like it's specific to the tool interface itself, but we'll help if we can.
Sep 11 '08 #2

P: 6
NeoPa,

Thanks for the reply, it can be used from a click of a command button.


Stephen Leban has been so caring that he published the MDB
that can be obtained from 'http://lebans.com/reporttopdf.htm'

In fact I am expecting that some expert can help me customize Stephen's code to meet my requirement.


Regards,

Ozair - Loving & Learning Ms-Access
Sep 12 '08 #3

puppydogbuddy
Expert 100+
P: 1,923
Hi,
For the record Leban's ReportToPDF is a user defined function (UDF) designed to convert Access Reports and Snapshot files to PDF documents without requiring an installed PDF Printer driver (e.g. Adobe).

http://www.lebans.com/reporttopdf.htm

If I understand your question correctly, you are already getting an unfiltered version of your Access report in PDF format, but do not know how to get a filtered version. For you, the answer is simple. The key trick is to call the Leban's function while the filtered Access report is the active object as shown below:
1.Create a global variable in Access for the report name >> Public gRptName As String. Make sure the variable name used here is the same variable name used in the Leban's function.

2. in your code, open the Access report in preview mode:
gRptName = "rptEmployees"
Docmd.OpenReport gRptName, acPreview, , ,"EmployeeID=" & Me.EmployeeID

3. then, call the Leban's function (I don't remember which option is for the file path)
Call ConvertReportToPDF(gRptName, , , True, False)

4. Close Access Report (DoCmd.Close.......)


thread for other details:
Lebans ReportToPDF solution Help
Sep 12 '08 #4

NeoPa
Expert Mod 15k+
P: 31,495
Thanks for that pDog.

I may have to look into this tool myself sometime. The ability to handle that programmatically instead of via the operator (I have an Adobe driver) could prove very handy indeed :)
Sep 12 '08 #5

puppydogbuddy
Expert 100+
P: 1,923
Adrian,
You are welcome.

If you already have Adobe installed, and Access's native pdf add-in does not work, try using the ShellExecute method. It may not give you everything you want, but it is easy to implement.

The following example uses ShellExecute on a button click event. You need to replace the strPdfWriterPath with the path to the Acrobat.exe on your machine.

Expand|Select|Wrap|Line Numbers
  1. Private Sub PrintPdf_Click()
  2. Dim strPdfWriterPath As String
  3. Dim strFilePath As String
  4.  
  5. 'Open File
  6. 'strPdfWriterPath is the path to the application object
  7. 'strFilePath is the path to the file you want to open
  8.  
  9. strPdfWriterPath = "C:\Program Files\Adobe\Acrobat 5.0\Acrobat\Acrobat.exe"
  10. strFilePath = Me.FilePath
  11.  
  12. Call Shell(strPdfWriterPath & " " & strFilePath, vbMaximizedFocus)
  13.  
  14. End Sub
  15.  
Also, if you have MS Access 2007, it natively supports pdf. All you need to do is launch the Access report in preview mode and store the report name in variable, for example strAccessRptName.
Then, put the following code behind a button.

Expand|Select|Wrap|Line Numbers
  1. Private Sub YourButton_Click()
  2. DoCmd.OutputTo acOutputReport, strAccessRptName, acFormatPDF, "c:PDF Reports\" & strAccessRptName & ".pdf", False
  3. End Sub
Sep 12 '08 #6

NeoPa
Expert Mod 15k+
P: 31,495
I don't want to take the thread too much off track here pDog, but, although I have Adobe drivers installed on MY PC, the databse also needs to be run from others which don't (take some of the workload off me).

For that reason I'm interested in a solution where Adobe drivers aren't required. I'm hoping the Lebans solution might be what I'm after, so I'll look into it when I get some time available.

Thanks for all your help anyway.
Sep 12 '08 #7

ADezii
Expert 5K+
P: 8,638
Adrian,
You are welcome.

If you already have Adobe installed, and Access's native pdf add-in does not work, try using the ShellExecute method. It may not give you everything you want, but it is easy to implement.

The following example uses ShellExecute on a button click event. You need to replace the strPdfWriterPath with the path to the Acrobat.exe on your machine.

Expand|Select|Wrap|Line Numbers
  1. Private Sub PrintPdf_Click()
  2. Dim strPdfWriterPath As String
  3. Dim strFilePath As String
  4.  
  5. 'Open File
  6. 'strPdfWriterPath is the path to the application object
  7. 'strFilePath is the path to the file you want to open
  8.  
  9. strPdfWriterPath = "C:\Program Files\Adobe\Acrobat 5.0\Acrobat\Acrobat.exe"
  10. strFilePath = Me.FilePath
  11.  
  12. Call Shell(strPdfWriterPath & " " & strFilePath, vbMaximizedFocus)
  13.  
  14. End Sub
  15.  
Also, if you have MS Access 2007, it natively supports pdf. All you need to do is launch the Access report in preview mode and store the report name in variable, for example strAccessRptName.
Then, put the following code behind a button.

Expand|Select|Wrap|Line Numbers
  1. Private Sub YourButton_Click()
  2. DoCmd.OutputTo acOutputReport, strAccessRptName, acFormatPDF, "c:PDF Reports\" & strAccessRptName & ".pdf", False
  3. End Sub
Hello puppydogbuddy, forgive me if I'm wrong, but but I don't think what you are describing is the ShellExecute() API Function which is used to execute a File based solely on its Extension. Below I've posted the Declaration, Wrapper Function, and a sample Call to ShellExecute():
Expand|Select|Wrap|Line Numbers
  1. Declare Function ShellExecute Lib "shell32.dll" _
  2. Alias "ShellExecuteA" (ByVal hWnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, _
  3. ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
Expand|Select|Wrap|Line Numbers
  1. 'Wrapper Function
  2. Public Function Execute_Program(ByVal strFilePath As String, _
  3.     ByVal strParms As String, ByVal strDir As String) _
  4.     As Boolean
  5.  
  6. 'run program ' <R6>
  7. Dim hwndProgram As Integer
  8. hwndProgram = ShellExecute(0, "Open", strFilePath, strParms, strDir, 3)     '3 ==> Show Maximized
  9.  
  10. 'evaluate errors (if any)
  11. Select Case (hwndProgram)
  12.   Case 0
  13.     MsgBox "Insufficent system memory or corrupt program file.", 0, "Error running " & strFilePath
  14.       Execute_Program = False
  15.         Exit Function
  16.   Case 2
  17.     MsgBox "File not found.", 0, "Error running " & strFilePath
  18.       Execute_Program = False
  19.         Exit Function
  20.   Case 3
  21.     MsgBox "Invalid path.", 0, "Error running " & strFilePath
  22.       Execute_Program = False
  23.         Exit Function
  24.   Case 5
  25.     MsgBox "Sharing or Protection Error.", 0, "Error running " & strFilePath
  26.       Execute_Program = False
  27.         Exit Function
  28.   Case 6
  29.     MsgBox "Seperate data segments are required for each task.", 0, "Error running " & strFilePath
  30.       Execute_Program = False
  31.         Exit Function
  32.   Case 8
  33.     MsgBox "Insufficient memory to run the program.", 0, "Error running " & strFilePath
  34.       Execute_Program = False
  35.         Exit Function
  36.   Case 10
  37.     MsgBox "Incorrect Windows version.", 0, "Error running " & strFilePath
  38.       Execute_Program = False
  39.         Exit Function
  40.   Case 11
  41.     MsgBox "Invalid program file.", 0, "Error running " & strFilePath
  42.       Execute_Program = False
  43.         Exit Function
  44.   Case 12
  45.     MsgBox "Program file requires a different operating system.", 0, "Error running " & strFilePath
  46.       Execute_Program = False
  47.         Exit Function
  48.   Case 13
  49.     MsgBox "Program requires MS-DOS 4.0.", 0, "Error running " & strFilePath
  50.       Execute_Program = False
  51.         Exit Function
  52.   Case 14
  53.     MsgBox "Unknown program file type.", 0, "Error running " & strFilePath
  54.       Execute_Program = False
  55.         Exit Function
  56.   Case 15
  57.     MsgBox "Windows program does not support protected memory mode.", 0, "Error running " & strFilePath
  58.       Execute_Program = False
  59.         Exit Function
  60.   Case 16
  61.     MsgBox "Invalid use of data segments when loading a second instance of a program.", 0, "Error running " & strFilePath
  62.       Execute_Program = False
  63.         Exit Function
  64.   Case 19
  65.     MsgBox "Attempt to run a compressed program file.", 0, "Error running " & strFilePath
  66.       Execute_Program = False
  67.         Exit Function
  68.   Case 20
  69.     MsgBox "Invalid dynamic link library.", 0, "Error running " & strFilePath
  70.       Execute_Program = False
  71.         Exit Function
  72.   Case 21
  73.     MsgBox "Program requires Windows 32-bit extensions.", 0, "Error running " & strFilePath
  74.       Execute_Program = False
  75.         Exit Function
  76.   Case Else
  77. End Select
  78.   'All is well if we get to this point
  79.   Execute_Program = True
  80. End Function
Expand|Select|Wrap|Line Numbers
  1. 'Sample Call
  2. Dim blnRetVal As Boolean
  3. blnRetVal = Execute_Program("C:\Graphics\MyTest.jpg", "", "")
Sep 13 '08 #8

puppydogbuddy
Expert 100+
P: 1,923
ADezii,
Sorry, I didn't mean to confuse everyone, but my intent was to refer to the MS Access Shell VBA Command, not to the Windows Shell API. See the link below for a free tutorial on how the Shell VBA command can be used to start external programs (Acrobat, Word, etc).

http://www.bluemoosetech.com/microso...osoft%20Access
Sep 13 '08 #9

P: 6
Hi PuppyDogBuddy,

Thank you very much it worked the way I exactly wanted.

Regards,
Ozair - Loving & Learning Ms Access
Sep 13 '08 #10

Post your reply

Sign in to post your reply or Sign up for a free account.