Get Folder location from folder picker and save as string

[babar2019]

Hi,

I'm trying to add the location that user picks as a string so I can use the same path to export reports to but having trouble doing so. Please look at the VBA below. I'm just trying to save the user selected location to 'sFolder' and then use rloc to have the same value. Please see line 18.

Option Compare Database

Private Sub Command0_Click()
Dim rs As DAO.Recordset
Dim rsSQL As String
Dim hsql As String
Dim bsql As String
Dim gid As String
Dim gbor As String
Dim rloc As String
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim fsql As String

DoCmd.SetWarnings False

DoCmd.OpenQuery "qRawMT"
fsql = "ALTER TABLE tRaw ADD COLUMN ****ingID COUNTER;"
DoCmd.RunSQL fsql


rsSQL = "SELECT DISTINCT tRaw.GroupID, tRaw.Borrower FROM tRaw;"
rloc = sFolder
Set rs = CurrentDb.OpenRecordset(rsSQL)
rs.MoveFirst

DoCmd.OutputTo acOutputReport, "rGroupBilling", acFormatPDF, rloc & gbor & " " & gdt & ".pdf"
   
    Pause (5)
   
    rs.MoveNext
Loop

Me.lblIntro.Visible = False
Me.lblReady.Visible = True
Me.lblReady2.Visible = False
'DoCmd.DeleteObject acTable, "tRaw"

DoCmd.SetWarnings True

End Sub


Private Sub Command12_Click()
'Declare a variable as a FileDialog object.
Dim sFolder As String
'Create a FileDialog object as a File Picker dialog box.
With Application.FileDialog(msoFileDialogFolderPicker)
'Declare a variable to contain the path of each selected item.

        'Use the Show method to display the File Picker dialog box and return the user's action.
        'The user pressed the action button.
        If .Show = -1 Then
        sFolder = .SelectedItems(1)
        End If
    End With
End Sub

 

[JonXL]

I spot a few problems here - like trying to reference sFolder from two different routines - , but what is it exactly that isn't working about the code (do you get an error, etc.)?

Some more specifics on what's not happening as you expect will help narrow the troubleshooting effort.

 

[babar2019]

I see..Thanks for spotting that. Let me give you some background on this.

There is a form with 2 buttons. Browse Folder and Generate Pdf's.

Users will click on the 'Browse folder' button, pick the folder location and click 'ok. This location is saved to the 'sFolder' string.

sFolder = .SelectedItems(1)

I just would like that location to be saved to the 'rloc' string.

rloc = sFolder

Then users would click Generate pdf's and it should generate to the location they selected.

DoCmd.OutputTo acOutputReport, "rGroupBilling", acFormatPDF, rloc & gbor & " " & gdt & ".pdf"

Hope I was clear.

Thank you

 

[babar2019]

@JonXL Hi.. Did you get a chance to look at my comment?

 

[JonXL]

sFolder = .SelectedItems(1)

I just would like that location to be saved to the 'rloc' string.

rloc = sFolder

Maybe there is a better way, but I handle this by putting the file location returned from the file selection dialogue into a field on the form (make it invisible if you want, but I don't recommend that...). Then you used the field's value for the variable in the other function.

sFolder = .SelectedItems(1)
Me.txtField.Value = sFolder

rloc = Me.txtField.Value