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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
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
Gustav BrockCIOCommented:
Set property IsLink to True.

A similar question was answered here:

Adding attachments into Access database
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.