MS Access: Browse For File Command Button (VBA Compile Error)

[Gwyn Jones]

I'm using Access 2000 on Windows 7, and have been following numerous examples of how to create a "Browse for file" button which displays the path of the file in a text box.

This is as close as I have come to finding a solution, and yet I have an error with the line highlighted in bold below.

The error states:

Compile Error:

Method or Data Member not found

Can anyone explain what might be causing the problem?


My code is found below:

Private Sub fdg_Click()

Dim fdg As FileDialog, vrtSelectedItem As Variant
Dim strSelectedFile As String
  
[u]Set fdg = Application.FileDialog(msoFileDialogFilePicker)[/u]
With fdg
  .AllowMultiSelect = False
  .InitialView = msoFileDialogViewDetails
    If .Show = -1 Then
      For Each vrtSelectedItem In .SelectedItems 'onby be 1
        strSelectedFile = vrtSelectedItem & "#" & vrtSelectedItem
      Next vrtSelectedItem
      Me![txtSelectedFile] = strSelectedFile
    Else 'The user pressed Cancel.
    End If
End With
  
Set fd = Nothing

End Sub

Many thanks!

[Seth Schrock]

I believe that your problem is in line 19. You have

Set fd = nothing

However, the variable that you defined was fdg. Try that and see what you get.

[TheSmileyCoder]

Have you set a reference to the Office library? Look in Select file or folder using filedialog object, in the Objects paragraph for more info.

[NeoPa]

Line #19 certainly is a problem, but the highlighted line (which was obscure before as BOLD tags don't show well within CODE tags) is the problem asked about (and Smiley's response deals with that).

Good work both of you :-)

[Mihail]

A problem can be (I am not sure) line 3. You already have a control named "fdg". I see that in line 1.

[NeoPa]

Not a compile problem certainly, but just as certainly an unwise choice when programming (and worth pointing out). Both can still be referenced, but any reference will be ambiguous to a reader if not the VBA interpreter (which is not good news).

[ADezii]

1. The Code you displayed is for Copying an Absolute File Path to a Text Box, whose Control Source is a Hyperlink Field. I do not think that this is what you want, since you did not specifically mention it.
2. The Code can be made shorter and more efficient, as in:
   

   Dim fdg As FileDialog
     
   'Must set a Reference to the Microsoft Office ?X.X Object Library
   Set fdg = Application.FileDialog(msoFileDialogFilePicker)
   
   With fdg
     .AllowMultiSelect = False
     .InitialView = msoFileDialogViewDetails
       If .Show = -1 Then
         Me![txtSelectedFile] = .SelectedItems(1)
       End If
   End With
     
   Set fdg = Nothing

[NeoPa]

And (Just to be competitive with ADezii :-D) you can tidy it even further (than the good job he's already done).

'Must set a Reference to the Microsoft Office XX.X Object Library
With Application.FileDialog(msoFileDialogFilePicker)
    .AllowMultiSelect = False
    .InitialView = msoFileDialogViewDetails
    If .Show Then Me.txtSelectedFile = .SelectedItems(1)
End With

When you're done though Gwyn, I would suggest post #3 as a good candidate for Best Answer.

NB. ADezii's post gives a good approach to dealing with code that has library dependencies too. It is a very good idea to include such a comment line whenever any of your code depends on any libraries that are not referenced as standard.

[Gwyn Jones]

Apologies for the late reply, thank you all for your answers.

Smiley: I had already referenced it, but I wonder whether these objects that are referenced might be causing an issue:

- Microsoft Access 9.0 Object Library (is there a MS Access/Windows 7 conflict?)

- MISSING: Core OLE Control Module (is this important?)


ADezii/NeoPa: Apologies I'm not the best at VBA. The purpose of this is for users to search folders for image or text files, then select them leaving behind a hyperlink to open that file.

Do your codes meet this requirement? I noticed ADezii has posted about this very clearly in the past, but for some reason I could not get that code to work, and I wonder whether the references I'm using are the issue.

[ADezii]

The only External Reference needed to get the Code to work is the Microsoft Office ?X.X Object Library.

[Mihail]

I am not sure if this code is what you are looking for.
To try, paste it in a global module, make necessary changes (especially in sFilter) and apply from where you need.

The code will open the common dialog for "Open File" using API interface, so you don't need to reference any library.

Option Compare Database
Option Explicit

Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _
"GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long

Private Type OPENFILENAME
    lStructSize As Long
    hwndOwner As Long
    hInstance As Long
    lpstrFilter As String
    lpstrCustomFilter As String
    nMaxCustFilter As Long
    nFilterIndex As Long
    lpstrFile As String
    nMaxFile As Long
    lpstrFileTitle As String
    nMaxFileTitle As Long
    lpstrInitialDir As String
    lpstrTitle As String
    flags As Long
    nFileOffset As Integer
    nFileExtension As Integer
    lpstrDefExt As String
    lCustData As Long
    lpfnHook As Long
    lpTemplateName As String
End Type

Public Function SelectFile(StartFolder As String) As String
    Dim OpenFile As OPENFILENAME
    Dim lReturn As Long
    Dim sFilter As String
    OpenFile.lStructSize = Len(OpenFile)
'    OpenFile.hwndOwner = strform.Hwnd
    sFilter = "Access 2003 (*.mdb)" & Chr(0) & "*.mdb" & Chr(0) & _
      "Access 2007 (*.accdb)" & Chr(0) & "*.accdb" & Chr(0)
    OpenFile.lpstrFilter = sFilter
    OpenFile.nFilterIndex = 1
    OpenFile.lpstrFile = String(257, 0)
    OpenFile.nMaxFile = Len(OpenFile.lpstrFile) - 1
    OpenFile.lpstrFileTitle = OpenFile.lpstrFile
    OpenFile.nMaxFileTitle = OpenFile.nMaxFile
    OpenFile.lpstrInitialDir = "StartFolder"
    OpenFile.lpstrTitle = "Select a file using the Common Dialog DLL"
    OpenFile.flags = 0
    lReturn = GetOpenFileName(OpenFile)
        If lReturn = 0 Then
            MsgBox "A file was not selected!", vbInformation, "Select a file using the Common Dialog DLL"
         Else
            SelectFile= Trim(Left(OpenFile.lpstrFile, InStr(1, OpenFile.lpstrFile, vbNullChar) - 1))
         End If
End Function

Good luck !

[NeoPa]

The code blocks suggested enable you to provide the browsing facility for the user and have a string value which is the address of the file chosen. What you do with it from there is down to you, but if you want help with something in particular then I'm sure posting a question about it would work for you (Not in here though as each thread must be about the single original question only). There is already an existing thread that covers the same ground though (Error 7980: HyperlinkAddres s or HyperlinkSubAdd ress read-only for Hyperlink).