1

I currently have a database set up to store the paths of pictures associated with my data and display them in imageframes with VBA. It works great, but the process of adding a picture is a bit tedious, and users struggle to use it correctly(type the wrong path, forget to include the extension, etc). This results in a bunch of garbage entries in the database. I would like to simplify this process. Ideally, when "add picture" is clicked, I would like for it to open up windows explorer, have the user select the desired picture, get the path of that picture, and insert it into the table. Again, I'm not using an OLE, just a text field for the path. Is this possible?

Scotch
  • 3,186
  • 11
  • 35
  • 50
  • 1
    See http://stackoverflow.com/questions/9105806/how-do-i-store-the-links-to-an-attachment-in-ms-access/9105887#9105887 – Fionnuala Jul 09 '12 at 15:52
  • @Remou this appears to be what I need to accomplish this. I'm going to try to implement it after lunch. I'll post an update and what I did when I am finished for future reference. – Scotch Jul 09 '12 at 15:57
  • It's giving a compiler error. "user defined type not defined" at Dim dlgOpen As FileDialog – Scotch Jul 09 '12 at 16:50
  • It was first necessary to add references to the Microsoft Office Object Library. It is now opening the windows explorer and allowing me to select things. My question is--How to I get the Path of the selected item onto a control for my form? – Scotch Jul 09 '12 at 17:10

1 Answers1

2

As Remou referenced, the FileDialog object can be used to accomplish this. For many people, it may be necessary to add references to the MS Office Object Library(The Access Library is not sufficient). The code that I used to collect a path name from a file selected in explorer is as follows:

 Public Sub ShowFileDialog()
 Dim dlgOpen As FileDialog
 Set dlgOpen = Application.FileDialog(msoFileDialogOpen)
 With dlgOpen
 .AllowMultiSelect = False
 .InitialFileName = "Z:\" 'Initial Path when explorer is opened
 .Show
     If .SelectedItems.Count = 0 Then
       MsgBox ("No file Selected") 'No file selected
     Else
        Me.txtPath = .SelectedItems(1) 'sets textbox on the form to the path selected
     End If
  End With


 End Sub
Scotch
  • 3,186
  • 11
  • 35
  • 50
  • Thanks hans. I stumbled on one of your answers from a similar question http://stackoverflow.com/questions/9476268/filedialog-doesnt-work-in-access-2010 there. It helped me out quite a bit. As you can see in my edit, I'd like to have the absolute path of the file rather than just the file name. Do you know how to do this? – Scotch Jul 09 '12 at 17:36
  • Get rid of `Dir()` ... which returns only the file name piece from the full path you feed it. IOW, try this: `Me.txtPath = .SelectedItems(1)` – HansUp Jul 09 '12 at 17:40
  • This works. I'll edit my answer to include that - thank you. I think all of this definitively answers my question. – Scotch Jul 09 '12 at 18:03