Dynamic file name form report export (1 Viewer)

amarton

New member
Local time
Today, 05:01
Joined
Nov 6, 2023
Messages
17
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:
Code:
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

Enthusiastic Amateur
Local time
Today, 05:01
Joined
Sep 21, 2011
Messages
14,528
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

Super Moderator
Staff member
Local time
Today, 00:01
Joined
Feb 19, 2002
Messages
43,587
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

New member
Local time
Today, 05:01
Joined
Nov 6, 2023
Messages
17
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

Super Moderator
Staff member
Local time
Today, 00:01
Joined
Feb 19, 2002
Messages
43,587
Are you sure that BILLINGMONTH has a value
 

Users who are viewing this thread

Top Bottom