Why does emailing a PDF work, but saving a PDF doesn't?

[cricketbird]

I'm trying to figure out why I can email a report as a PDF, but not save it as a PDF using the code below?

When I save, I get the dreaded error 2501.

The searching I did for this said to uninstall and reinstall printers (I've done that - seems to have no effect), or took issue with filtering the report with a where clause (but if that was an issue, why does it email a PDF just fine?).

I'd just like to be able to save a report as a PDF to a specified location. I'd appreciate any advice or suggestions. I'm fairly new to VBA.

Thank you!
CB
Developing in: Access 2010; Win7
For use in a mixed 2007/2010 XP/7 environment

Private Sub EmailDietCardBtn_Click()

On Error GoTo MyErrorHandler
Me.Refresh

Dim stReport As String
Dim stWhere As String
Dim stSubject As String
Dim stEmailMessage As String
Dim stCaption As String
Dim myPath As String

stEmailMessage = "Please see the attached yada yada yada"
stSubject = "Updates to Diet #" & Me.DIETID '
stReport = "DIET CARD"
stCaption = Me.PATIENT & " " & Me.NOTEID & " #" & Me.DIETID & " " & Format(Now(), "yyyy-mm-dd") & " " & Format(Now(), "hh-nn")
myPath = "J:\Diet Cards\" & stCaption
stWhere = "DietID = " & Me.DIETID '


DoCmd.OpenReport stReport, acViewPreview, "", stWhere, acWindowNormal, ""
Reports![DIET CARD].Caption = stCaption  'Renames The report and add ID fields

'Not all users have Access 2010 so...

If Access.Version > 13 Then

'EMAILING THE PDF WORKS
DoCmd.SendObject acSendReport, stReport, "PDFFormat(*.pdf)", , , , stSubject, stEmailMessage, True, ""

'BUT SAVING IT TO A SPECIFIED LOCATION DOES NOT
DoCmd.OutputTo acOutputReport, stReport, "PDFFormat(*.pdf)", myPath & ".pdf", False, "", , acExportQualityPrint

Else

DoCmd.SendObject acSendReport, stReport, acFormatXPS, , , , stSubject, stEmailMessage, True, ""
DoCmd.OutputTo acOutputReport, , acFormatXPS, myPath & ".xps", False
End If


Exit Sub

MyErrorHandler:
If Err.Number = 2501 Then
MsgBox "Oops - Error 2501 yet again", vbOKOnly
Exit Sub
Else
MsgBox Err.Number & ":" & Err.DESCRIPTION, vbOKOnly
End If

End Sub

 

[michaeljryan78]

DoCmd.OutputTo acOutputReport, , acFormatXPS, myPath & ".xps", False

should be

DoCmd.OutputTo acOutputReport,stReport , acFormatPDF, myPath & ".pdf", False

That should work for both versions.

 

[cricketbird]

Thanks, but unfortunately the 2007 folks cannot export to PDF. You have to install a service pack, and our IT folks will not do this because they are slowly rolling out 2010.

For whatever antiquated reasons, people regularly use the .xps format internally around here. Strange, but true. I suppose its a hold-over from when Office was less PDF-friendly.

At any rate, my database doesn't save to either XPS or PDF, even though it emails XPS and PDF just fine.

Any other suggestions?

Thanks,
CB

 

[KCLNick]

I think you missed the point of the reply you were given by michaeljryan78.

Both your saving as pdf or xps lines of code contain errors.

The pdf line should use acFormatPDF, not PDFFormat(*.pdf)"

Not used xps but the line looks to omit the stReport object name.

 

[pr2-eugin]

KCLNick, Welcome to AWF..

PDFFormat(*.pdf) and acFormatPDF are interchangeable.. That is either of the two can be used.. Access recognizes them both to be the same.. the PDFFormat(*.pdf) is passed as a String, acFormatPDF is a constant..

Regarding your error cricketbird, I would suggest you check if the directory exists before saving it to the location..

 

[cricketbird]

Thanks KCLNick and Paul. Here is my updated code that incorporates your suggestions, but still does not save the file, and returns error 2501.

CB

myPath = "J:\Diet Cards\" 'I removed stCaption from here so that I could check the directory by itself

'This returns a message box saying that "does exist", but only if the final backslash is in there.  Also, I have write permissions to that directory and have saved other files there just to be sure with no problems.

If Len(Dir(myPath)) = 0 Then
   MsgBox myPath & "does not exist."
Else
   MsgBox myPath & " does exist."
End If

stWhere = "DietID = " & Me.DIETID '
DoCmd.OpenReport stReport, acViewPreview, "", stWhere, acWindowNormal, ""
Reports![DIET CARD].Caption = stCaption  'Renames The report and adds ID fields

'Not all users have Access 2010 so...

If Access.Version > 13 Then

'Emailing still works
DoCmd.SendObject acSendReport, stReport, acFormatPDF, , , , stSubject, stEmailMessage, True, ""

'But saving still does not
DoCmd.OutputTo acOutputReport, stReport, acFormatPDF, myPath & stCaption & ".pdf", False, "", , acExportQualityPrint

Else

'Emailing XPS still works
DoCmd.SendObject acSendReport, stReport, acFormatXPS, , , , stSubject, stEmailMessage, True, ""

'And saving XPS still does not
DoCmd.OutputTo acOutputReport, stReport, acFormatXPS, myPath & stCaption & ".xps", False
End If

 

[michaeljryan78]

Try removing the quotes in the Template file parameter:

DoCmd.OutputTo acOutputReport, stReport, acFormatPDF, myPath & stCaption & ".pdf", False, , , acExportQualityPrint

 

[michaeljryan78]

have a look at this: http://www.access-programmers.co.uk/forums/showthread.php?t=204524

Then this: http://www.access-programmers.co.uk/forums/showthread.php?t=204478

 

[magster06]

I am not sure if this will help, but I borrowed your code (I hope this is ok) to email and save my reports to pdf, but of course I was having the same issues (error 2501 and would not save).

I had my file path as such:

myFilePath = "C:\Users\arr\Documents\Allegation Reports\"

When I changed my file path to :

myFilePath = "C:\Users\arr\Documents\"

now the code saves my file, but of course not in a folder. I guess if I created my folder in advance, then it probably would work also.

 

[gemma-the-husky]

OP

maybe mypath in your system does not have a trailing "\" character. i would check that.

 

[cricketbird]

Thanks michaelryan78! That was the kicker. No idea where I got those quotes from.

The final working code now reads:

If Access.Version > 13 Then

DoCmd.SendObject acSendReport, stReport, acFormatPDF, , , , stSubject, stEmailMessage, True, ""
DoCmd.OutputTo acOutputReport, stReport, acFormatPDF, myPath & stCaption & ".pdf", False, , , acExportQualityPrint

Else

DoCmd.SendObject acSendReport, stReport, acFormatXPS, , , , stSubject, stEmailMessage, True, ""
DoCmd.OutputTo acOutputReport, , acFormatXPS, myPath & ".xps", False
End If

 

[michaeljryan78]

Glad you get 'er up and runnin. Keep it up!