Inserting Hyperlink into a table

[AllusiveKitten]

Hi all

I have an issue inserting a hyperlink into a table. I have all of the detail going into the field but it is going in as only the text of the hyperlink not the file address.

How do I get the Record to read eg QSTR001 (this is the actual file name eg QSTR001.pdf) and the actual file path comes from a file picker which is working fantastically.

The coding that I have so far is:

Set db = CurrentDb()
    updatesql = "UPDATE Tbl_DespactchedDocuments SET Tbl_DespactchedDocuments.TransmittalRtn = Yes, Tbl_DespactchedDocuments.TransmittalRtnDate = Now(), Tbl_DespactchedDocuments.TransmittalRtnHLink = '" & Txt_HyperLink & "' WHERE (((Tbl_DespactchedDocuments.IssueNo)='" & Txt_IssueNo & "'));"
    DoCmd.SetWarnings False
    DoCmd.RunSQL (updatesql)
    DoCmd.SetWarnings True

Thx for the help
AK

[nico5038]

Are you sure using the right string.
This url describes you need to use a string with a leading http://:
http://office.microsoft.com/en-us/ac...698221033.aspx

Hyperlink

You can enter any data in this type of field, and Access wraps it in a Web address. For example, if you type a value in the field, Access surrounds your text with Uniform Resource Locator (URL) (Uniform Resource Locator (URL): An address that specifies a protocol (such as HTTP or FTP) and a location of an object, document, World Wide Web page, or other destination on the Internet or an intranet, for example: http://www.microsoft.com/.) text, like so: http://www.your_text.com. If you enter a valid Web address, your link will work — otherwise, your link will result in an error message. Also, editing existing hyperlinks can be difficult because clicking a hyperlink field with your mouse starts your Web browser and takes you to the site specified in the link. To edit a hyperlink field, you select an adjacent field, use the TAB or arrow keys to move the focus to the hyperlink field, and then press F2 to enable editing.

Nic;o)

[AllusiveKitten]

Hi Nico,

I am not trying to post a web address, I am trying to insert a hyperlink to a document. When the address is entered it is all looking correct, but when I go into view the Hyperlink, the information that has gone into the field is sitting in the Text of the Hyperlink but nothing is in the actual Hyperlink Address.

[ADezii]

Hi all

I have an issue inserting a hyperlink into a table. I have all of the detail going into the field but it is going in as only the text of the hyperlink not the file address.

How do I get the Record to read eg QSTR001 (this is the actual file name eg QSTR001.pdf) and the actual file path comes from a file picker which is working fantastically.

The coding that I have so far is:

Set db = CurrentDb()
    updatesql = "UPDATE Tbl_DespactchedDocuments SET Tbl_DespactchedDocuments.TransmittalRtn = Yes, Tbl_DespactchedDocuments.TransmittalRtnDate = Now(), Tbl_DespactchedDocuments.TransmittalRtnHLink = '" & Txt_HyperLink & "' WHERE (((Tbl_DespactchedDocuments.IssueNo)='" & Txt_IssueNo & "'));"
    DoCmd.SetWarnings False
    DoCmd.RunSQL (updatesql)
    DoCmd.SetWarnings True

Thx for the help
AK

When you add data to a Field that is of the Hyperlink Data Type, you can specify up to 3 Sections delimited by # signs. These Sections are

1. Text to display
2. Address
3. Sub Address

The following SQL Statement will update all Records in the [MyHyperlink] Field (Data Type = Hyperlink) in tblTest to 'Yahoo Home Page#http://www.yahoo.com'. Only 'Yahoo Home Page' (Section #1) will be visible and if you click on this Hyperlink, you'll navigate to 'http://www.yahoo.com' (Section #2). Hope this solves your problem.

Dim MySQL As String
 
MySQL = "Update tblTest Set tblTest.[MyHyperlink] = 'Yahoo Home Page#http://www.yahoo.com'"
 
DoCmd.RunSQL MySQL

[AllusiveKitten]

When you add data to a Field that is of the Hyperlink Data Type, you can specify up to 3 Sections delimited by # signs. These Sections are

1. Text to display
2. Address
3. Sub Address

The following SQL Statement will update all Records in the [MyHyperlink] Field (Data Type = Hyperlink) in tblTest to 'Yahoo Home Page#http://www.yahoo.com'. Only 'Yahoo Home Page' (Section #1) will be visible and if you click on this Hyperlink, you'll navigate to 'http://www.yahoo.com' (Section #2). Hope this solves your problem.

Dim MySQL As String
 
MySQL = "Update tblTest Set tblTest.[MyHyperlink] = 'Yahoo Home Page#http://www.yahoo.com'"
 
DoCmd.RunSQL MySQL

Hi ADezii

I am really not getting this one at all, I think I must be confusing myself, how do I incorporated all of the data of the hyperlink into a update code?

Thank you for you help
AK

[ADezii]

Hi ADezii

I am really not getting this one at all, I think I must be confusing myself, how do I incorporated all of the data of the hyperlink into a update code?

Thank you for you help
AK

Hello AK, lets start from the beginning. My assumption is that you are trying to Update a specific Field in a Table. This Field is of the Hyperlink Data Type, and when you click on this Field, you want to Navigate to a specific Web Page. Is this corect so far? If it is:

1. What is the Field Name?
2. Is this Field defined as a Hyperlink Data Type in the Table?
3. What is the Table's Name?
4. What URL are you trying to Update the Field to? (Microsoft.com, Google.com, TheScripts.com, etc.).
5. Is there any Criteria for other Fields for this Update process?
6. What are those other Field Names, and what are the Criteria?
7. If my assumptions are incorrect, please tell me, then explain in detail exactly what you are trying to accomplish.

[AllusiveKitten]

Hi ADezzi

I will firstly say "Your are definately worth you weight in gold".

OK from the start, you are correct in assuming I am trying to Update a specific Field in a Table, yes it is a Hyperlink Data Type, and when I click on this field I want the "document" to open not navigate to a Web Page.

I am getting the address of the document from a msoFileDialogFilePicker which is being inserted into a text box.

1. Field Name: TransmittalRtnHLink
2. The Field is definated a a Hperlink Data Type
3. Table Name: Tbl_DespactchedDocuments
4. Trying to update the file address from text box: Txt_HyperLink
5 & 6. Criteria: WHERE IssueNo (from the same table) is equal to Txt_IssueNo

I really hope this is clear, thanks again
AK

[ADezii]

Hi ADezzi

I will firstly say "Your are definately worth you weight in gold".

OK from the start, you are correct in assuming I am trying to Update a specific Field in a Table, yes it is a Hyperlink Data Type, and when I click on this field I want the "document" to open not navigate to a Web Page.

I am getting the address of the document from a msoFileDialogFilePicker which is being inserted into a text box.

1. Field Name: TransmittalRtnHLink
2. The Field is definated a a Hperlink Data Type
3. Table Name: Tbl_DespactchedDocuments
4. Trying to update the file address from text box: Txt_HyperLink
5 & 6. Criteria: WHERE IssueNo (from the same table) is equal to Txt_IssueNo

I really hope this is clear, thanks again
AK

1. Here is some sample code for a File Dialog (msoFileDialogFilePicker) for reference purposes. I'll make the assumption that Multiselect = True which it should be under the circumstances.
   

   Dim varItem As Variant
    
   With Application.FileDialog(msoFileDialogFilePicker)
      With .Filters
        .Clear
        .Add "Text Files", "*.txt"
        .Add "Database Files", "*.mdb"
        .Add "All Files", "*.*"
      End With
          'The Show Method returns True if 1 or more files are selected
          .AllowMultiSelect = True
          .FilterIndex = 2     'Database files
          .ButtonName = "Open Sesame"
          .InitialFileName = vbNullString
          .InitialView = msoFileDialogViewDetails
          .Title = "Find the damn File!"
             If .Show Then
               For Each varItem In .SelectedItems     'There will only be 1
                 Me![Txt_HyperLink] = varItem
               Next varItem
             End If
   End With

2. Set the Record Source of your Form to Tbl_DespactchedDocuments or a Query based on this Table.
3. Set the Control Source of the [Txt_HyperLink] Field to TransmittalRtnHLink.
4. Transfer the Absolute Path from the File selected by the File Dialog to Me!Txt_HyperLink as displayed in Line #19.
   

   Me![Txt_HyperLink] = varItem

5. Since Txt_HyperLink is Bound to the TransmittalRtnHLink Field, the value will permanently be stored in the underlying Table (Tbl_DespactchedDocuments).
6. Specifying the Criteria = Me![Txt_IssueNo] would be meaningless since the value in this Field as well as the value in [Txt_HyperLink] will be saved together in the same Record.
7. By Default, the Text that will be displayed as well as the Path to the Document will be exactly the same. We can change that later, but for now, let's get past this hurdle.