Browse and Open Folder Based on Matching Form Field

[Tomfernandez1]

Greetings,



I'm wondering if a simple code exists that can be applied to a command button on a Form to perform the following functions:
- browse specific folder on share drive
- open specific subfolder that matches information on the open Form

Any guidance would be much appreciated!

Thanks,

Tommy

[ajetrumpet]

Tom,

try something like:

      With Application.FileDialog(msoFileDialogFilePicker)
             .InitialFileName = "c:\yourMainDirectory\" & me.FORMFIELDsubDirectory
             .Show
      End With

[Tomfernandez1]

Hi Adam,

I inserted the code in the command button "on click" procedure, but got error message
"The expression On Click you entered as the event property setting produced the following error: Invalid outside procedure."

Any ideas? The directory I used is C:\Users\Tommy\Documents\Access
The Form field is "Last Name"

Thanks in advance for your help.

Tommy

[ajetrumpet]

YES, my guess would be that since the message says "invalid outside procedure", you forgot to put the code I posted INSIDE your actual procedure declaration lines. for instance:

private sub button_click()


end sub

that possible??

I know you said otherwise though. Care to post your entire procedure?? my other guess would be that if you did this:

.initialfilename = "directory" & me.lastname

you should be fine. spaces in field names on forms are automatically translated to (_) underscores in code references (I think). So your code might look like "last_name"

[Tomfernandez1]

Here is the code inserted:

Private Sub Command88_Click()
With APPLICATION.FileDialog(msoFileDialogFilePicker)
.InitialFileName = "c:\Users\Tommy\Documents\Access" & Me.[Last Name:]subDirectory
.Show
End With
End Sub

Last Name: is the Form field whose contents (Smith) would match a "Smith" folder in c:\Users\Tommy\Documents\Access

Tommy

[ajetrumpet]

geez...

change:

)
.InitialFileName = "c:\Users\Tommy\Documents\Access" & Me.[Last Name:]subDirectory

to:

.InitialFileName = "c:\Users\Tommy\Documents\Access" & Me.[Last Name]

[Tomfernandez1]

Adam- still coming up with same error message after using the latest code revision. Might you have a database with the code that you could share? I find this method to work best so I can compare my database/codes to one that actually works.

Tommy

[ajetrumpet]

I can do that, or upload yours and I'll fix it. let me know. I'd prefer yours over mine.

my db's are always over complex for my own enjoyment. Hence, I always have to modify code to make it relevant to someone else. And readable too!

[Tomfernandez1]

Here is a copy of the database I use that has button "command496" on the driver Form. This button has the code and when clicked, an error occurs with an option to 'debug'...

Thanks for the assistance, Adam!

[ajetrumpet]

and i said before, the intellisense changes control names with spaces in it to control names with (_) in them when you reference them in VBA. reference it correctly and it should work.

you still have it written as:

"directory\" & me.[LAST NAME]

when you use intellisense to help you, it becomes:

"directory\" & me.last_name

these vba tags aren't the greatest here, so the above code should read "last_name". for some reason, underscores aren't being parsed. I have no idea why.

please refer to my earlier posts.

[Tomfernandez1]

Adam- here is the code I found works:

Private Sub Command496_Click()

Dim fd As FileDialog

Set fd = Application.FileDialog(msoFileDialogFilePicker)

fd.InitialFileName = "C:\Documents and Settings\tommyf\My Documents\Access\" & Me.LAST_NAME

fd.Show
End Sub

Thanks, again, for your assistance!

Have a great day!

Tommy

[naseerrahaman]

Hi Team,

could any of you help me to open text files with word document with the below codes.

I am failing to write correct variables which help me browse and open text files with word document.

Thanks in advance for the help.

Option Explicit
Function BrowseForFolder(Optional OpenAt As Variant) As Variant
'Function purpose: To Browser for a user selected folder.
'If the "OpenAt" path is provided, open the browser at that directory
'NOTE: If invalid, it will open at the Desktop level
Dim ShellApp As Object

'Create a file browser window at the default folder
Set ShellApp = CreateObject("Shell.Application"). _
BrowseForFolder(0, "Please choose a folder", 0, OpenAt)

'Set the folder to that selected. (On error in case cancelled)
On Error Resume Next
BrowseForFolder = ShellApp.self.Path
On Error GoTo 0

'Destroy the Shell Application
Set ShellApp = Nothing

'Check for invalid or non-entries and send to the Invalid error
'handler if found
'Valid selections can begin L: (where L is a letter) or
Select Case Mid(BrowseForFolder, 2, 1)
Case Is = ":"
If Left(BrowseForFolder, 1) = ":" Then GoTo Invalid
Case Is = "\"
If Not Left(BrowseForFolder, 1) = "\" Then GoTo Invalid
Case Else
GoTo Invalid
End Select

Exit Function
Invalid:
'If it was determined that the selection was invalid, set to False
BrowseForFolder = False
End Function

Sub test1()
Dim result As String
result = BrowseForFolder
Select Case result
Case Is = False
result = "an invalid folder!"
Case Else
'don't change anything
End Select
MsgBox "You selected " & result, _
vbOKOnly + vbInformation
End Sub
Sub test2()
Dim OpenBrowseAt As String
MsgBox "Please choose a folder to use as your starting point!"
OpenBrowseAt = BrowseForFolder

Select Case OpenBrowseAt
Case Is = False
MsgBox "You selected an invalid folder!" & vbNewLine & _
"I will now open a new browser as" & vbNewLine & _
"if you made no selection!", vbOKOnly + vbInformation
Case Else
MsgBox "I will now open a new browser at" & vbNewLine & _
"your chosen location:" & vbNewLine & _
OpenBrowseAt, vbOKOnly + vbInformation
End Select
BrowseForFolder (OpenBrowseAt)

End Sub

Regards,
Rahaman.