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

   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!

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!

   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

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

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!