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

[OzairKhalid]

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.

[NeoPa]

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.

[OzairKhalid]

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

[puppydogbuddy]

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

[NeoPa]

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 :)

[puppydogbuddy]

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.

Private Sub PrintPdf_Click()
Dim strPdfWriterPath As String
Dim strFilePath As String
 
'Open File
'strPdfWriterPath is the path to the application object
'strFilePath is the path to the file you want to open
 
strPdfWriterPath = "C:\Program Files\Adobe\Acrobat 5.0\Acrobat\Acrobat.exe"
strFilePath = Me.FilePath
 
Call Shell(strPdfWriterPath & " " & strFilePath, vbMaximizedFocus)
 
End Sub
 

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.

Private Sub YourButton_Click()
DoCmd.OutputTo acOutputReport, strAccessRptName, acFormatPDF, "c:PDF Reports\" & strAccessRptName & ".pdf", False
End Sub

[NeoPa]

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.

[ADezii]

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.

Private Sub PrintPdf_Click()
Dim strPdfWriterPath As String
Dim strFilePath As String
 
'Open File
'strPdfWriterPath is the path to the application object
'strFilePath is the path to the file you want to open
 
strPdfWriterPath = "C:\Program Files\Adobe\Acrobat 5.0\Acrobat\Acrobat.exe"
strFilePath = Me.FilePath
 
Call Shell(strPdfWriterPath & " " & strFilePath, vbMaximizedFocus)
 
End Sub
 

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.

Private Sub YourButton_Click()
DoCmd.OutputTo acOutputReport, strAccessRptName, acFormatPDF, "c:PDF Reports\" & strAccessRptName & ".pdf", False
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():

Declare Function ShellExecute Lib "shell32.dll" _
Alias "ShellExecuteA" (ByVal hWnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, _
ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

'Wrapper Function
Public Function Execute_Program(ByVal strFilePath As String, _
    ByVal strParms As String, ByVal strDir As String) _
    As Boolean
 
'run program ' <R6>
Dim hwndProgram As Integer
hwndProgram = ShellExecute(0, "Open", strFilePath, strParms, strDir, 3)     '3 ==> Show Maximized
 
'evaluate errors (if any)
Select Case (hwndProgram)
  Case 0
    MsgBox "Insufficent system memory or corrupt program file.", 0, "Error running " & strFilePath
      Execute_Program = False
        Exit Function
  Case 2
    MsgBox "File not found.", 0, "Error running " & strFilePath
      Execute_Program = False
        Exit Function
  Case 3
    MsgBox "Invalid path.", 0, "Error running " & strFilePath
      Execute_Program = False
        Exit Function
  Case 5
    MsgBox "Sharing or Protection Error.", 0, "Error running " & strFilePath
      Execute_Program = False
        Exit Function
  Case 6
    MsgBox "Seperate data segments are required for each task.", 0, "Error running " & strFilePath
      Execute_Program = False
        Exit Function
  Case 8
    MsgBox "Insufficient memory to run the program.", 0, "Error running " & strFilePath
      Execute_Program = False
        Exit Function
  Case 10
    MsgBox "Incorrect Windows version.", 0, "Error running " & strFilePath
      Execute_Program = False
        Exit Function
  Case 11
    MsgBox "Invalid program file.", 0, "Error running " & strFilePath
      Execute_Program = False
        Exit Function
  Case 12
    MsgBox "Program file requires a different operating system.", 0, "Error running " & strFilePath
      Execute_Program = False
        Exit Function
  Case 13
    MsgBox "Program requires MS-DOS 4.0.", 0, "Error running " & strFilePath
      Execute_Program = False
        Exit Function
  Case 14
    MsgBox "Unknown program file type.", 0, "Error running " & strFilePath
      Execute_Program = False
        Exit Function
  Case 15
    MsgBox "Windows program does not support protected memory mode.", 0, "Error running " & strFilePath
      Execute_Program = False
        Exit Function
  Case 16
    MsgBox "Invalid use of data segments when loading a second instance of a program.", 0, "Error running " & strFilePath
      Execute_Program = False
        Exit Function
  Case 19
    MsgBox "Attempt to run a compressed program file.", 0, "Error running " & strFilePath
      Execute_Program = False
        Exit Function
  Case 20
    MsgBox "Invalid dynamic link library.", 0, "Error running " & strFilePath
      Execute_Program = False
        Exit Function
  Case 21
    MsgBox "Program requires Windows 32-bit extensions.", 0, "Error running " & strFilePath
      Execute_Program = False
        Exit Function
  Case Else
End Select
  'All is well if we get to this point
  Execute_Program = True
End Function

'Sample Call
Dim blnRetVal As Boolean
blnRetVal = Execute_Program("C:\Graphics\MyTest.jpg", "", "")

[puppydogbuddy]

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

[OzairKhalid]

Hi PuppyDogBuddy,

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

Regards,
Ozair - Loving & Learning Ms Access