Save a Report as a pdf.

[Robeen]

Hi,

I'm trying to save a report to my Desktop as a pdf - adding a date {that I am retrieving from a query} as part of the pdf file name.
I get a
Run-time error '2501' - The OutputTo action was cancelled.
(See screenshot).

Here's the code I'm using. So far - all I've tried to do is run the function from the Visual Basic Editor.

Function SaveReport()

Dim strSQL As String
Dim strPath As String
Dim strFilename As String

'Get the ReportDate.
Dim db As DAO.Database
Dim rs As DAO.Recordset
DoCmd.SetWarnings False

Set db = CurrentDb
strSQL = "SELECT Min([VPNDate]) AS ReportDate FROM QryVPN_Forensics_SessionID"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

'Set up the Path & File names.
strPath = "C:\Documents and Settings\rsengupta\Desktop\"
strFilename = strPath & "VPN_Forensics_Report_" & CStr(rs![ReportDate]) & ".pdf"

'Save The Report {this is where the code errors}.
DoCmd.OutputTo acOutputReport, "VPN_Forensics_SessionID", acFormatPDF, strFilename, False

End Function

I've googled and tried some suggestions - but I still get this error.

I'd appreciate any help I can get.



Thanks!!

[pbaldy]

My guess is that the date portion is including slashes, which would be an invalid file name. You can check when it's in debug mode. I'd use the format function to make sure it's in the desired format. Typically I use yyyymmdd which makes it sort properly.

[Robeen]

Thanks, Paul!

Yes - I just noticed that and changed it and came back in to say I'd figured it out - when I saw your response.
Appreciate your help - as always!

I made this change in my code:

MyFilename = MyPath & "VPN_Forensics_Report_" & CStr(Format((rs![ReportDate]), "yyyy-mm-dd")) & ".pdf"

Works fine now.

[pbaldy]

No problem. By the way, the CStr() is unnecessary, as the Format() function returns a string anyway. It's not hurting anything, but it's not doing anything either.

[Robeen]

Thanks! I'll take that out.