Results 1 to 15 of 15

Thread: Hyperlinks

  1. #1
    Join Date
    Oct 2013
    Posts
    163

    Unanswered: Hyperlinks

    Dear Seniors,

    I had exported the Hyperlink data from Excel to Access Table and it works fine. However in the Hyperlink path is displayed in long text. I just want to rename the hyperlink text to "Open Report".

    In excel I had used the Hyperlink function and it works fine. Is it possible to do the same in Access Tables or in Forms?

    For ex

    My Hyperlink Path is : "\\saipemnet.saipem.intranet\jakarta\PROJECT\INPEX _001_MASELA_FLNG_FEED\4.DOCUMENT CONTROL\002 - TRANSMITTAL\1. PIMS\TR to CPY\PIMS PDF Transmittal\AF09A-SAI-INM-TR-0001.pdf" ------I want to Display this as "Open Report" or "AF09A-SAI-INM-TR-0001"

    AF09A-SAI-INM-TR-0001 - Field value in Access Table

    Thanks and Regards
    R. Vadivelan

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    In the folowing example, I have a bound form with 2 controls:

    - 1 textbox named Text_HL which is bound to an hyperlink field in the table the form is bound with. This textbox is invisible.
    - 1 Label named Label_HL.

    The module of the form contains:
    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub Form_Current()
    
        Me.Label_HL.Caption = GetName(Me.Text_HL)
        
    End Sub
    
    Private Sub Label_HL_Click()
    
        FollowHyperlink Me.Text_HL.Hyperlink.Address
        
    End Sub
    
    Public Function GetName(ByVal ctl As Control) As String
    
    
        GetName = Mid(ctl.Hyperlink.Address, InStrRev(ctl.Hyperlink.Address, "\") + 1)
        GetName = Left(GetName, InStr(GetName, ".") - 1)
        
    End Function
    If the hyperlink fields contains: C:\Documents and Settings\Sinndho.AN-TI\Desktop\Library\PowerShell.chm, the caption of the label will be: PowerShell
    Have a nice day!

  3. #3
    Join Date
    Oct 2013
    Posts
    163
    Dear Sinndho,

    Thanks for your quick reply. I had followed your steps but I had struck in the middle and could not succeed.

    As you said I had created the form Text box and Label and renamed as you said.

    Quote Originally Posted by Sinndho View Post
    In the folowing example, I have a bound form with 2 controls:

    - 1 textbox named Text_HL which is bound to an hyperlink field in the table the form is bound with. This textbox is invisible.
    - 1 Label named Label_HL.

    The module of the form contains:
    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub Form_Current()
    
        Me.Label_HL.Caption = GetName(Me.Text_HL)
        
    End Sub
    
    Private Sub Label_HL_Click()
    
        FollowHyperlink Me.Text_HL.Hyperlink.Address
        
    End Sub
    
    Public Function GetName(ByVal ctl As Control) As String
    
    
        GetName = Mid(ctl.Hyperlink.Address, InStrRev(ctl.Hyperlink.Address, "\") + 1)
        GetName = Left(GetName, InStr(GetName, ".") - 1)
        
    End Function
    I had copied the code into module and saved the module. However I am not able to click anything to open the PDF file.

    If the hyperlink fields contains: C:\Documents and Settings\Sinndho.AN-TI\Desktop\Library\PowerShell.chm, the caption of the label will be: PowerShell
    Herewith I had enclosed the sample file, could you please explain the above in the attached file.

    Thanks and Regards
    R. Vadivelan
    Attached Files Attached Files

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by Sinndho View Post
    The module of the form contains:
    You have created a standard module instead of pasting the code in the Classs module of the form (and you cannot compile the project, by the way).
    Quote Originally Posted by Sinndho View Post
    In the folowing example, I have a bound form with 2 controls:

    - 1 textbox named Text_HL which is bound to an hyperlink field in the table the form is bound with. This textbox is invisible.
    - 1 Label named Label_HL.
    The label cannot be linked to the textbox, which is obvious : otherwise the label would be invisible too and you would not be able to click on it. The textbox Text_HL is visible on your form and the label Label_HL is bound to it.

    1. Move the code to the class module of the form.
    2. Break the link between the label and the textbox.
    Have a nice day!

  5. #5
    Join Date
    Oct 2013
    Posts
    163
    Thanks Sinndho,

    Finally I made it work and my file open successfully when I click the label.

    Now I have some more query, If there are multiple files linked, how it will work.

    For example, In my case, Client Document Identification is a Primary key and there are multiple transmittal's for the same document number.

    So I would like to see the List of Transmittal and when I click a particular name, then the corresponding file should open.

    Thanks and Regards
    R. Vadivelan

  6. #6
    Join Date
    Oct 2013
    Posts
    163
    Dear Sinndho,

    With help of this post, finally I had created a form and opened the file.

    Quote Originally Posted by Sinndho View Post
    You have created a standard module instead of pasting the code in the Classs module of the form (and you cannot compile the project, by the way).

    The label cannot be linked to the textbox, which is obvious : otherwise the label would be invisible too and you would not be able to click on it. The textbox Text_HL is visible on your form and the label Label_HL is bound to it.

    1. Move the code to the class module of the form.
    2. Break the link between the label and the textbox.
    However herewith I had enclosed a sample database, which contains 2 Tables and a form. Up to this is fine, Now I want to create the Hyperlink to Open the Transmittal's available from a Particular location.

    The Transmittal's are available in D:\01_Projects\05_FLNG Indonesia\DCC\TR from CPY\Filename

    I want the hyperlinks to be created for both CTR Transmittal No and CPY Transmittal No.

    If I click the CTR Transmittal No then the particular Transmittal should open externally.

    I had referred some database where the links are created to open another form, however I didn't find any sample database to open a file from a location.

    Could you please help me to sort out this issue.

    Thanks and Regards
    R. Vadivelan
    Attached Files Attached Files

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This question was already answered in this thread and in the other thread you opened on (almost) the same subject (http://www.dbforums.com/microsoft-ac...-location.html).

    From the moment you have the location of the files (e.g. D:\01_Projects\05_FLNG Indonesia\DCC\TR from CPY\) and the file name (e.g. AF09A-SAI-INM-TR-1515.pdf), you simply need to concatenate both to get the full path (or link, or hyperlink, whatever you prefer to name it) that can be used with the FollowHyperlink method:
    Code:
    Dim strLocation As String
    Dim strFileName As String
    Dim strHyperLink As String
    
    strLocation = "D:\01_Projects\05_FLNG Indonesia\DCC\TR from CPY\"
    strFileName = "AF09A-SAI-INM-TR-1515.pdf"
    strHyperLink = strLocation & strFileName 
    FollowHyperlink strHyperLink
    Have a nice day!

  8. #8
    Join Date
    Oct 2013
    Posts
    163
    Dear Sinndho,

    Sorry for asking the question again and again. In fact that thread was opened by me; however the I didnít get the 100% result. This is mainly due to fault of mine, since I am a new user to MS Access. I am using MS Access for the past 2 months I am learning the same with help of this forum and you tube videos. And I am a Zero level user in coding thatís why I am not able to transfer the replies from you to 100% result.

    It will be very much greatful for me if you reply to me in the attached file for the final time. I am trying to create a Document control database from where I can create queries and export the same to excel for reporting purpose.
    But when it comes to verify the transmittals then I am facing issues. Thatís why I had imported the hyperlinks from Excel and it works for me but the disgusting thing is entire hyperlink path is displayed in the report/Forms. I want to avoid the same and thatís why I am seeking your help.

    In my case I have more transmittals for 1 document no. If I search by document number in the form all the transmittals are displayed below, now I want to open the transmittals based on my selection. I donít know how to do this. My idea is to display the list of Transmittals in a Combo Box based on the dates, then when I select the number and press the button then the particular file.

    I can see the coding (for the above ides) in your replies, but When I copy and paste the code it is showing lot of Errors. So I request for the final time to do the coding in the attached file and I can learn the same.

    Thanks and Regards
    R.Vadivelan
    Attached Files Attached Files

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I have yet to see a server server in a path qualifed with a fullstop
    Id expect to see
    //server/path/to/file.ext
    not
    //server.path/to/file/ext
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here's what I would do:

    1. Change the data type of the columns [OPEN CPY Transmittal] and [OPEN CTR Transmittal] of the table [TblTransmittal] from HyperLink to Memo:
    Code:
    ALTER TABLE TblTransmittal ALTER [OPEN CTR Transmittal] MEMO;
    Code:
    ALTER TABLE TblTransmittal ALTER [OPEN CPY Transmittal] MEMO;
    2. Cleanup the data in these columns:
    Code:
    UPDATE TblTransmittal 
       SET TblTransmittal.[OPEN CTR Transmittal] = Replace([OPEN CTR Transmittal],'#','')
         , TblTransmittal.[OPEN CPY Transmittal] = Replace([OPEN CPY Transmittal],'#','');
    3. Rename the form TblMDRMaster to Frm_MDRMaster. It does not make sense to have a form with its name beginning by the prefix "tbl"!

    4. Create a new form:
    - Name: SF_Transmittal
    - RecordSource: TblTransmittal
    - DefaultView: Datasheet

    Note: You can use the wizard to create this form.

    5. Open the form TblMDRMaster in Design view, open the the Property windows (F4), select the subform/subreport control and change its name from Child21 to Child_Transmittal.

    6. Change it's SourceObject property from Table.TblTransmittal to SF_Transmittal.

    7. In the FormFooter section of the parent form TblMDRMaster, create two command buttons:

    First button:
    - Name: Command_OpenCTRTransmittal
    - Caption: OPEN CTR Transmittal
    - OnClick: [Event Procedure]

    Second button:
    - Name: Command_OpenCPYTransmittal
    - Caption: OPEN CPY Transmittal
    - OnClick: [Event Procedure]

    8. Open the module of the parent form TblMDRMaster (Ctrl+G) and paste this code into it:
    Code:
    Private Sub Command_OpenCPYTransmittal_Click()
    
        With Me.Child_Transmittal.Form
            If Not IsNull(![OPEN CPY Transmittal]) Then
                Application.FollowHyperlink ![OPEN CPY Transmittal]
            End If
        End With
    
    End Sub
    
    Private Sub Command_OpenCTRTransmittal_Click()
    
        With Me.Child_Transmittal.Form
            If Not IsNull(![OPEN CTR Transmittal]) Then
                Application.FollowHyperlink ![OPEN CTR Transmittal]
            End If
        End With
    
    End Sub
    Note: The database would be easier to maintain and develop if you would avoid using spaces, non alphanumeric characters (except the underscore) and reserved words in the naming scheme of the objects. e.g. OPEN_CTR_Transmittal or OpenCTRTransmittal instead of OPEN CTR Transmittal
    Attached Files Attached Files
    Have a nice day!

  11. #11
    Join Date
    Oct 2013
    Posts
    163
    Hi Healdem,

    Quote Originally Posted by healdem View Post
    I have yet to see a server server in a path qualifed with a fullstop
    Id expect to see
    //server/path/to/file.ext
    not
    //server.path/to/file/ext
    Actually my file is located in this location only.

    \\saipemnet.saipem.intranet\Jakarta\PROJECT\INPEX_ 001_MASELA_FLNG_FEED\4.DOCUMENT CONTROL\002 - TRANSMITTAL\1. PIMS\TR to CPY\PIMS PDF Transmittal\

    Thanks and Regards
    R. Vadivelan

  12. #12
    Join Date
    Oct 2013
    Posts
    163
    Thanks Sinndho for your detailed reply, steps and Tips for the naming.

    I will try to understand the coding and keep you posted for the queries. Now its working for me and it solved my problem.

    Quote Originally Posted by Sinndho View Post
    Here's what I would do:

    1. Change the data type of the columns [OPEN CPY Transmittal] and [OPEN CTR Transmittal] of the table [TblTransmittal] from HyperLink to Memo:
    Code:
    ALTER TABLE TblTransmittal ALTER [OPEN CTR Transmittal] MEMO;
    Code:
    ALTER TABLE TblTransmittal ALTER [OPEN CPY Transmittal] MEMO;
    2. Cleanup the data in these columns:
    Code:
    UPDATE TblTransmittal 
       SET TblTransmittal.[OPEN CTR Transmittal] = Replace([OPEN CTR Transmittal],'#','')
         , TblTransmittal.[OPEN CPY Transmittal] = Replace([OPEN CPY Transmittal],'#','');
    3. Rename the form TblMDRMaster to Frm_MDRMaster. It does not make sense to have a form with its name beginning by the prefix "tbl"!

    4. Create a new form:
    - Name: SF_Transmittal
    - RecordSource: TblTransmittal
    - DefaultView: Datasheet

    Note: You can use the wizard to create this form.

    5. Open the form TblMDRMaster in Design view, open the the Property windows (F4), select the subform/subreport control and change its name from Child21 to Child_Transmittal.

    6. Change it's SourceObject property from Table.TblTransmittal to SF_Transmittal.

    7. In the FormFooter section of the parent form TblMDRMaster, create two command buttons:

    First button:
    - Name: Command_OpenCTRTransmittal
    - Caption: OPEN CTR Transmittal
    - OnClick: [Event Procedure]

    Second button:
    - Name: Command_OpenCPYTransmittal
    - Caption: OPEN CPY Transmittal
    - OnClick: [Event Procedure]

    8. Open the module of the parent form TblMDRMaster (Ctrl+G) and paste this code into it:
    Code:
    Private Sub Command_OpenCPYTransmittal_Click()
    
        With Me.Child_Transmittal.Form
            If Not IsNull(![OPEN CPY Transmittal]) Then
                Application.FollowHyperlink ![OPEN CPY Transmittal]
            End If
        End With
    
    End Sub
    
    Private Sub Command_OpenCTRTransmittal_Click()
    
        With Me.Child_Transmittal.Form
            If Not IsNull(![OPEN CTR Transmittal]) Then
                Application.FollowHyperlink ![OPEN CTR Transmittal]
            End If
        End With
    
    End Sub
    Note: The database would be easier to maintain and develop if you would avoid using spaces, non alphanumeric characters (except the underscore) and reserved words in the naming scheme of the objects. e.g. OPEN_CTR_Transmittal or OpenCTRTransmittal instead of OPEN CTR Transmittal
    Thanks and Regards
    R. Vadivelan

  13. #13
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  14. #14
    Join Date
    Oct 2013
    Posts
    163
    Dear Sinndho,

    Quote Originally Posted by Sinndho View Post
    Here's what I would do:

    1. Change the data type of the columns [OPEN CPY Transmittal] and [OPEN CTR Transmittal] of the table [TblTransmittal] from HyperLink to Memo:
    Code:
    ALTER TABLE TblTransmittal ALTER [OPEN CTR Transmittal] MEMO;
    Code:
    ALTER TABLE TblTransmittal ALTER [OPEN CPY Transmittal] MEMO;
    2. Cleanup the data in these columns:
    Code:
    UPDATE TblTransmittal 
       SET TblTransmittal.[OPEN CTR Transmittal] = Replace([OPEN CTR Transmittal],'#','')
         , TblTransmittal.[OPEN CPY Transmittal] = Replace([OPEN CPY Transmittal],'#','');
    3. Rename the form TblMDRMaster to Frm_MDRMaster. It does not make sense to have a form with its name beginning by the prefix "tbl"!

    4. Create a new form:
    - Name: SF_Transmittal
    - RecordSource: TblTransmittal
    - DefaultView: Datasheet

    Note: You can use the wizard to create this form.

    5. Open the form TblMDRMaster in Design view, open the the Property windows (F4), select the subform/subreport control and change its name from Child21 to Child_Transmittal.

    6. Change it's SourceObject property from Table.TblTransmittal to SF_Transmittal.

    7. In the FormFooter section of the parent form TblMDRMaster, create two command buttons:

    First button:
    - Name: Command_OpenCTRTransmittal
    - Caption: OPEN CTR Transmittal
    - OnClick: [Event Procedure]

    Second button:
    - Name: Command_OpenCPYTransmittal
    - Caption: OPEN CPY Transmittal
    - OnClick: [Event Procedure]

    8. Open the module of the parent form TblMDRMaster (Ctrl+G) and paste this code into it:
    Code:
    Private Sub Command_OpenCPYTransmittal_Click()
    
        With Me.Child_Transmittal.Form
            If Not IsNull(![OPEN CPY Transmittal]) Then
                Application.FollowHyperlink ![OPEN CPY Transmittal]
            End If
        End With
    
    End Sub
    
    Private Sub Command_OpenCTRTransmittal_Click()
    
        With Me.Child_Transmittal.Form
            If Not IsNull(![OPEN CTR Transmittal]) Then
                Application.FollowHyperlink ![OPEN CTR Transmittal]
            End If
        End With
    
    End Sub
    Note: The database would be easier to maintain and develop if you would avoid using spaces, non alphanumeric characters (except the underscore) and reserved words in the naming scheme of the objects. e.g. OPEN_CTR_Transmittal or OpenCTRTransmittal instead of OPEN CTR Transmittal
    I had stored the File Location Path in Module Code it self and it works fine. That's by I had eliminated the "OPEN CTR TRANSMITTAL" & "OPEN CPY TRANSMITTAL" Field in my Table.

    Now When the Transmittal is not available in the folder then the Module is showing error. Instead of Module error is it possible to Display the Message Box saying "File Not Found".

    Also I had replaced the Client Document Identification in Frm_MDRMaster with a Combo Box which enables me to search the document.

    Thanks and Regards
    R. Vadivelan

  15. #15
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Use the Dir() function to test whether the file exists or not.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •