Link to home
Avatar of Senniger1
Senniger1

asked on

Access 2010 - PDF or XPS Button is Greyed Out

I have Access 2010 on a Windows 7 PC.

My understanding is the PDF functionality is built into Access 2010, but when I preview my report, my data tab didn't have the choice "PDF or XPS" so I added it to my ribbon, but it's just greyed out.

I have done a repair and even uninstalled and reinstalled Office 2010.  Still I don't have an Acrobat tab.   Also under Save & Publish, Save Object As, Database File type PDF or XPS is greyed out.

I figured out if I open Access in Safe Mode and create a new database, I have the "PDF or XPS" button on the Print Preview tab in the Data Group and it works perfectly.

The moment I exit the newly created database and open it back up (even in safe mode), I loose that function and it is greyed out again.

I've checked Add-ins, etc. and cannot figure out what is causing the issue.

Does anyone have any ideas?

Note I have Acrobat 11 installed on my PC and I'm aware Acrobat XI PDFMaker no longer supports MS Access 2010.  I even tried uninstall Acrobat 11 (just as a test) and it still doesn't correct my issue.

Thanks in advance!
SOLUTION
Avatar of Ramin
Ramin

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Avatar of Senniger1
Senniger1

ASKER

Many thanks for all the contributions!

Ramin:  I did everyone from your instructions and it didn't work.  BTW - Excellent information.

Rajul Raj:  I found this site on my own before contacted EE and nothing worked for me.  I'm looking it over again to ensure I didn't miss anything.

John Hurst:  I tried this before contacting EE, but it didn't help.

Shaun Vermaak:  I would be totally okay with printing to a PDF printer however I need this to work so my code will work.
      DoCmd.OutputTo acOutputReport, "rptCalendar_Month", acFormatPDF, "c:\TimeOff.pdf", False

   I had considering trying to print to a PDF printer and got this far, but it still requires user intervention.  I need to be able to
      print my report to PDF without user intervention.
      Set myPrinter = Application.Printer
      Set Application.Printer = Application.Printers("Adobe PDF")
      DoCmd.OpenReport "rptCalendar_Month", acViewPreview, , , acWindowNormal
      DoCmd.PrintOut acPrintAll, 1, , acHigh, 1
      Set Application.Printer = myPrinter

Any ideas?
You may need to run a Windows 7 Repair Install since nothing else above worked.  Here is a Seven Forums tutorial to assist.

http://www.sevenforums.com/tutorials/3413-repair-install.html
Yes, enumerate through the printer collection for one that contains string "PDF"
Sorry Shaun.  I don't know how to do that.  I need someone to give the code.
This part sets to any PDF printer
Sub SetToAnyPDFPrinter()
    Dim Printers() As String
    Dim N As Long
    Printers = GetPrinterFullNames()
    For N = LBound(Printers) To UBound(Printers)
        If InStr(1, Printers(N), "PDF") > 0 Then
            Set Application.Printer = Application.Printers(Printers(N))
            Exit For
        End If
    Next N
End Sub

Open in new window


Option Explicit
Private Const HKEY_CURRENT_USER As Long = &H80000001
Private Const HKCU = HKEY_CURRENT_USER
Private Const KEY_QUERY_VALUE = &H1&
Private Const ERROR_NO_MORE_ITEMS = 259&
Private Const ERROR_MORE_DATA = 234

Private Declare Function RegOpenKeyEx Lib "advapi32" _
    Alias "RegOpenKeyExA" ( _
    ByVal HKey As Long, _
    ByVal lpSubKey As String, _
    ByVal ulOptions As Long, _
    ByVal samDesired As Long, _
    phkResult As Long) As Long

Private Declare Function RegEnumValue Lib "advapi32.dll" _
    Alias "RegEnumValueA" ( _
    ByVal HKey As Long, _
    ByVal dwIndex As Long, _
    ByVal lpValueName As String, _
    lpcbValueName As Long, _
    ByVal lpReserved As Long, _
    lpType As Long, _
    lpData As Byte, _
    lpcbData As Long) As Long

Private Declare Function RegCloseKey Lib "advapi32.dll" ( _
    ByVal HKey As Long) As Long

Public Function GetPrinterFullNames() As String()
Dim Printers() As String ' array of names to be returned
Dim PNdx As Long    ' index into Printers()
Dim HKey As Long    ' registry key handle
Dim Res As Long     ' result of API calls
Dim Ndx As Long     ' index for RegEnumValue
Dim ValueName As String ' name of each value in the printer key
Dim ValueNameLen As Long    ' length of ValueName
Dim DataType As Long        ' registry value data type
Dim ValueValue() As Byte    ' byte array of registry value value
Dim ValueValueS As String   ' ValueValue converted to String
Dim CommaPos As Long        ' position of comma character in ValueValue
Dim ColonPos As Long        ' position of colon character in ValueValue
Dim M As Long               ' string index

' registry key in HCKU listing printers
Const PRINTER_KEY = "Software\Microsoft\Windows NT\CurrentVersion\Devices"

PNdx = 0
Ndx = 0
' assume printer name is less than 256 characters
ValueName = String$(256, Chr(0))
ValueNameLen = 255
' assume the port name is less than 1000 characters
ReDim ValueValue(0 To 999)
' assume there are less than 1000 printers installed
ReDim Printers(1 To 1000)

' open the key whose values enumerate installed printers
Res = RegOpenKeyEx(HKCU, PRINTER_KEY, 0&, _
    KEY_QUERY_VALUE, HKey)
' start enumeration loop of printers
Res = RegEnumValue(HKey, Ndx, ValueName, _
    ValueNameLen, 0&, DataType, ValueValue(0), 1000)
' loop until all values have been enumerated
Do Until Res = ERROR_NO_MORE_ITEMS
    M = InStr(1, ValueName, Chr(0))
    If M > 1 Then
        ' clean up the ValueName
        ValueName = Left(ValueName, M - 1)
    End If
    ' find position of a comma and colon in the port name
    CommaPos = InStr(1, ValueValue, ",")
    ColonPos = InStr(1, ValueValue, ":")
    ' ValueValue byte array to ValueValueS string
    On Error Resume Next
    ValueValueS = Mid(ValueValue, CommaPos + 1, ColonPos - CommaPos)
    On Error GoTo 0
    ' next slot in Printers
    PNdx = PNdx + 1
    Printers(PNdx) = ValueName & " on " & ValueValueS
    ' reset some variables
    ValueName = String(255, Chr(0))
    ValueNameLen = 255
    ReDim ValueValue(0 To 999)
    ValueValueS = vbNullString
    ' tell RegEnumValue to get the next registry value
    Ndx = Ndx + 1
    ' get the next printer
    Res = RegEnumValue(HKey, Ndx, ValueName, ValueNameLen, _
        0&, DataType, ValueValue(0), 1000)
    ' test for error
    If (Res <> 0) And (Res <> ERROR_MORE_DATA) Then
        Exit Do
    End If
Loop
' shrink Printers down to used size
ReDim Preserve Printers(1 To PNdx)
Res = RegCloseKey(HKey)
' Return the result array
GetPrinterFullNames = Printers
End Function

Sub SetToAnyPDFPrinter()
    Dim Printers() As String
    Dim N As Long
    Printers = GetPrinterFullNames()
    For N = LBound(Printers) To UBound(Printers)
        If InStr(1, Printers(N), "PDF") > 0 Then
            Set Application.Printer = Application.Printers(Printers(N))
            Exit For
        End If
    Next N
End Sub

Open in new window

Shaun,

Thanks so much for the code.  I only know a little about working with all this so I'm not sure I know what to do with the code.

I'm assuming all this code does is assign a printer, but again this is a little over my head.

When I run the code, I get a Run-time error '5':  Invalid procdure call or argument on the following:
   Set Application.Printer = Application.Printers(Printers(N))

Also, I'm not exactly sure how I would be applying your code to my code as my code already assigns the printer.  It just opens the report in Adobe and requires someone to manually click Save.

       Set myPrinter = Application.Printer
       Set Application.Printer = Application.Printers("Adobe PDF")
       DoCmd.OpenReport "rptCalendar_Month", acViewPreview, , , acWindowNormal
       DoCmd.PrintOut acPrintAll, 1, , acHigh, 1
       Set Application.Printer = myPrinter

Sorry for my lack on this.

Thanks!
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Thanks.
I was never assisted with code to print to the Adobe PDF printer without user intervention.  I had to come up with that on my own.