DoCmd.OutputTo not working in MS Access 2010 VBA

Question

I'm having a bit of trouble with VBA in MS Access 2010. My code is attempting to create a new report for each unique Settlement No. I'm then exporting that report to a folder that's created with today's date and the PDF file is assigned a name with the Settlement No.

In the code below, I keep getting an error in the line with the DoCmd.OutputTo method.

Public Function exporttopdf()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim MyFileName As String
Dim mypath As String
Dim temp As String

mypath = "S:\Settlement Reports\" & Format(Date, "mm-dd-yyyy") & "\"

Set db = CurrentDb()

Set rs = CurrentDb.OpenRecordset("SELECT dbs_eff_date, batch_id_r1, jrnl_name, ledger, entity_id_s1, account_s2, intercompany_s6, trans_amt, dbs_description, icb_name, [Settlement No] FROM [Today's Settled Jrnls]", dbOpenDynaset)

Do While Not rs.EOF

temp = rs("[Settlement No]")
MyFileName = rs("[Settlement No]") & ".PDF"

DoCmd.OpenReport "Settlement Report", acViewReport, , "[Settlement No]='" & temp & "'"
DoCmd.OutputTo acOutputReport, "", acFormatPDF, mypath & MyFileName
DoCmd.Close acReport, "Settlement Report"
rs.MoveNext

Loop

Set rs = Nothing
Set db = Nothing

End Function

If I replace mypath & MyFileName with a hard-coded file path and file name, the macro works so I'm thinking that's where my error is but I can't get it to work correctly.

Any ideas?

Thanks!

Edit:

Here's the specific error:

Run-time error '2501': The OutputTo action was canceled.

Answer 1

You will want to check to make sure the folder exists first, and create it if it doesn't exist. Like this:

If Dir(mypath, vbDirectory) = "" Then MkDir mypath