Can't set a folder in a form text box, for later use in a sub for exporting a query... (1 Viewer)

Peter_W

New member
Local time
Today, 13:03
Joined
Jun 4, 2019
Messages
12
Hello all,

Even as a novice programmer, I thought I could get this Access VBA code to work, but can't.

I would like to set a folder's value in a form text box, for later use to direct the user's exporting to Excel. I am assuming I can set just the folder path....and that I do not need to select a specific file.

So, I have this code entered for a button's button click, to first allow the user to specify a folder. Surely I must be doing something wrong, since the folder will first correctly populate in the text box, but will disappear after the form is closed:

Public Sub cmdBrowserButton_Click()

Dim strChoice As String
strChoice = FolderSelection
If Len(strChoice) > 0 Then

Me.txtForBrowser.Value = strChoice

Else
Close
End If

End Sub


Public Function FolderSelection() As String
Dim objFD As Object
Dim strOut As String

strOut = vbNullString
'msoFileDialogFolderPicker = 4
Set objFD = Application.FileDialog(4)
If objFD.Show = -1 Then
strOut = objFD.SelectedItems(1)
End If
Set objFD = Nothing
FolderSelection = strOut
End Function


Again, after a specific folder is set by the form, I am later hoping to use the specified folder when exporting a query to Excel, per this code in another sub:

'Set the Folder path for Exporting Query to Excel

strPath = Forms![frmMainMenu]![txtForBrowser].Value

So all in all, the exporting sub does cycle through, but the expected query data does not show in the destination template (xltm) file.

I assume I'm doing something wrong in the above code. Everything else in the database I'm creating, works fine.

Any feedback would be most appreciated, thank you.
PW
 

Gasman

Enthusiastic Amateur
Local time
Today, 20:03
Joined
Sep 21, 2011
Messages
14,047
That is just the folder path?, you still need \ and a filename appended to it?
 

Peter_W

New member
Local time
Today, 13:03
Joined
Jun 4, 2019
Messages
12
Thank you Gasman for replying. I had trouble logging back in earlier to Access World.

I get the same problem, when I use code to select just a folder.

The folder path stays in the text box for a minute, then it disappears, say as soon as I close the form.

Of all my Access misadventures, this is one of the most deflating. Thanks again.
 

jdraw

Super Moderator
Staff member
Local time
Today, 16:03
Joined
Jan 23, 2006
Messages
15,364
The value you enter in a form does not persist (once form is closed) unless you save it to a file/table or a tempVars variable.

More info/examples
 

Gasman

Enthusiastic Amateur
Local time
Today, 20:03
Joined
Sep 21, 2011
Messages
14,047
Thank you Gasman for replying. I had trouble logging back in earlier to Access World.

I get the same problem, when I use code to select just a folder.

The folder path stays in the text box for a minute, then it disappears, say as soon as I close the form.

Of all my Access misadventures, this is one of the most deflating. Thanks again.
Ah, OK, I was expecting the form to be open? as it has to if you are going to refer to any control on that form?
 

Peter_W

New member
Local time
Today, 13:03
Joined
Jun 4, 2019
Messages
12
Thank you jdraw. I was not aware that a text box does not save an instance of a single entry, as you wrote. Let me go back and try what you are saying. Thanks again, PW
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:03
Joined
Feb 19, 2002
Messages
42,973
Put another way, tables store data, forms display data that was stored in a table.
 

Users who are viewing this thread

Top Bottom