Producing PDFs from Access

[9798]

I am attempting to produce pdfs from an access db where the code is correct and should work. However, when I run the pdf macro, the output file titles may have one to several characters cut off from the beginning of the title that it should be. Also it doesn't output the file that it specifies in the code. Does anyone know how this can be fixed? Is there something wrong with my Access db settings or my Acrobat distiller?
thanks
9798

 

[M8KWR]

hi i print from access to acrobat all the time with no issues, do you want me to give you my code that i use. I use mine through PCFWriter not the distiller.. I personal do not like the distiller, but that is me.

I can specify the filename, the location and it works every time with no issues.

Let me know.

 

[BSman]

I'd also be interested in your code. We're using PDF995 rather than Acrobat. The suite includes some very powerful functions (combining PDFs, conversion to other formats, etc.) at a very low price (free for single users).

Currently, when my users need to create a PDF they will usually preview the report and then print through the File/Print menu option, which brings up the printer dialog box, thus enabling them to select the "printer" that creates the PDF file.

Bob S.

 

[9798]

Hi. thks M8KWR and BSman. I can change to use PDFWriter and not Distiller and willing to give your code a try M8KWR. I am not very good with VBA coding in Access. i prefer not to preview the report because i have over 100 reports to go through! thks for all your help
9798

 

[M8KWR]

This is really easy, first step create a new module and paste all the code below-

 Public Const REG_SZ As Long = 1
   Public Const REG_DWORD As Long = 4

   Public Const HKEY_CLASSES_ROOT = &H80000000
   Public Const HKEY_CURRENT_USER = &H80000001
   Public Const HKEY_LOCAL_MACHINE = &H80000002
   Public Const HKEY_USERS = &H80000003

   Public Const ERROR_NONE = 0
   Public Const ERROR_BADDB = 1
   Public Const ERROR_BADKEY = 2
   Public Const ERROR_CANTOPEN = 3
   Public Const ERROR_CANTREAD = 4
   Public Const ERROR_CANTWRITE = 5
   Public Const ERROR_OUTOFMEMORY = 6
   Public Const ERROR_ARENA_TRASHED = 7
   Public Const ERROR_ACCESS_DENIED = 8
   Public Const ERROR_INVALID_PARAMETERS = 87
   Public Const ERROR_NO_MORE_ITEMS = 259

   Public Const KEY_QUERY_VALUE = &H1
   Public Const KEY_SET_VALUE = &H2
   Public Const KEY_ALL_ACCESS = &H3F

   Public Const REG_OPTION_NON_VOLATILE = 0

   Declare Function RegCloseKey Lib "advapi32.dll" _
   (ByVal hKey As Long) As Long
   Declare Function RegCreateKeyEx Lib "advapi32.dll" Alias _
   "RegCreateKeyExA" (ByVal hKey As Long, ByVal lpSubKey As String, _
   ByVal Reserved As Long, ByVal lpClass As String, ByVal dwOptions _
   As Long, ByVal samDesired As Long, ByVal lpSecurityAttributes _
   As Long, phkResult As Long, lpdwDisposition As Long) As Long
   Declare Function RegOpenKeyEx Lib "advapi32.dll" Alias _
   "RegOpenKeyExA" (ByVal hKey As Long, ByVal lpSubKey As String, _
   ByVal ulOptions As Long, ByVal samDesired As Long, phkResult As _
   Long) As Long
   Declare Function RegQueryValueExString Lib "advapi32.dll" Alias _
   "RegQueryValueExA" (ByVal hKey As Long, ByVal lpValueName As _
   String, ByVal lpReserved As Long, lpType As Long, ByVal lpData _
   As String, lpcbData As Long) As Long
   Declare Function RegQueryValueExLong Lib "advapi32.dll" Alias _
   "RegQueryValueExA" (ByVal hKey As Long, ByVal lpValueName As _
   String, ByVal lpReserved As Long, lpType As Long, lpData As _
   Long, lpcbData As Long) As Long
   Declare Function RegQueryValueExNULL Lib "advapi32.dll" Alias _
   "RegQueryValueExA" (ByVal hKey As Long, ByVal lpValueName As _
   String, ByVal lpReserved As Long, lpType As Long, ByVal lpData _
   As Long, lpcbData As Long) As Long
   Declare Function RegSetValueExString Lib "advapi32.dll" Alias _
   "RegSetValueExA" (ByVal hKey As Long, ByVal lpValueName As String, _
   ByVal Reserved As Long, ByVal dwType As Long, ByVal lpValue As _
   String, ByVal cbData As Long) As Long
   Declare Function RegSetValueExLong Lib "advapi32.dll" Alias _
   "RegSetValueExA" (ByVal hKey As Long, ByVal lpValueName As String, _
   ByVal Reserved As Long, ByVal dwType As Long, lpValue As Long, _
   ByVal cbData As Long) As Long

'**********************************************************

Public Sub SaveReportAsPDF(strReportName As String, strPath As String)

    Dim strOldDefault As String
    
    strOldDefault = QueryKey("Software\Microsoft\Windows NT\CurrentVersion\Windows", "Device")
    
    SetKeyValue "Software\Microsoft\Windows NT\CurrentVersion\Windows", "Device", "Acrobat PDFWriter", REG_SZ
    
    SetKeyValue "Software\Adobe\Acrobat PDFWriter", "PDFFilename", strPath, REG_SZ
    
    SetKeyValue "Software\Adobe\Acrobat PDFWriter", "bExecViewer", 0, REG_SZ

    DoCmd.OpenReport strReportName
    
    SetKeyValue "Software\Microsoft\Windows NT\CurrentVersion\Windows", "Device", strOldDefault, REG_SZ

End Sub

   Public Function SetValueEx(ByVal hKey As Long, sValueName As String, _
   lType As Long, vValue As Variant) As Long
       Dim lValue As Long
       Dim sValue As String
       Select Case lType
           Case REG_SZ
               sValue = vValue & Chr$(0)
               SetValueEx = RegSetValueExString(hKey, sValueName, 0&, _
                                              lType, sValue, Len(sValue))
           Case REG_DWORD
               lValue = vValue
               SetValueEx = RegSetValueExLong(hKey, sValueName, 0&, _
   lType, lValue, 4)
           End Select
   End Function

   Function QueryValueEx(ByVal lhKey As Long, ByVal szValueName As _
   String, vValue As Variant) As Long
       Dim cch As Long
       Dim lrc As Long
       Dim lType As Long
       Dim lValue As Long
       Dim sValue As String

       On Error GoTo QueryValueExError

       ' Determine the size and type of data to be read
       lrc = RegQueryValueExNULL(lhKey, szValueName, 0&, lType, 0&, cch)
       If lrc <> ERROR_NONE Then Error 5

       Select Case lType
           ' For strings
           Case REG_SZ:
               sValue = String(cch, 0)

   lrc = RegQueryValueExString(lhKey, szValueName, 0&, lType, _
   sValue, cch)
               If lrc = ERROR_NONE Then
                   vValue = Left$(sValue, cch - 1)
               Else
                   vValue = Empty
               End If
           ' For DWORDS
           Case REG_DWORD:
   lrc = RegQueryValueExLong(lhKey, szValueName, 0&, lType, _
   lValue, cch)
               If lrc = ERROR_NONE Then vValue = lValue
           Case Else
               'all other data types not supported
               lrc = -1
       End Select

QueryValueExExit:
       QueryValueEx = lrc
       Exit Function

QueryValueExError:
       Resume QueryValueExExit
   End Function

Public Function CreateNewKey(sNewKeyName As String, lPredefinedKey As Long)

    Dim hNewKey As Long         ' Handle to the new key
    Dim lRetVal As Long         ' Result of the RegCreateKeyEx function
    
    lRetVal = RegCreateKeyEx(lPredefinedKey, sNewKeyName, 0&, vbNullString, REG_OPTION_NON_VOLATILE, _
        KEY_ALL_ACCESS, 0&, hNewKey, lRetVal)
        
    RegCloseKey (hNewKey)

End Function

Public Function SetKeyValue(sKeyName As String, sValueName As String, vValueSetting As Variant, lValueType As Long)

    Dim lRetVal As Long         ' Result of the SetValueEx function
    Dim hKey As Long            ' Handle of open key
    
    ' Open the specified key
    lRetVal = RegOpenKeyEx(HKEY_CURRENT_USER, sKeyName, 0, KEY_SET_VALUE, hKey)
    
    lRetVal = SetValueEx(hKey, sValueName, lValueType, vValueSetting)
    
    RegCloseKey (hKey)

End Function

Public Function QueryKey(sKeyName As String, sValueName As String)

    Dim lRetVal As Long         ' Result of the API functions
    Dim hKey As Long            ' Handle of opened key
    Dim vValue As Variant       ' Setting of queried value
    
    lRetVal = RegOpenKeyEx(HKEY_CURRENT_USER, sKeyName, 0, KEY_QUERY_VALUE, hKey)
    
    lRetVal = QueryValueEx(hKey, sValueName, vValue)
    
    QueryKey = vValue
    
    RegCloseKey (hKey)

End Function

Now if you create a button on a form, in the on_click procedure all you need to do is to put in the following code-

Dim ReportName, ReportFile

ReportName = "Report1" 'this is where you would enter the exact name of the report as it is spelt in access

ReportFile = "C:\Report1.pdf" this is where you would enter the path and file name of the pdf

Call SaveReportAsPDF(ReportName, ReportFile)

I hope this is ok, and is quite easy for you. 9798 - what you could do is do a loop through all the report, i know you can call the reports you have saved into a form, and keep stepping through each record (or report name) and then keep sending this to a pdf file. I do not know that code to call the report to a form, but someone on here must - but if you need help designing the code to step through etc then please let me know.

BSman - unsure if this would work with PDF995 i have not tried it myself, and unsure if there is a free version of just PDFWriter.

Any issues at all then please let me know.

 

[BSman]

M8KWR,

Thanks for the code. I will post something here if I'm able to get it working with PDF995. This should work well with a module I wrote some time ago that has been very useful. You call it to run a report and it asks the user if the user wants to preview the report. If no, then it will either print the report or (depending on a value you pass to it) it will ask if you want to save as a file (.rtf format). I suspect that I can easily add the ability to print as a pdf assuming (for our situation) that the code can be changed to work correctly with PDF995.

If anyone wants the code for this function (which I call PrintOrPreview), I can post it. It's been working for about 4 years with many different users and applications, and the only complaint I've had is when someone asks for a report to be sent to a file and the criteria are not used to limit the data. Of course, that is an Access limitation because when sending to a file you can't include criteria (like you can when printing or previewing a report).

Thanks again.
Bob S.