You are here:

Using MS Access/Hyperlinking in Access using VBA


 You aided me greatly in writing the below code to save files to an external shared drive via an Access Form. I was wondering if you had any insight into what I may be missing in my attempt to retain the displayed name of the file in the field (ie: ExampleTraining.pdf)while hyperlinking the name to the location of the file on the shared drive when it is saved into the table?

After implementing the coding to save the file as, I started researching how to automatically place a hyperlink to the document onto the displayed name of the document.  The last few lines (prefaced by HYPERLINK CODE) are where I've become lost.  Any help is appreciated.

Private Sub AdminDocLink_DblClick(Cancel As Integer)
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
'the number of the button chosen
Dim FileChosen As Integer
FileChosen = fd.Show
'1) To set the caption of the dialog box,
' set the Title property
fd.Title = "Select Admin Document to Upload"
'2) Set the oddly named InitialFileName property to
' determine the initial folder selected
fd.InitialFileName = "C:\Users\swsims\Documents"
'3) Set the InitialView property to control how your files
' appear on screen (as a list, icons, etc.)
fd.InitialView = msoFileDialogViewSmallIcons
'5) Set the ButtonName property to control the text on
' the OK button (the ampersand means the following
' letter is underlined and choosable with the ALT key)
fd.ButtonName = "Choose PDF file"
If FileChosen <> -1 Then
'didn't choose anything (clicked on CANCEL)
MsgBox "Upload Cancelled"
'display name and path of file chosen
MsgBox fd.SelectedItems(1)
End If
'store the selected file to a variable
strSelectedFile = fd.SelectedItems(1)
'parse out the filename from the path
strFilename = Right(strSelectedFile, Len(strSelectedFile) - InStrRev(strSelectedFile, "\"))
'build the destination
strDestination = "C:\Users\swsims\Desktop\E-Pers Test\Admin Docs\" & strFilename
'HYPERLINK CODE-retrieve the address
strAddress = Application.HyperlinkPart(Me.AdminDocLink, acAddress)
'copy the file to the new folder
FileCopy strSelectedFile, strDestination
'HYPERLINK CODE-edit the "Display Text" property for that hyperlink & store file as part of the record
Me.AdminDocLink = strFilename & strAddress
End Sub

ANSWER: I assume you are currently using a folder on the local drive just for testing purposes for your strDestination.

Where did you find the line:

strAddress = Application.HyperlinkPart(Me.AdminDocLink, acAddress)

I believe that is unnecessary and actually doesn't belong in this part of your process. You don't need to use a Hyperlink datatype and I've found them to be a bit flaky.

The code above should be used to select the file, copy it into a new location and store it into a text field in your table. That's all it should do. The process of opening the file should be elsewhere. And all you need for that is one line of code:

Application.FollowHyperlink fullpath

Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

P.S. thanks for the kind words in your comments.

---------- FOLLOW-UP ----------

QUESTION: You're correct on the strDestination.

I found the strAddress line while researching how to automate making the file name a hyperlink to the strDestination.  I get the feeling that I may not be understanding the most optimal way to use the hyperlink with VBA, which may explain my struggle to uncover the correct coding. My intent is to have reports that list the file names (along with date/doc type etc) so that managers can pull up docs as required for their personnel via the hyperlink. I assumed that attaching the hyperlink to the file name had to take place in the upload procedure above.  

I have a version where the field is a text datatype, but have no idea where to put the code you recommended.  Would it be a separate Private sub?

Your help has been unequalled in this endeavor.  Thanks again.

You can put that code behind a button on a form.

If you want to put these on reports how are you going to distribute the reports?

Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers

Answers by Expert:

Ask Experts




I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.


I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Brooklyn College BA

©2017 All rights reserved.