Dynamic file name form report export (1 Viewer)

[amarton]

Hi,

I have a form what I would like to export out to PDF.
I tried 2 different ways, both is working, but I get stuck, when I would like to include a date.

The current code is this:

Private Sub SaveAppendixTest_Click()[/INDENT]
[INDENT][/INDENT]
[INDENT][/INDENT]
[INDENT]    Dim SITEID As String[/INDENT]
[INDENT]    Dim fileName As String[/INDENT]
[INDENT]    Dim pdfFilePath As String[/INDENT]
[INDENT]    SITEID = Me.SITEID.Value[/INDENT]
[INDENT]    fileName = SITEID & "-" & [CLIENT COMPANY] & "-" & [SITE Name] & ".pdf"[/INDENT]
[INDENT]    pdfFilePath = "C:\Users\xyz\OneDrive\Desktop\" & fileName[/INDENT]
[INDENT]    DoCmd.OutputTo acOutputReport, "Monthly Appendix", acFormatPDF, pdfFilePath, Autostart:=True[/INDENT]
[INDENT][/INDENT]
[INDENT]End Sub

I have a date field on the report (BILLINGMONTH) in a MMM YYYY format, but I can't include it into the filename. ANY OTHER field is working, but this on is not.

Any suggestions what do I do wrong?
Thanks in advance!

 

[Gasman]

Show how you are trying to include it.
Do not know what option you used to paste the code, but it clutters the code more than make it easy to view?
Do you actually check what you post?

 

[Pat Hartman]

If the report has criteria based on a date and that is what is driving the selection, you need to access that date from the code so you can use it to format the PDF name. In my applications, either the date/date range is calculated, in which case, it can be calculated in the VBA or it comes from unbound or bound controls on a form, either of which can be referenced from the VBA.

 

[amarton]

If the report has criteria based on a date and that is what is driving the selection, you need to access that date from the code so you can use it to format the PDF name. In my applications, either the date/date range is calculated, in which case, it can be calculated in the VBA or it comes from unbound or bound controls on a form, either of which can be referenced from the VBA.

The report is based on the siteID, that is driving the selection and I have the bound control for the date.

I tired this 1st:
Private Sub SaveAppendixTest_Click()

DoCmd.OutputTo acOutputReport, "Monthly Appendix", acFormatPDF, "C:\Users\axyz\OneDrive\Desktop\" & SITEID & BILLINGMONTH & "-" & "App" & "-" & [CLIENT COMPANY] & " - " & [SITE Name] & ".PDF", AutoStart:=True

End Sub

It works, but the BILLINGMONTH is not picked up, to the file is generated without the date.

The second attempt was this.

Private Sub SaveAppendixTest_Click()
Dim SITEID As String
Dim BILLINGMONTH As Sting
Dim fileName As String
Dim pdfFilePath As String
SITEID = Me.SITEID.Value
BILLINGMONTH = Me.BILLINGMONTH.Value
fileName = SITEID & "-" & BILLINGMONTH & [CLIENT COMPANY] & "-" & [SITE Name] & ".pdf"
pdfFilePath = "C:\Users\xyz\OneDrive\Desktop\" & fileName
DoCmd.OutputTo acOutputReport, "Monthly Appendix", acFormatPDF, pdfFilePath, Autostart:=True

End Sub

But is was also not working, so I just left out the BILLINGMONTH

 

[Pat Hartman]

Are you sure that BILLINGMONTH has a value