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?
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() & "#"
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!
