Print Page | Close Window

Microsoft Access

Printed From: www.exp-systems.com
Category: PDF reDirect
Forum Name: Programming
Forum Discription: VBA and Batch Tools to control PDF reDirect Pro
URL: http://www.exp-systems.com/Forum_exp/forum_posts.asp?TID=125
Printed Date: 22 Nov 24 at 7:52AM


Topic: Microsoft Access
Posted By: jaydeel
Subject: Microsoft Access
Date Posted: 12 Sep 05 at 4:14AM
If I set my default printer as "Batch PDF - VBA", can I use the PDF reDirect VBA class module to print Micorsoft Access reports from VBA? If yes, can you provide sample code? I'm hoping that I can batch print a report by progammatically changing the report's record source.


Replies:
Posted By: Michel_K17
Date Posted: 12 Sep 05 at 10:35PM
   VBA for Access is a little bit harder than VBA for Word or Excel, because it does not allow you to specify a printer of your choice (in your case, the "Batch PDF - VBA" printer). Unfortunately, it only prints to the default printer. Therefore, what you need to do is:

  1. Record the current Default Printer,
  2. Set the Default Printer to "Batch PDF - VBA"
  3. Print the Report, and finally
  4. Restore the original default printer.


   I created the VBA code to do just that by playing around in the Northwind sample database that comes with MS Access, and it works quite well. This is how you go about it (requires a few Windows API calls, but nothing too scary).

  1. Import my VBA Class Module (clsPrint_PDF_Pro.cls) into your database
  2. Add an "Export to PDF" command button to your form
  3. Call the new button: cmdExport2PDF
  4. Add the code given below to your form.
  5. Modify the name of the report in the code from "Your Report Here" to whatever your report is called.



' Windows API Declarations
Private Declare Function GetProfileString Lib "Kernel32" Alias "GetProfileStringA" (ByVal lpAppName As String, ByVal lpKeyName As String, ByVal lpDefault As String, ByVal lpReturnedString As String, ByVal nSize As Long) As Long
Private Declare Function WriteProfileString Lib "Kernel32" Alias "WriteProfileStringA" (ByVal lpszSection As String, ByVal lpszKeyName As String, ByVal lpszString As String) As Long
Private Declare Function SendMessageByString Lib "user32" Alias "SendMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As String) As Long

' Code to Convert the Report to a PDF
'------------------------------------
Private Sub cmdExport2PDF_Click()
    
    ' Get the current Default Printer
    Dim OriginalPrinter As String
    OriginalPrinter = GetDefaultPrinter()
    
    ' Now set my Batch PDF Printer as the Default Printer (temporarily)
    ' Note: the driver might be "PDF reDirect v2" or "PDF reDirect Pro" but code below
    '       is most likely to be correct
    If SetDefaultPrinter("Batch PDF - VBA,PDF reDirect Pro v2,PDF_REDIRECT_PORT:") Then
    
        ' Set my output PDF filename
        Dim oPDF As New clsPrint_PDF_Pro
        oPDF.SetOutput "Batch PDF - VBA", "C:\Temp", "Access_Report.pdf"
       
        ' Print report to the PDF, then close the report
        DoCmd.OpenReport "Your Report Here", acViewNormal
        DoCmd.Close acForm
       
        ' Done. Recover memory
        Set oPDF = Nothing
       
        ' Restore the original default printer
        SetDefaultPrinter OriginalPrinter
       
    End If
    
End Sub


' SetDefaultPrinter Function
Private Function SetDefaultPrinter(PrinterName_DriverName_PrinterPort As String) As Boolean
    WriteProfileString "windows", "device", PrinterName_DriverName_PrinterPort
    SetDefaultPrinter = CBool(SendMessageByString(&HFFFF&, &H1A, 0&, "windows"))
End Function

' GetDefaultPrinter Function
Private Function GetDefaultPrinter() As String
    GetDefaultPrinter = Space$(1024)
    GetDefaultPrinter = Trim$(Left$(GetDefaultPrinter, GetProfileString("windows", "device", "", GetDefaultPrinter, 1024)))
End Function



Posted By: jaydeel
Date Posted: 13 Sep 05 at 3:04AM
This is exactly what I needed. It's PERFECT!
Thanks!

Now that I can print a single pdf on demand, I'd like to have a second command button to batch print this same report for every entry listed in a combobox control on the form. My control is named "cboPOC" and it has a rowsource named "qry_Sort_POCs". I assume I'd need to transfer each item from a recordset based on this control, then programmatically execute the button cmdExport2PDF above. Would it be possible to suggest the source code for this as well?

Posted By: Michel_K17
Date Posted: 13 Sep 05 at 10:16PM
   You are welcome.

   About your second request: you are (unfortunately) pushing my envelope a bit as I have done very little database programming with MS Access. The little that I have done, 3 years ago, was with MySql (www.mysql.com).

   What you are suggesting seems to make sense: you should be able to run through each entry of the ComboBox with something like this:

   For i = 0 to cboPOC.ListCount
       ' Recover the content of the next entry
       Entry = cboPOC.List(i)
       ' Print the report based on the item recovered
       (gets fuzzy here)
   Next i

   It's difficult to get more specific as I do not know exactly what you are recovering via the rowsource, and how your database is setup. Does this combobox contain a "where condition" you can feed directly to the DoCmd.OpenReport command?

   If you get really stuck, have you considered using www.rentacoder.com? You can usually get small problems like this sorted out for $5.

   Cheers!

Posted By: cosmo
Date Posted: 28 Sep 05 at 3:37AM

Hi Michel,

 

I have snet this to your email as well, nice clean code, any change of getting clsPrint_PDF_Pro.cls?

 

Would be much appreciated.

 

 

Posted By: Michel_K17
Date Posted: 28 Sep 05 at 4:13AM
Hi Cosmo,

   You can download the class [ http://exp-systems.com/PDFreDirect/Download/PDF_Class4VB.zip - here ].

   The zip file contains an Excel demo. Please keep in mind that PDF reDirect Pro must be installed for the code to work.

   Cheers!

Posted By: cosmo
Date Posted: 28 Sep 05 at 5:58AM
Legend! thanks for that.

Posted By: Michel_K17
Date Posted: 09 Feb 06 at 12:50AM
   I received a report today from someone who was having a problem printing his Access report. The first few records were being repeated/duplicated on the second page.

   Although I did not figure out why this problem occured, I was able to fix the problem by generating a "Preview" first before printing. Therefore, the old code:

      DoCmd.OpenReport "MyReport", acViewNormal   ' Print the Report

became like this:

      DoCmd.OpenReport "MyReport", acViewPreview ' Generate a Preview First
      DoCmd.OpenReport "MyReport", acViewNormal   ' Print the Report



Posted By: Spyryl
Date Posted: 16 Apr 06 at 11:08AM
I have just tried the code you supplied for printing reports and I get one of two results. If I just let it run then Access stops responding and has to be terminated via the task manager OR if I step through it then the main pdf-redirect window opens up.

What I need to achieve is for each record in a table a report has to be printed (to pdf), I then have to send the newly created pdf file and then take a copy to a different directory (the save and copy directories can change at time of printing).

Thanks for your assistance with this.

Posted By: Michel_K17
Date Posted: 17 Apr 06 at 12:29AM
Hi there,

   The code above, by itself, is insufficient. You need the VB class that interfaces with PDF reDirect Pro. It's available [ http://www.exp-systems.com/forum_exp/forum_posts.asp?TID=39&PN=1 - here ].

   Finally, you need to use the "Professional" version of PDF reDirect, and you need to have setup a "batch printer" (easily done within PDF reDirect Pro, and only has to be done once).

   To answer your second question: the VB Class code will show you how to save a PDF to the directory and with the filename of your choosing.

   Cheers!

Michel

Posted By: donkur2
Date Posted: 01 Mar 07 at 3:57PM
How are you importing the module? I am using Access 97 and it seems the only way I can import it is to make a new module, then copy and paste the code. This however, does not work as when the button is clicked, I get a compile error: "User-defined type not allowed" at the "Dim oPDF As New clsPrint_PDF_Pro" line. I really need to make a pdf from a report, so that I can merge it with an existing pdf, so that these two documents (which should now be merged into one), can duplex. Does this sound possible?

Posted By: Michel_K17
Date Posted: 01 Mar 07 at 9:35PM
The "VB class" is no longer supported. I have a new "ActiveX component" which is much more powerful/useful and I include an Access demo as well as demos for Word and Excel.

 The last working component was for beta v2.1.908 which you can use with the Beta v2.1.908 of PDF reDirect Pro. All of the downloads are available on the forum at the post [ http://www.exp-systems.com/Forum_exp/forum_posts.asp?TID=51&FID=10&PR=3 - here ].

    In addition, you need to know that an updated beta/component will be available on Monday. It will be v2.1.911, so you should come back and check then. That version will be "very close" to the final release version.

    Cheers!




-------------
Michel Korwin-Szymanowski
EXP Systems LLC


Print Page | Close Window