How to save a Caption for a Hyperlink, different from the Hyperlink itself?

[sierra7]

I'm trying to create a form where a user enters a document reference number and then clicks a command button to add a hyperlink to the document, via a file picker.

Thanks to Adezii's excellent article http://bytes.com/topic/access/answer...ink-into-table I can manage to create a hyperlink to the document and store it in the table, but it is the full hyperlink path not the reference number of the document that is displayed.

I have a workaround at present, where I have overlaid a second textbox [HCaption] over the one holding the hyperlink [Hlink]. If I type my document reference into [HCaption] box, then click the command button I can programatically add the 'caption' to the selected item to create the hyperlink, by modifying Adezii's code as below.

If .Show Then
    For Each varItem In .SelectedItems 'There will only be 1
 
        'Replaced the original code marked with *, with this
        'to produce different Caption and Address
        Me!Hlink = Me!HCaption & "#" & varItem
 
      ''*Caption and Address of Hyperlink will be the same (Caption#Address)
      '*strHyperlinkFile = varItem & "#" & varItem
      '*Me!Hlink = strHyperlinkFile
 
    Next varItem
  End If

I then have to make [HCaption] Visible = False so that the user so that the user can click on the hyperlink box if it is populated. This works but I feel is messy; I have overlaid boxes and I have to keep two fields on the table.

I should add, I spent some time using just the [Hlink] text box and trying to enter the caption directly into that but Access seems to append 'http:\' or whatever, depending where the target is, and it does not work.

On the up-side, not all documents are scanned and available via a hyperlink and this is clear because they show as black text rather than blue underlined text. But I would welcome any comments on how to improve this.

The massive advantage of a hyperlink is that the document can be in any format that the default browser can interpret, so you don't have to read the file extension to know what program to shell out to.

[ChipR]

I find this interesting, since I haven't used hyperlinks yet. I'm going to test some things today or tomorrow and get back to you.

[ADezii]

@sierra7
Hello sierra7, I had no problem implementing this logic. Is the Control Source for [HLink] a Hyperlink Data Type?

[ChipR]

Here's how I've got this working. With an unbound textbox (txtLink) set
Display As Hyperlink = If Hyperlink

I have a button for:

    Dim fDialog As Object
    Dim varFile As Variant
    Dim strFileName As String
 
    Set fDialog = Application.FileDialog(3) 'msoFileDialogFilePicker)
 
    With fDialog
        .AllowMultiSelect = False
        .Title = "Please select the input file"
        .Filters.Clear
        If .Show = True Then
            For Each varFile In .SelectedItems
                strFileName = varFile
            Next
        Else
            Exit Sub
        End If
    End With
 
    Set fDialog = Nothing
    Set varFile = Nothing
 
    HLink = txtLink & "#" & strFileName
    Me.Dirty = False
    txtLink.IsHyperlink = True
    txtLink = HLink

Now, if you want the caption to show properly when you move to a record:

'Didn't test this
Private Sub Form_Current()
    If HLink > "" Then
        txtLink.IsHyperlink = True
        txtLink = Left(HLink, InStr(HLink, "#") - 1)
    Else
        txtLink.IsHyperlink = False
        txtLink = ""
    End If
End Sub

[sierra7]

@ADezii
Yes, [HLink] is a hyperlink data type and I added [HCaption] as Text when I could not get the result I wanted.

With hindsight I am warming to this solution because, as I said in my original post, it is clear which records have a hyperlinked document (a scanned certificate) and which don't. This is useful. I don't want to use two fields both displaying the same data.

Also with hindsight it was silly typing into a hypertext field and expecting to get text out. After leaving the field then tabbing back and pressing F2 to edit (and Shift+F2) you can see what Access has added. I tried InStr() for '#' on [HLink].Value, then substringing but it was a mess.

I need to read what ChipR is saying

[ChipR]

Sorry, that Form_Current code isn't right. All you really have to do is:

...
    If HLink > "" Then 
        txtLink.IsHyperlink = True 
        txtLink = HLink
...

I was trying to illustrate how you could get just the caption, and I put it in the wrong place.

[sierra7]

Hi ChipR, thanks for your time
Yes I'm new to using hyperlinks in Access too and finding it a little trickier that I imagined.

You chose to enter the 'caption' via an unbound text box which work well if there is always a file to link to. However, my 'caption' is a Certificate Number and sometimes there is not a document to link to until it is scanned. To persist this value until a file is available I must store it in the table. I thought I could store it in the hypertext field, but then it appears blue and is underlined, hence the additional field.

I have tried your idea of setting the 'IsHyperLink' property of a textbox but have not had results I expected. i.e. if the underlying field is hyperlink data type the text is still underlined and blue when IsHyperlink is False and will still link (!?). If the underlying field is Text holding a valid hyperlink text string, then the display can be toggles to show just the caption part by making IsHyperlink True; aTool-tip also appears but will never link.

It looks as though I must rely on my OnCurrent event to toggle the Visible property of the two text boxes if [HLink] holds a string (containing a #)

Thanks again for your time

[ADezii]

@sierra7
Hello sierra7, I think I have a crude, workable solution to your unique circumstance, but only you can decide for sure:

1. Create 2 Fields in your Primary Table, or use existing ones.
   1. [Link] {HYPERLINK} - will contain the actual Hyperlink itself.
   2. [NoLink] {TEXT} - will display Caption only if Address Component does not exist.
2. Create 2 Text Boxes Bound to this Fields and make them Invisible (New Record).
3. Examine your concatenated Value and determine if it is a Hyperlink or not. If it is, write to the Hyperlink Field, if not write to the Text Field.
   

   Dim strHyperlink As String
    
   strHyperlink = Me!HCaption & "#" & varItem
    
   If HyperlinkPart(strHyperlink, acAddress) <> "" Then      'It is a Hyperlink
     Me![Link] = strHyperlink
   Else
     Me![NoLink] = strHyperlink
   End If

4. The code is crude and incomplete, but it is only the concept that I wanted to bring across.
5. Similar functionality can be used in the Form's Current() Event in order to display/overlay the proper Control.

[NeoPa]

I recently started playing in this area myself. You may find Error 7980: HyperlinkAddress or HyperlinkSubAddress read-only for Hyperlink helpful.

[sierra7]

Hi Guys
Thanks for all your suggestions.

ADezii, I shall check out the HyperlinkPart() function to see if I can make use of it as it is new to me. I did not have much luck using 'IsHyperlink'.

NeoPa, I had read the Allen Brown article to get to my solution, although I find that the hyperlink works ok without the trailing hash '#'. It was intesting to see he uses a double hash '##' to refer to a table or form within ACCESS. I liked your code to handle Nulls.

[NeoPa]

@sierra7
Thanks :)

The full explanation can be found in Using "&" and "+" in WHERE Clause.