By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,478 Members | 1,602 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,478 IT Pros & Developers. It's quick & easy.

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

P: 12
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:


Expand|Select|Wrap|Line Numbers
  1. Private Sub fdg_Click()
  2.  
  3. Dim fdg As FileDialog, vrtSelectedItem As Variant
  4. Dim strSelectedFile As String
  5.  
  6. Set fdg = Application.FileDialog(msoFileDialogFilePicker)
  7. With fdg
  8.   .AllowMultiSelect = False
  9.   .InitialView = msoFileDialogViewDetails
  10.     If .Show = -1 Then
  11.       For Each vrtSelectedItem In .SelectedItems 'onby be 1
  12.         strSelectedFile = vrtSelectedItem & "#" & vrtSelectedItem
  13.       Next vrtSelectedItem
  14.       Me![txtSelectedFile] = strSelectedFile
  15.     Else 'The user pressed Cancel.
  16.     End If
  17. End With
  18.  
  19. Set fd = Nothing
  20.  
  21. End Sub

Many thanks!
Nov 29 '11 #1
Share this Question
Share on Google+
11 Replies


Seth Schrock
Expert 2.5K+
P: 2,951
I believe that your problem is in line 19. You have
Expand|Select|Wrap|Line Numbers
  1. Set fd = nothing
However, the variable that you defined was fdg. Try that and see what you get.
Nov 29 '11 #2

TheSmileyCoder
Expert Mod 100+
P: 2,321
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.
Nov 29 '11 #3

NeoPa
Expert Mod 15k+
P: 31,709
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 :-)
Nov 29 '11 #4

100+
P: 759
A problem can be (I am not sure) line 3. You already have a control named "fdg". I see that in line 1.
Nov 30 '11 #5

NeoPa
Expert Mod 15k+
P: 31,709
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).
Nov 30 '11 #6

ADezii
Expert 5K+
P: 8,679
  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:
    Expand|Select|Wrap|Line Numbers
    1. Dim fdg As FileDialog
    2.  
    3. 'Must set a Reference to the Microsoft Office ?X.X Object Library
    4. Set fdg = Application.FileDialog(msoFileDialogFilePicker)
    5.  
    6. With fdg
    7.   .AllowMultiSelect = False
    8.   .InitialView = msoFileDialogViewDetails
    9.     If .Show = -1 Then
    10.       Me![txtSelectedFile] = .SelectedItems(1)
    11.     End If
    12. End With
    13.  
    14. Set fdg = Nothing
Nov 30 '11 #7

NeoPa
Expert Mod 15k+
P: 31,709
And (Just to be competitive with ADezii :-D) you can tidy it even further (than the good job he's already done).

Expand|Select|Wrap|Line Numbers
  1. 'Must set a Reference to the Microsoft Office XX.X Object Library
  2. With Application.FileDialog(msoFileDialogFilePicker)
  3.     .AllowMultiSelect = False
  4.     .InitialView = msoFileDialogViewDetails
  5.     If .Show Then Me.txtSelectedFile = .SelectedItems(1)
  6. 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.
Nov 30 '11 #8

P: 12
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.
Dec 12 '11 #9

ADezii
Expert 5K+
P: 8,679
The only External Reference needed to get the Code to work is the Microsoft Office ?X.X Object Library.
Dec 12 '11 #10

100+
P: 759
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.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _
  5. "GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long
  6.  
  7. Private Type OPENFILENAME
  8.     lStructSize As Long
  9.     hwndOwner As Long
  10.     hInstance As Long
  11.     lpstrFilter As String
  12.     lpstrCustomFilter As String
  13.     nMaxCustFilter As Long
  14.     nFilterIndex As Long
  15.     lpstrFile As String
  16.     nMaxFile As Long
  17.     lpstrFileTitle As String
  18.     nMaxFileTitle As Long
  19.     lpstrInitialDir As String
  20.     lpstrTitle As String
  21.     flags As Long
  22.     nFileOffset As Integer
  23.     nFileExtension As Integer
  24.     lpstrDefExt As String
  25.     lCustData As Long
  26.     lpfnHook As Long
  27.     lpTemplateName As String
  28. End Type
  29.  
  30. Public Function SelectFile(StartFolder As String) As String
  31.     Dim OpenFile As OPENFILENAME
  32.     Dim lReturn As Long
  33.     Dim sFilter As String
  34.     OpenFile.lStructSize = Len(OpenFile)
  35. '    OpenFile.hwndOwner = strform.Hwnd
  36.     sFilter = "Access 2003 (*.mdb)" & Chr(0) & "*.mdb" & Chr(0) & _
  37.       "Access 2007 (*.accdb)" & Chr(0) & "*.accdb" & Chr(0)
  38.     OpenFile.lpstrFilter = sFilter
  39.     OpenFile.nFilterIndex = 1
  40.     OpenFile.lpstrFile = String(257, 0)
  41.     OpenFile.nMaxFile = Len(OpenFile.lpstrFile) - 1
  42.     OpenFile.lpstrFileTitle = OpenFile.lpstrFile
  43.     OpenFile.nMaxFileTitle = OpenFile.nMaxFile
  44.     OpenFile.lpstrInitialDir = "StartFolder"
  45.     OpenFile.lpstrTitle = "Select a file using the Common Dialog DLL"
  46.     OpenFile.flags = 0
  47.     lReturn = GetOpenFileName(OpenFile)
  48.         If lReturn = 0 Then
  49.             MsgBox "A file was not selected!", vbInformation, "Select a file using the Common Dialog DLL"
  50.          Else
  51.             SelectFile= Trim(Left(OpenFile.lpstrFile, InStr(1, OpenFile.lpstrFile, vbNullChar) - 1))
  52.          End If
  53. End Function
Good luck !
Dec 12 '11 #11

NeoPa
Expert Mod 15k+
P: 31,709
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: HyperlinkAddress or HyperlinkSubAddress read-only for Hyperlink).
Dec 13 '11 #12

Post your reply

Sign in to post your reply or Sign up for a free account.