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

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • sierra7
    Recognized Expert Contributor
    • Sep 2007
    • 446

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

    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.

    Code:
    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
    Recognized Expert Top Contributor
    • Jul 2008
    • 1289

    #2
    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.

    Comment

    • ADezii
      Recognized Expert Expert
      • Apr 2006
      • 8834

      #3
      Originally posted by 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.

      Code:
      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.
      Hello sierra7, I had no problem implementing this logic. Is the Control Source for [HLink] a Hyperlink Data Type?

      Comment

      • ChipR
        Recognized Expert Top Contributor
        • Jul 2008
        • 1289

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

        I have a button for:
        Code:
            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:
        Code:
        '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

        Comment

        • sierra7
          Recognized Expert Contributor
          • Sep 2007
          • 446

          #5
          Originally posted by ADezii
          Hello sierra7, I had no problem implementing this logic. Is the Control Source for [HLink] a Hyperlink Data Type?
          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

          Comment

          • ChipR
            Recognized Expert Top Contributor
            • Jul 2008
            • 1289

            #6
            Sorry, that Form_Current code isn't right. All you really have to do is:
            Code:
            ...
                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.

            Comment

            • sierra7
              Recognized Expert Contributor
              • Sep 2007
              • 446

              #7
              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

              Comment

              • ADezii
                Recognized Expert Expert
                • Apr 2006
                • 8834

                #8
                Originally posted by 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
                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.
                  Code:
                  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.

                Comment

                • NeoPa
                  Recognized Expert Moderator MVP
                  • Oct 2006
                  • 32636

                  #9
                  I recently started playing in this area myself. You may find Error 7980: HyperlinkAddres s or HyperlinkSubAdd ress read-only for Hyperlink helpful.

                  Comment

                  • sierra7
                    Recognized Expert Contributor
                    • Sep 2007
                    • 446

                    #10
                    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.

                    Comment

                    • NeoPa
                      Recognized Expert Moderator MVP
                      • Oct 2006
                      • 32636

                      #11
                      Originally posted by sierra7
                      I liked your code to handle Nulls.
                      Thanks :)

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

                      Comment

                      Working...