Vba – FileDialog doesn’t work

ms-accessms-access-2010vba

I've looked quite intensely, but couldn't find a post that directly solves my problem.

The following code for a form I created works in Access 2003, which I use at work.

Dim FileName As FileDialog
Set FileName = Application.FileDialog(msoFileDialogFilePicker)
Dim Name As Variant

With FileName
    .AllowMultiSelect = False
    .Show
    If .SelectedItems.Count = 0 Then
    MsgBox "No file selected."
    Exit Sub
    End If
End With

For Each Name In FileName.SelectedItems
FileNameTextBox.Text = Mid$(Name, InStrRev(Name, "\") + 1)
Next Name

However, when I tried to run the same code on a form in Access 2010 on my personal computer, it doesn't work.The error message highlights the first line and says "User-defined type not defined." I also tried declaring FileName as Office.FileDialog, but no luck either. I do have Microsoft Access 14.0 Object Library as one of the references in use, so I don't know what's wrong with that.

I've only been using Access for two weeks, and all my knowledge come from googling, so it's very likely that I'm missing something obvious.

Best Solution

The FileDialog object is not provided by the Access library, but by the Office library. So your code should work if you set a reference to the Microsoft Office [version number] Object Library. Either you don't have that reference set, or it's broken.

However if it were me, I would leave the reference unset and modify the code like this. See if it works for you.

Const msoFileDialogFilePicker As Long = 3
Dim objDialog As Object

Set objDialog = Application.FileDialog(msoFileDialogFilePicker)

With objDialog
    .AllowMultiSelect = False
    .Show
    If .SelectedItems.Count = 0 Then
        MsgBox "No file selected."
    Else
        Me.FileNameTextBox.Value = Dir(.SelectedItems(1))
    End If
End With
Related Question