Active TopicsActive Topics  Display List of Forum MembersMemberlist  Search The ForumSearch  HelpHelp
  RegisterRegister  LoginLogin
Programming
 EXP Systems Forum : PDF reDirect : Programming
Topic: Microsoft Access Post Reply Post New Topic
Author Message
jaydeel
Newbie
Newbie


Joined: 12 Sep 05
Location: United States
Posts: 2
Quote jaydeel Replybullet Topic: Microsoft Access
    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.
IP IP Logged
Michel_K17
Moderator Group
Moderator Group
Avatar
Forum Administrator

Joined: 25 Jan 03
Posts: 1666
Quote Michel_K17 Replybullet 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


IP IP Logged
jaydeel
Newbie
Newbie


Joined: 12 Sep 05
Location: United States
Posts: 2
Quote jaydeel Replybullet 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?
IP IP Logged
Michel_K17
Moderator Group
Moderator Group
Avatar
Forum Administrator

Joined: 25 Jan 03
Posts: 1666
Quote Michel_K17 Replybullet 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!
IP IP Logged
cosmo
Newbie
Newbie


Joined: 28 Sep 05
Location: New Zealand
Posts: 2
Quote cosmo Replybullet 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.

 

 

IP IP Logged
Michel_K17
Moderator Group
Moderator Group
Avatar
Forum Administrator

Joined: 25 Jan 03
Posts: 1666
Quote Michel_K17 Replybullet Posted: 28 Sep 05 at 4:13AM
Hi Cosmo,

   You can download the class [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!
IP IP Logged
cosmo
Newbie
Newbie


Joined: 28 Sep 05
Location: New Zealand
Posts: 2
Quote cosmo Replybullet Posted: 28 Sep 05 at 5:58AM
Legend! thanks for that.
IP IP Logged
Michel_K17
Moderator Group
Moderator Group
Avatar
Forum Administrator

Joined: 25 Jan 03
Posts: 1666
Quote Michel_K17 Replybullet 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


IP IP Logged
Spyryl
Newbie
Newbie
Avatar

Joined: 16 Apr 06
Location: Australia
Posts: 6
Quote Spyryl Replybullet 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.
IP IP Logged
Michel_K17
Moderator Group
Moderator Group
Avatar
Forum Administrator

Joined: 25 Jan 03
Posts: 1666
Quote Michel_K17 Replybullet 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 [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
IP IP Logged
donkur2
Newbie
Newbie
Avatar

Joined: 01 Mar 07
Location: Canada
Posts: 3
Quote donkur2 Replybullet 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?
IP IP Logged
Michel_K17
Moderator Group
Moderator Group
Avatar
Forum Administrator

Joined: 25 Jan 03
Posts: 1666
Quote Michel_K17 Replybullet 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 [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
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum