DoCmd.OutputTo - Formatting Title of Report

I am creating a DoCmd.OutputTo for an Access report in a PDF format.  I am having trouble with the syntax for the title.

Here is the code I have:

Private Sub Print_Transfer_Report_Click()

Dim vFrom As String
Dim vTo As String

vFrom = Format([Forms]![FrmTransfRpt]![txtFrom], "mm/dd/yy")
vTo = Format([Forms]![FrmTransfRpt]![TxtTo], "mm/dd/yy")
DoCmd.OutputTo acReport, "rpt_TransferReport", "PDF Format(*.pdf)", _
"\\pmcfs\groups\HR\HR Extractions\TransferRpts\Employee Transfers From_" _
& vFrom & "_To_" & " " & ".PDF", False, 0

DoCmd.Close acReport, "rpt_TransferReport"
End Sub


I want to add the input the From and To date from the form "FrmTransfrRpt".  The fields are text boxes with a short date format, txtFrom and txtTo.

When I type in the code, the Access program overwrites the the txtTo field name with TxtTo.

I have tried to save the values to a temporary variable but that does not work either.

If I change the areas for the dates to a " ", the report runs.

Can someone help with the proper syntax?

Thanks

Glen
GPSPOWAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Helen FeddemaCommented:
The outputformat argument is acformatPDF.  Also, I recommend saving the proposed save name to a String variable and inspecting it in the Immediate Window with a Debug.Print statement.  If you want a date in the save name, make sure you use a format that doesn't use slashes, as they aren't allowed in file names.  Format(dteTest, "m-d-yyyy") would work.

Public Sub ExportReportToPDF()
'Created by Helen Feddema 12-Jul-2013
'Last modified by Helen Feddema 12-Jul-2013

On Error GoTo ErrorHandler

   Dim appOutlook As New Outlook.Application
   Dim strReport As String
   Dim strSavePath As String
   Dim strReportFile As String
   Dim itm As Outlook.MailItem
   Dim strEmail As String
   
   strReport = "rptCurrentData"
   strSavePath = "G:\Documents\PDFs\"
   strEmail = "hfeddema@hvc.rr.com"
   strReportFile = strSavePath & "Current Data.pdf"
   
   'Create PDF file
   DoCmd.OutputTo objecttype:=acOutputReport, _
      objectname:=strReport, _
      outputformat:=acFormatPDF, _
      outputfile:=strReportFile, _
      autostart:=True
   
   'Create email
   strEmail = "Email address"
   Set itm = appOutlook.CreateItem(olMailItem)
   itm.Subject = "Message Subject"
   itm.Body = "Message body"
   itm.To = strEmail
   itm.Attachments.Add Source:=strReportFile, _
      Type:=olByValue
   itm.Display
   
ErrorHandlerExit:
   Set appOutlook = Nothing
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number & "; Description: "
   Resume ErrorHandlerExit

End Sub

Open in new window

0
GPSPOWAuthor Commented:
Helen,

How can I add the values from the form input fields txtFrom and txtTo to the "strReport" field value?

'Create PDF file
   DoCmd.OutputTo objecttype:=acOutputReport, _
      objectname:=strReport, _
      outputformat:=acFormatPDF, _
      outputfile:=strReportFile, _
      autostart:=True

Thanks

Glen
0
Jeffrey CoachmanMIS LiasonCommented:
Using Your original code, try something like this:

Private Sub Print_Transfer_Report_Click()

Dim vFrom As String
Dim vTo As String

vFrom = Format([Forms]![FrmTransfRpt]![txtFrom], "mm/dd/yy")
vTo = Format([Forms]![FrmTransfRpt]![TxtTo], "mm/dd/yy")
Private Sub Print_Transfer_Report_Click()

Dim vFrom As String
Dim vTo As String

vFrom = Format([Forms]![FrmTransfRpt]![txtFrom], "mm-dd-yy")
vTo = Format([Forms]![FrmTransfRpt]![TxtTo], "mm-dd-yy")
DoCmd.OutputTo acReport, "rpt_TransferReport", acFormatPDF, "\\pmcfs\groups\HR\HR Extractions\TransferRpts\Employee Transfers From_" & vFrom & "_To_" & vTo & ".PDF", False, 0

DoCmd.Close acReport, "rpt_TransferReport"

End Sub
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jeffrey CoachmanMIS LiasonCommented:
Private Sub Print_Transfer_Report_Click()

Dim vFrom As String
Dim vTo As String

vFrom = Format([Forms]![FrmTransfRpt]![txtFrom], "mm-dd-yy")
vTo = Format([Forms]![FrmTransfRpt]![TxtTo], "mm-dd-yy")
DoCmd.OutputTo acReport, "rpt_TransferReport", acFormatPDF, "\\pmcfs\groups\HR\HR Extractions\TransferRpts\Employee Transfers From_" & vFrom & "_To_" & vTo & ".PDF", False, 0

DoCmd.Close acReport, "rpt_TransferReport"

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GPSPOWAuthor Commented:
Thanks to all who submitted a solution.  This was what I was looking for.  The format of the date was acceptable for a file name.  the solution before had the "/" in it so it would not work.

Glen
0
Jeffrey CoachmanMIS LiasonCommented:
Thanks...

Also note that if you do his twice in the same day, the original will be overwritten (lost, unrecoverable)

So it is advised to include the current date and time to all exported reports, ...something like this:
DoCmd.OutputTo acReport, "rpt_TransferReport", acFormatPDF, "\\pmcfs\groups\HR\HR Extractions\TransferRpts\Employee Transfers From_" & vFrom & "_To_" & vTo & DoCmd.OutputTo acOutputReport, "rptHousesWithOptimum", acFormatPDF, "c:\Junk" & "_CreatedOn_" & Format(Now, "YYYY-MM-DD hh_nn_ss") & ".pdf"
.PDF", False, 0

This way you can keep a history of all the reports.

As a benefit, the format: "YYYY-MM-DD hh_nn_ss"
...will allow the files to be sorted correctly by date.

JeffCoachman
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.