By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,635 Members | 2,187 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,635 IT Pros & Developers. It's quick & easy.

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

Expert 100+
P: 446
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.

Expand|Select|Wrap|Line Numbers
  1. If .Show Then
  2.     For Each varItem In .SelectedItems 'There will only be 1
  3.  
  4.         'Replaced the original code marked with *, with this
  5.         'to produce different Caption and Address
  6.         Me!Hlink = Me!HCaption & "#" & varItem
  7.  
  8.       ''*Caption and Address of Hyperlink will be the same (Caption#Address)
  9.       '*strHyperlinkFile = varItem & "#" & varItem
  10.       '*Me!Hlink = strHyperlinkFile
  11.  
  12.     Next varItem
  13.   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.
Nov 5 '09 #1
Share this Question
Share on Google+
10 Replies


Expert 100+
P: 1,287
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.
Nov 5 '09 #2

ADezii
Expert 5K+
P: 8,638
@sierra7
Hello sierra7, I had no problem implementing this logic. Is the Control Source for [HLink] a Hyperlink Data Type?
Nov 5 '09 #3

Expert 100+
P: 1,287
Here's how I've got this working. With an unbound textbox (txtLink) set
Display As Hyperlink = If Hyperlink

I have a button for:
Expand|Select|Wrap|Line Numbers
  1.     Dim fDialog As Object
  2.     Dim varFile As Variant
  3.     Dim strFileName As String
  4.  
  5.     Set fDialog = Application.FileDialog(3) 'msoFileDialogFilePicker)
  6.  
  7.     With fDialog
  8.         .AllowMultiSelect = False
  9.         .Title = "Please select the input file"
  10.         .Filters.Clear
  11.         If .Show = True Then
  12.             For Each varFile In .SelectedItems
  13.                 strFileName = varFile
  14.             Next
  15.         Else
  16.             Exit Sub
  17.         End If
  18.     End With
  19.  
  20.     Set fDialog = Nothing
  21.     Set varFile = Nothing
  22.  
  23.     HLink = txtLink & "#" & strFileName
  24.     Me.Dirty = False
  25.     txtLink.IsHyperlink = True
  26.     txtLink = HLink
Now, if you want the caption to show properly when you move to a record:
Expand|Select|Wrap|Line Numbers
  1. 'Didn't test this
  2. Private Sub Form_Current()
  3.     If HLink > "" Then
  4.         txtLink.IsHyperlink = True
  5.         txtLink = Left(HLink, InStr(HLink, "#") - 1)
  6.     Else
  7.         txtLink.IsHyperlink = False
  8.         txtLink = ""
  9.     End If
  10. End Sub
Nov 5 '09 #4

Expert 100+
P: 446
@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
Nov 5 '09 #5

Expert 100+
P: 1,287
Sorry, that Form_Current code isn't right. All you really have to do is:
Expand|Select|Wrap|Line Numbers
  1. ...
  2.     If HLink > "" Then 
  3.         txtLink.IsHyperlink = True 
  4.         txtLink = HLink
  5. ...
I was trying to illustrate how you could get just the caption, and I put it in the wrong place.
Nov 5 '09 #6

Expert 100+
P: 446
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
Nov 6 '09 #7

ADezii
Expert 5K+
P: 8,638
@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.
    Expand|Select|Wrap|Line Numbers
    1. Dim strHyperlink As String
    2.  
    3. strHyperlink = Me!HCaption & "#" & varItem
    4.  
    5. If HyperlinkPart(strHyperlink, acAddress) <> "" Then      'It is a Hyperlink
    6.   Me![Link] = strHyperlink
    7. Else
    8.   Me![NoLink] = strHyperlink
    9. 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.
Nov 6 '09 #8

NeoPa
Expert Mod 15k+
P: 31,495
I recently started playing in this area myself. You may find Error 7980: HyperlinkAddress or HyperlinkSubAddress read-only for Hyperlink helpful.
Nov 8 '09 #9

Expert 100+
P: 446
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.
Nov 9 '09 #10

NeoPa
Expert Mod 15k+
P: 31,495
@sierra7
Thanks :)

The full explanation can be found in Using "&" and "+" in WHERE Clause.
Nov 9 '09 #11

Post your reply

Sign in to post your reply or Sign up for a free account.