Access 2007 VBA - dynamic hyperlinks

I've got a report that is supposed to show a listing of files.  This listing of files is contained within a table in my database and I have the data coming down to the report correctly.  I have a file name and path to the file on the computer or network where the file is located.  I would like to display only the name of the file and then when someone clicks on the file name it open the file for them.  I'm inside the onclick event of a textbox in the detail portion of the report that is showing the file name.  The textbox is set to appear as a hyperlink.  In code, when this textbox is clicked, how do I access the location field of the same record from the database.  Again, the location field is already part of the report.  The data is present, I'm just now sure how to access the right location (or path) for a given filename that has been clicked on.

Who is Participating?
omgangConnect With a Mentor IT ManagerCommented:
So, you have a text box for the file name and a text box/field for Location.

Private Sub FileName_Click()
On Error GoTo Err_FileName_Click

    Dim strLink As String
    strLink = Me.Location & Me.FileName
    Application.FollowHyperlink strLink, , True     '<--- use True to open in a new window, False to open in same window

    Exit Sub

    MsgBox Err.Number & ", " & Err.Description, , "Error"
    Resume Exit_FileName_Click

End Sub

OM Gang
omgangIT ManagerCommented:
What are the control/field names on your report?
OM Gang
JosephEricDavisAuthor Commented:
The field in the current record that I'm trying to access is called Location.
omgangIT ManagerCommented:
You may need to tweak the link string a bit, e.g. if your location field contains

and the FileName field contains

You'll need to do this
strLink = Me.Location & "\" & Me.FileName

OM Gang
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.