Using textbox for filename Export (1 Viewer)

[JoeBruce]

I have a form where the user enters a StartDate and EndDate to generate a report. If I want to export this report to a PDF, I would like the filename to include the StartDate and EndDate. I have the code below, which does not return any errors, but does not include the info in the file name.

Sub GetStartDate()
    Dim StartDate As String
    StartDate = [Forms]![frmDatabaseReports]![StartDate]
End Sub
Sub GetEndDate()
    Dim EndDate As String
    EndDate = [Forms]![frmDatabaseReports]![EndDate]
End Sub

Private Sub btnExportPDF_Click()
    TestForDir
    GetStartDate
    GetEndDate
    Dim strfilename As String
    strfilename = "C:\Users\" & Environ("username") & "\Desktop\Database Reports\Program Statistics from " & StartDate & " to " & EndDate & ".pdf"
    DoCmd.OutputTo acOutputReport, "rptProgramStatsByDate", acFormatPDF, strfilename, True
End Sub

Right now the file name just comes out as "Program Statistics from to .pdf"

Any thoughts on getting the file name to include the dates?

 

[MarkK]

What is the name of the form? If it's 'frmDatabaseReports', then you can just do...

Private Sub btnExportPDF_Click()
    Dim strfilename As String
    TestForDir
    strfilename = "C:\Users\User\Reports\Stats" & [COLOR="DarkRed"]Me.StartDate[/COLOR] & " to " & [COLOR="DarkRed"]Me.EndDate[/COLOR] & ".pdf"
    DoCmd.OutputTo acOutputReport, "rptProgramStatsByDate", acFormatPDF, strfilename, True
End Sub

 

[Ranman256]

If you are in the form doing the printing,
Me.txtDateStart

Use the name of the textbox,not the name of the field. (But they could be the same)

if not on the form,Use the full path
Forms!myForm!txtDateStart

You can't use slashes,so format it some other way...
"MyFile" & Format(date(),"yyyymmdd") & ".pdf"

 

[MarkK]

This is important...

You can't use slashes,so format it some other way...
"MyFile" & Format(date(),"yyyymmdd") & ".pdf"

Good one Ranman.

 

[JoeBruce]

The user enters the dates in a textbox on "frmDatabaseReports" then clicks a button that opens the report. The textboxes are just named "StartDate" and "EndDate." On the report is a button that exports the report to PDF.

I've changed it to keep the form open so it can pull these values, hence why I used the full path name for the form.

I'm also aware of the problem with slashes, and I've forced the format of the textboxes to Medium Date so it appears 01-Mar-16 - hopefully the code is pulling this as the stored value and not whatever a user might enter (i.e. 3/1/2016).

Unless I'm missing something, my code is already following your suggestions. I suppose I could change the command button on "frmDatabaseReports" to just export the PDF - cutting out the 'middle man' of viewing the report itself. But I'd like to be able to view the report in Access.

Edit: I tried this without defining strings, and inserting the full path name:

strfilename = "C:\Users\" & Environ("username") & "\Desktop\Database Reports\Program Statistics from " & Forms!frmDatabaseReports!StartDate & " to " & Forms!frmDatabaseReports!EndDate & ".pdf"

But I believe this returned an error of some kind (not at work right now so I can't check this).

 

[MarkK]

The problem with the code you posted is that these routines...

Sub GetStartDate()
    Dim StartDate As String
    StartDate = [Forms]![frmDatabaseReports]![StartDate]
End Sub
Sub GetEndDate()
    Dim EndDate As String
    EndDate = [Forms]![frmDatabaseReports]![EndDate]
End Sub

...do not return values, and the assignments are made to variables that go out of scope when the routines end, so when you make these references later on...

strFileName = "C:\Users\...\Statistics from " & [COLOR="DarkRed"]StartDate[/COLOR] & " to " & [COLOR="DarkRed"]EndDate[/COLOR] & ".pdf"

...the highlighted variables are not the same as the ones declared and destroyed in the above sub-routines.

 

[JoeBruce]

I understand you're saying my code is no good, but beyond that....

I'm very new to writing code (and Access in general), and it's taken me quite a lot to get this far. I've only started learning VBA over the last few days because I realized Macros can't do all I want to do.

Is my approach at least on the right track - trying to store those values as strings do recall them later for use in the naming of the file? If so, how would I go about fixing it? Moving these two lines within the "sub" thing that exports the file?:

Dim StartDate As String
    StartDate = [Forms]![frmDatabaseReports]![StartDate]

Is it better to just recall them using the Forms! bit as described previously?:

strfilename = "C:\Users\" & Environ("username") & "\Desktop\Database Reports\Program Statistics from " & Forms!frmDatabaseReports!StartDate & " to " & Forms!frmDatabaseReports!EndDate & ".pdf"

I appreciate the advice.

 

[JoeBruce]

I've forced the format of the textboxes to Medium Date so it appears 01-Mar-16 - hopefully the code is pulling this as the stored value and not whatever a user might enter (i.e. 3/1/2016).

After figuring it out, I believe the code was still pulling the user-entered value, or was somehow reverting the format to using the "/" character. I also followed my own inclinations - inspired by MarkK - to reorder my code:

Private Sub btnExportPDF_Click()
    Dim StartDate As String
    StartDate = [Forms]![frmDatabaseReports]![StartDate]
    StartDate = Format(StartDate, "Medium Date")
    Dim EndDate As String
    EndDate = [Forms]![frmDatabaseReports]![EndDate]
    EndDate = Format(EndDate, "Medium Date")
    Dim strfilename As String
    strfilename = "C:\Users\" & Environ("username") & "\Desktop\Database Reports\Program Statistics from " & StartDate & " to " & EndDate & ".pdf"
    DoCmd.OutputTo acOutputReport, "rptProgramStatsByDate", acFormatPDF, strfilename, True
End Sub

Just before coming up with my solution, I realized I could also pull those user-defined values as my report header; so even if I couldn't get it to populate in the exported file name, at least it was at the top of the report.

 

[MarkK]

Nice job. One best practice is to do all your declarations at the start of the routine. See how laying out the routine like this may make it considerably more readable?

Private Sub btnExportPDF_Click()
    Dim StartDate As String
    Dim EndDate As String
    Dim strfilename As String
    
    StartDate = Format(Forms!frmDatabaseReports.StartDate, "Medium Date")
    EndDate = Format(Forms!frmDatabaseReports.EndDate, "Medium Date")

    strfilename = _
        "C:\Users\" & Environ("username") & "\Desktop\Database Reports\" & _
        "Program Statistics from " & StartDate & " to " & EndDate & ".pdf"
    
    DoCmd.OutputTo acOutputReport, "rptProgramStatsByDate", acFormatPDF, strfilename, True
End Sub

Keep in mind that developing a code base is a significant amount of work and is, therefore, a considerable asset. It is worth protecting that investment by making that code as easy as possible to understand. Code that is hard to understand, code that is not clear what it does, is less valuable code.