asked on

User choose folder in Microsoft Access 2003

In my system I want the user to be able to set the systemfolder so the database, templates and other files can be moved. Today I use only a textfield, but it would be nice if the user could choose folder by browsing to the folder with a dialogbox.

Thank you!

ASKER CERTIFIED SOLUTION

Scott McDaniel (EE MVE )

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

therealmongoose

here's a useful function to achieve this. You can also set a default path to start the browse... Please note you need to set a reference to the shell32.dll (vba editor, tools, references... browse for the dll

To use the function, have a text field (or code variable) which is populated from a command button

me.txtfield = fncBrowseForFolder("Select a folder", "C:\")

Option Explicit
 
'Browse for folder function - allows selection of start folder (strRoot argument)
'requires reference to "c:\windows\system32\shell32.dll to be set in tools menu
 
Private Const BIF_RETURNONLYFSDIRS = &H1
 
Function fncBrowseForFolder(strTitle As String, strRoot As String) As String
 
On Error GoTo fncBrowseForFolder_error
 
    Dim shlObj As Shell
    Dim fldFolder As Folder
    Dim strFolder As String
    
    Set shlObj = New Shell
    Set fldFolder = shlObj.BrowseForFolder(hWndAccessApp, strTitle, BIF_RETURNONLYFSDIRS, strRoot)
    Set shlObj = Nothing
    
    strFolder = fldFolder.Self.Path
        
    Set fldFolder = Nothing
    
fncBrowseForFolder_exit:
 
    fncBrowseForFolder = strFolder
    Exit Function
    
fncBrowseForFolder_error:
    
    If Err.Number <> 91 Then 'object error = user pressed cancel - folder not created
        MsgBox Err.Number & " - " & Err.Description
    End If
    
    Resume fncBrowseForFolder_exit
    
End Function

Open in new window