MS Access, Browse for file and then copy file path into text box

I had this question after viewing MS Access, Browse for file and then copy file path into text box.

I have managed to change the code so that I can select an actual file, not just a folder.

However it is only saving the file as text, not as a hyperlink; as when I click on the filepath it is not opening.

This is the code I have used:

Option Compare Database

Private Sub GetFilepath_Click()


     'Declare a variable to contain the path
     'of each selected item. Even though the path is a String,
     'the variable must be a Variant because For Each...Next
     'routines only work with Variants and Objects.
     Dim vrtSelectedItem As Variant

     'Use a With...End With block to reference the FileDialog object.
     With Application.FileDialog(3)  '3=msoFileDialogFilePicker

             
          'Allow the selection of multiple file.
         .AllowMultiSelect = False
         .InitialFileName = "O:\PC Die\Technicians\DATABASE - DO NOT DELETE\ModSheets"


         'Use the Show method to display the File Picker dialog box and return the user's action.
         'The user pressed the action button.
         If .Show = -1 Then

             'Step through each string in the FileDialogSelectedItems collection
             For Each vrtSelectedItem In .SelectedItems

                 'vrtSelectedItem is a String that contains the path of each selected item.
                 'You can use any file I/O functions that you want to work with this path.
                 'This example simply displays the path in a message box.
                  Me.ModSheet = vrtSelectedItem

             Next vrtSelectedItem
         'The user pressed Cancel.
         Else
         End If
     End With

     
End Sub
Thomas ReidMaintenance TechnicianAsked:
Who is Participating?
 
Gustav BrockConnect With a Mentor CIOCommented:
Set property IsLink to True.

A similar question was answered here:

Adding attachments into Access database
0
 
Dale FyeCommented:
generally, I don't save data as hypertext, but if you want to do that, you need to configure that field in the table as hypertext.

Personally, I store the path or file name as a text string, and use the Application.FollowHyperlink  method to actually open that file, either putting that method in the double-click event of the textbox where the filename is stored, or in a command button to the right of the textbox.

Dale
0
 
Thomas ReidMaintenance TechnicianAuthor Commented:
Hi Gustav Brock,

In the properties of the text box, its is already set as a link.

However I have noticed if I go into the table and cut and paste the filepath it appears to have two '#' after the filepath such as:

C:\Users\Thomas\Desktop\Archery Instructor Handbook.pdf##

Removing these allows the link to work, I don't know if this has any significance?
However I would like it so that I dont have to go in manually and remove these as this database is being designed for non computer friendly users!!

Many thanks
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Those hashmarks are put in if you set the Datatype to Hyperlink. If you change that, then you'll have to modify the existing data, but new data put in by users should not include those hashmarks.
0
 
Gustav BrockCIOCommented:
You have mixed it up. Please pay notice to the last comments of the thread referred to above.
The syntax for the field content is:

    DisplayValue#LinkValue#

For example:

    "Archery Instructor Handbook#C:\Users\Thomas\Desktop\Archery Instructor Handbook.pdf#"
0
 
Thomas ReidMaintenance TechnicianAuthor Commented:
Thanks guys,

I managed to get it sorted using comments on this post and post referenced earlier on.

Something to do with the octothorpe being incorrect. Now sorted.
0
 
Thomas ReidMaintenance TechnicianAuthor Commented:
For reference this is the final code:
Private Sub GetFilepath_Click()


     'Declare a variable to contain the path
     'of each selected item. Even though the path is a String,
     'the variable must be a Variant because For Each...Next
     'routines only work with Variants and Objects.
     Dim vrtSelectedItem As Variant

     'Use a With...End With block to reference the FileDialog object.
     With Application.FileDialog(3)  '3=msoFileDialogFilePicker

             
          'Allow the selection of multiple file.
         .AllowMultiSelect = False
         .InitialFileName = "O:\PC Die\Technicians\DATABASE - DO NOT DELETE\ModSheets"


         'Use the Show method to display the File Picker dialog box and return the user's action.
         'The user pressed the action button.
         If .Show = -1 Then

             'Step through each string in the FileDialogSelectedItems collection
             For Each vrtSelectedItem In .SelectedItems

                 'vrtSelectedItem is a String that contains the path of each selected item.
                 'You can use any file I/O functions that you want to work with this path.
                 'This example simply displays the path in a message box.
                  Me.ModSheet = vrtSelectedItem

             Next vrtSelectedItem
         'The user pressed Cancel.
         Else
         End If
         
         If .SelectedItems.Count = 1 Then
            Me!ModSheet.Value = "#" & .SelectedItems.Item(1) & "#"
        End If

     End With

     
End Sub

Open in new window

0
 
Gustav BrockCIOCommented:
Thanks for the feedback.
0
 
Gustav BrockCIOCommented:
With some help, yes.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I agree. I believe Gustav's comment (https://www.experts-exchange.com/questions/29094603/MS-Access-Browse-for-file-and-then-copy-file-path-into-text-box.html#a42533858) was helpful in resolving your issue, since he showed you the correct format for storing this type of data. You can certainly select your own comment as the answer, but you should also accept Gustav's comment as an Assisted Solution.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.