asked on

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

Dale Fye

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

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Thomas Reid

ASKER

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

Scott McDaniel (EE MVE )

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.

Gustav Brock

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#"

Thomas Reid

ASKER

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.

Thomas Reid

ASKER

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

Gustav Brock

Thanks for the feedback.

Gustav Brock

With some help, yes.

Scott McDaniel (EE MVE )

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?anchorAnswerId=42533858#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.