Access - Hyperlinks Aren't Linking

2
votes

I have a form with a user created function (See here for function: Adding a file browser button to a MS Access form) to pull a folder path name into a text box.

Ideally, I want this text to become a hyperlink that can be opened from a query. However, for all records I have added from the form the hyperlink is not working. I looked in "Edit Hyperlink" for the records and there is no address. Also, I had manually entered some records in before I had made the form and their hyperlinks worked just fine by pasting the folder path name into the cell, and still work now.

I think the issue is that the function I am using returns a plain string, is there any way to transform it into a working hyperlink once it is in the table?

2
ms-accesshyperlinkvbatransform

2 Answers

3
votes

Roughly speaking, a functional Access Hyperlink field value is a string which includes # characters between the "hyperlink parts". (See Access' Application.HyperlinkPart Method help topic.)

For example, this DLookup returns the stored value for a link which is displayed as AccessApps and which links to my C:\Users\hans\AccessApps folder.

? DLookup("url", "tblFoo", "id=1")
AccessApps#C:\Users\hans\AccessApps#

However, if I store only the string which contains the folder path ...

CurrentDb.Execute "UPDATE tblFoo " & _
    "SET url='C:\Users\hans\AccessApps' " & _
    "WHERE id=4"

... the string does get stored ...

? DLookup("url", "tblFoo", "id=4")
C:\Users\hans\AccessApps

... but since it doesn't include # characters, Access doesn't treat it as a clickable hyperlink. It's just text which looks like a link, but clicking it does nothing.

Meanwhile it seems you're using a function named FolderSelection() to select a folder path string. If you want to store the function's return value as a hyperlink, you can include # characters before and after.

"#" & FolderSelection() & "#"

If you want the folder path as the link but have it displayed as something else, put the display part before the first #.

"Click Me!#" & FolderSelection() & "#"
1
votes

Perfect, it works exactly how I wanted it to! For anyone interested, here is the edited code:

Private Sub Command26_Click()
Dim strChoice As String
strChoice = FolderSelection
If Len(strChoice) > 0 Then
    Me.Photo_Location = "#" & strChoice & "#"
    ' the "#" & additions around strChoice above complete the hyperlink
Else
    ' what should happen if user cancelled selection?
End If
End Sub

I guess the # character could have been placed somewhere else, but I am a new to this!