Choose a Folder

[mike60smart]

Mike,
Not sure I'm following the details.
I added a button in the header of the report.
button name btnSave
click event

Private Sub btnSave_Click()
    Call SaveFileToSelectedFolder
End Sub

SaveFileToSelectedFolder is routine I provided yesterday.

I have attached a very rough gif in the zip showing things.

Hi Jack

That works perfect.

Do you know how I can also add the capability to store the Filename as follows:-

OutputFile = OutputFile & [LoadNumber] & " - " & Format(Date, "mmddyyyy") & "- AgentSettlement.pdf"

 

[mike60smart]

You haven't shown how you're doing it, but I was expecting something like:

OutputFile = FunctionCallHereToGetFolderPath
OutputFile = OutputFile & [LoadNumber] & " - " & Format(Date, "mmddyyyy") & "- AgentSettlement.pdf"

You may have to add a trailing "\" if the function call doesn't include one.

Hi Paul

In your example you used this line of Code:-

OutputFile = FunctionCallHereToGetFolderPath

Where does the "FunctionCallHereToGetFolderPath" come from ?

 

[jdraw]

Mike,

Based on my code from yesterday, I would adjust this line replace the blue part with the green part

DoCmd.OutputTo acReport, "rpt_ancestor", acFormatPDF, selectedFolder _
& "\SampleFileName" & "Mar212023_mikesmart" & ".pdf"

& [LoadNumber] & " - " & Format(Date, "mmddyyyy") & "- AgentSettlement.pdf"

 

[mike60smart]

Mike,

Based on my code from yesterday, I would adjust this line replace the blue part with the green part

DoCmd.OutputTo acReport, "rpt_ancestor", acFormatPDF, selectedFolder _
& "\SampleFileName" & "Mar212023_mikesmart" & ".pdf"

& [LoadNumber] & " - " & Format(Date, "mmddyyyy") & "- AgentSettlement.pdf"

Hi Jack
Now that is a thing of beauty.

Please ignore the following lines in Red :-
Only problem that I can see is "AgentSettlement" is the name of the Report which will of course vary

depending on Report to Save,

I created an Unbound Textbox with the ReportName as follows ="Agent Settlement" and named it "DocName"

Then in your Code i used this:-

50 DoCmd.OutputTo acReport, "rpt_ancestor", acFormatPDF, SelectedFolder _
& [LoadNumber] & " - " & Format(Date, "mmddyyyy") & "- [DocName].pdf"

When save the Report the Folder Picker displays as normal.
I select the Folder to store the Report
Click OK and the msgbox pops up stating it is outputing to the folder.
But when I look in the C: Drive it is not stored in any folder but directly to the C: Drive ??

Any thoughts??

 

[pbaldy]

Hi Paul

In your example you used this line of Code:-

OutputFile = FunctionCallHereToGetFolderPath

Where does the "FunctionCallHereToGetFolderPath" come from ?

FunctionCallHereToGetFolderPath was meant to be replaced with whatever function you chose to let the user pick the target folder.

 

[pbaldy]

Hi Paul & Jack

Change of plan.

We now display the Report in Report View.

Then on the Report we have a Command Button with the following OnClick Event.

Private Sub cmdSave_Click()

10        On Error GoTo cmdSave_Click_Error
       
          Dim strDocname As String
          Dim strWhere As String
         
          Dim SelectedFolder As String
          Dim FunctionCallHereToGetFolderPath As String
          Dim OutputFile As String
         
20        strDocname = "AgentSettlement"
30        strWhere = "[AgentID]=" & Me.ID


40        OutputFile = FunctionCallHereToGetFolderPath
50        OutputFile = [LoadNumber] & " - " & Format(Date, "mmddyyyy") & "- [DocName].pdf"
         

         
60        On Error GoTo 0
70        Exit Sub

cmdSave_Click_Error:

80        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdSave_Click, line " & Erl & "."

End Sub

Currently it does not do anything and I get no errors.

Can you help to show me where I am going wrong?

Any help appreciated.

You are setting a string variable but not doing anything with it. You'd use OutputFile with DoCmd.OutputTo to create the file. Also, in conjunction with my previous post, this line

40 OutputFile = FunctionCallHereToGetFolderPath

would look more like this, using the function you originally posted:

40 OutputFile = FSBrowse(Appropriate arguments here)

 

[jdraw]

Mike,

Modified the sub to accept a parameter (the name of the report).

This requires a change to the button click event

Private Sub btnSave_Click()
'Me.Name is the name of the current report
    Call SaveFileToSelectedFolder(Me.name)
End Sub

Here is revised sub:

' ----------------------------------------------------------------
' Procedure Name: SaveFileToSelectedFolder
' Purpose: Routine to use FileDialogPicker to save a file
' Parameter: rptName (string): Name of the report
' Procedure Kind: Sub
' Procedure Access: Public
' Author: Jack
' Date: 22-Mar-23
' Related: https://www.access-programmers.co.uk/forums/threads/choose-a-folder.327069/#post-1869955
' ----------------------------------------------------------------
Sub SaveFileToSelectedFolder(rptName As String)
10        On Error GoTo SaveFileToSelectedFolder_Error
          Dim fd As filedialog
          Dim selectedFolder As String
         
          ' Create a FileDialog object as a Folder Picker dialog box.
20        Set fd = Application.filedialog(msoFileDialogFolderPicker)
         
          ' Show the Folder Picker dialog box.
30        If fd.Show = -1 Then
              ' User has selected a folder, so set the selectedFolder variable to the path of the selected folder.
40            selectedFolder = fd.SelectedItems(1)
              ' Save the file to the selected folder.
'*************************************************************************
'  Mike: 'Whatever is whatever you have for Load info
' " - " & Format(Date, "mmddyyyy") & " is directly from your code
'rptName is the parameter being passed in the button click event
'**************************************************************************
50            DoCmd.OutputTo acReport, "rpt_ancestor", acFormatPDF, selectedFolder _
                  & "\Whatever" & " - " & Format(Date, "mmddyyyy") & " - " & rptName & ".pdf"

             'This is just to record that the report was saved
60            Debug.Print "File " & "\Whatever" & " - " & Format(Date, "mmddyyyy") & " - " & rptName & ".pdf" _
                  & " saved to: " & selectedFolder & "   " & Now
70        Else
              ' User has cancelled the Folder Picker dialog box.
80            MsgBox "No folder was selected."
90        End If
       
          ' Set the FileDialog object to Nothing.
100       Set fd = Nothing
         
110       On Error GoTo 0
SaveFileToSelectedFolder_Exit:
120       Exit Sub

SaveFileToSelectedFolder_Error:

130       Debug.Print "Error " & Err.Number & " (" & Err.Description & "), line " & Erl & " in Procedure SaveFileToSelectedFolder" _
              & "  Module  ZZ_ScratchPad "
140       GoTo SaveFileToSelectedFolder_Exit
End Sub

This got written to the immediate window

File \Whatever - 03232023 - rpt_ancestor.pdf saved to: C:\Users\JP\Documents\AdjustADDin 23-Mar-23 1:01:01 PM

 

[mike60smart]

Mike,

Modified the sub to accept a parameter (the name of the report).

This requires a change to the button click event

Private Sub btnSave_Click()
'Me.Name is the name of the current report
    Call SaveFileToSelectedFolder(Me.name)
End Sub

Here is revised sub:

' ----------------------------------------------------------------
' Procedure Name: SaveFileToSelectedFolder
' Purpose: Routine to use FileDialogPicker to save a file
' Parameter: rptName (string): Name of the report
' Procedure Kind: Sub
' Procedure Access: Public
' Author: Jack
' Date: 22-Mar-23
' Related: https://www.access-programmers.co.uk/forums/threads/choose-a-folder.327069/#post-1869955
' ----------------------------------------------------------------
Sub SaveFileToSelectedFolder(rptName As String)
10        On Error GoTo SaveFileToSelectedFolder_Error
          Dim fd As filedialog
          Dim selectedFolder As String
       
          ' Create a FileDialog object as a Folder Picker dialog box.
20        Set fd = Application.filedialog(msoFileDialogFolderPicker)
       
          ' Show the Folder Picker dialog box.
30        If fd.Show = -1 Then
              ' User has selected a folder, so set the selectedFolder variable to the path of the selected folder.
40            selectedFolder = fd.SelectedItems(1)
              ' Save the file to the selected folder.
'*************************************************************************
'  Mike: 'Whatever is whatever you have for Load info
' " - " & Format(Date, "mmddyyyy") & " is directly from your code
'rptName is the parameter being passed in the button click event
'**************************************************************************
50            DoCmd.OutputTo acReport, "rpt_ancestor", acFormatPDF, selectedFolder _
                  & "\Whatever" & " - " & Format(Date, "mmddyyyy") & " - " & rptName & ".pdf"

             'This is just to record that the report was saved
60            Debug.Print "File " & "\Whatever" & " - " & Format(Date, "mmddyyyy") & " - " & rptName & ".pdf" _
                  & " saved to: " & selectedFolder & "   " & Now
70        Else
              ' User has cancelled the Folder Picker dialog box.
80            MsgBox "No folder was selected."
90        End If
     
          ' Set the FileDialog object to Nothing.
100       Set fd = Nothing
       
110       On Error GoTo 0
SaveFileToSelectedFolder_Exit:
120       Exit Sub

SaveFileToSelectedFolder_Error:

130       Debug.Print "Error " & Err.Number & " (" & Err.Description & "), line " & Erl & " in Procedure SaveFileToSelectedFolder" _
              & "  Module  ZZ_ScratchPad "
140       GoTo SaveFileToSelectedFolder_Exit
End Sub

This got written to the immediate window

File \Whatever - 03232023 - rpt_ancestor.pdf saved to: C:\Users\JP\Documents\AdjustADDin 23-Mar-23 1:01:01 PM

Hi Jack

I modified the Save Event as follows

Private Sub btnSave_Click()
'Me.Name is the name of the current report
Call SaveFileToSelectedFolder(Me.rptName) "Where rptName is the name of the Current Report"
End Sub

Used your latest version of the Function and now nothing happens after I select the Folder name and Click OK . No errors displayed??

 

[mike60smart]

You are setting a string variable but not doing anything with it. You'd use OutputFile with DoCmd.OutputTo to create the file. Also, in conjunction with my previous post, this line

40 OutputFile = FunctionCallHereToGetFolderPath

would look more like this, using the function you originally posted:

40 OutputFile = FSBrowse(Appropriate arguments here)

Thanks Paul that explains that

 

[mike60smart]

Hi Jack & Paul

I found the following Code which does exactly what I wanted.

Many thanks for all the help.

Dim fileName As String, fldrPath As String, filePath As String
    Dim answer As Integer
    
     'filename for PDF file*"
    fileName = "" & [CarrierName] & " - " & Format(Date, "mmddyyyy") & "- CarrierSettlement"
  
    fldrPath = "C:\Carrier Settlements"       'folder path where pdf file will be saved *
    
    filePath = fldrPath & "\" & fileName & ".pdf"
    
    
    On Error GoTo invalidFolderPath
    DoCmd.OutputTo ObjectType:=acOutputReport, ObjectName:=Me.Name, outputformat:=acFormatPDF, OutputFile:=filePath
    
    MsgBox Prompt:="PDF File exported to: " & vbNewLine & filePath, buttons:=vbInformation, Title:="Report Exported as PDF"
    Exit Sub
    
invalidFolderPath:
    MsgBox Prompt:="Error: Invalid folder path. Please update code.", buttons:=vbCritical

 

[pbaldy]

I'm glad you have a solution, though it doesn't address your original question of allowing the user to select the folder.

 

[mike60smart]

Hi Paul

True but he only has 5 Reports so it was a better solution to specify the Folder in the Code.

 

[jdraw]

Glad you have it sorted.