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

Inserting Hyperlink into a table

AllusiveKitten
P: 43
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:
Expand|Select|Wrap|Line Numbers
  1. Set db = CurrentDb()
  2.     updatesql = "UPDATE Tbl_DespactchedDocuments SET Tbl_DespactchedDocuments.TransmittalRtn = Yes, Tbl_DespactchedDocuments.TransmittalRtnDate = Now(), Tbl_DespactchedDocuments.TransmittalRtnHLink = '" & Txt_HyperLink & "' WHERE (((Tbl_DespactchedDocuments.IssueNo)='" & Txt_IssueNo & "'));"
  3.     DoCmd.SetWarnings False
  4.     DoCmd.RunSQL (updatesql)
  5.     DoCmd.SetWarnings True
Thx for the help
AK
Oct 19 '07 #1
Share this Question
Share on Google+
7 Replies


nico5038
Expert 2.5K+
P: 3,072
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)
Oct 19 '07 #2

AllusiveKitten
P: 43
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.
Oct 21 '07 #3

ADezii
Expert 5K+
P: 8,638
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:
Expand|Select|Wrap|Line Numbers
  1. Set db = CurrentDb()
  2.     updatesql = "UPDATE Tbl_DespactchedDocuments SET Tbl_DespactchedDocuments.TransmittalRtn = Yes, Tbl_DespactchedDocuments.TransmittalRtnDate = Now(), Tbl_DespactchedDocuments.TransmittalRtnHLink = '" & Txt_HyperLink & "' WHERE (((Tbl_DespactchedDocuments.IssueNo)='" & Txt_IssueNo & "'));"
  3.     DoCmd.SetWarnings False
  4.     DoCmd.RunSQL (updatesql)
  5.     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.

Expand|Select|Wrap|Line Numbers
  1. Dim MySQL As String
  2.  
  3. MySQL = "Update tblTest Set tblTest.[MyHyperlink] = 'Yahoo Home Page#http://www.yahoo.com'"
  4.  
  5. DoCmd.RunSQL MySQL
Oct 21 '07 #4

AllusiveKitten
P: 43
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.

Expand|Select|Wrap|Line Numbers
  1. Dim MySQL As String
  2.  
  3. MySQL = "Update tblTest Set tblTest.[MyHyperlink] = 'Yahoo Home Page#http://www.yahoo.com'"
  4.  
  5. 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
Oct 22 '07 #5

ADezii
Expert 5K+
P: 8,638
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.
Oct 22 '07 #6

AllusiveKitten
P: 43
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
Oct 22 '07 #7

ADezii
Expert 5K+
P: 8,638
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.
    Expand|Select|Wrap|Line Numbers
    1. Dim varItem As Variant
    2.  
    3. With Application.FileDialog(msoFileDialogFilePicker)
    4.    With .Filters
    5.      .Clear
    6.      .Add "Text Files", "*.txt"
    7.      .Add "Database Files", "*.mdb"
    8.      .Add "All Files", "*.*"
    9.    End With
    10.        'The Show Method returns True if 1 or more files are selected
    11.        .AllowMultiSelect = True
    12.        .FilterIndex = 2     'Database files
    13.        .ButtonName = "Open Sesame"
    14.        .InitialFileName = vbNullString
    15.        .InitialView = msoFileDialogViewDetails
    16.        .Title = "Find the damn File!"
    17.           If .Show Then
    18.             For Each varItem In .SelectedItems     'There will only be 1
    19.               Me![Txt_HyperLink] = varItem
    20.             Next varItem
    21.           End If
    22. 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.
    Expand|Select|Wrap|Line Numbers
    1. 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.
Oct 22 '07 #8

Post your reply

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