Adding a file browser button to a MS Access form

Question

I'd like to add a "Browse" button to an MS Access 2007 form that will bring up a standard Windows file browser (as a modal window) and allow the user to select a directory. When the user OKs out of that browser, the path the the selected directory should be written to a text box in the Access form.

What's the best way to do this? Is there a native Access way?

Answer 1

Create a function which uses Application.FileDialog. The FileDialog is modal.

This function will return the user's folder selection if they made one, or an empty string if they clicked cancel on the FileDialog.

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

I think you can use that function in your command button's click event.

Dim strChoice As String
strChoice = FolderSelection
If Len(strChoice) > 0 Then
    Me.TextBoxName = strChoice
Else
    ' what should happen if user cancelled selection?
End If

If you're concerned that Microsoft may remove the FileDialog object from Office someday, you can use the Windows API method instead: BrowseFolder Dialog.