none
Creating Batch hyperlinks based on field contents RRS feed

  • Question

  • This problem will be elementary I know but I'm stumped. I'm not a code writer but I understand access pretty well so I will explain as I understand it. I believe there is VB involved. I have a database, contains one table. This table contains nine fields, 1 autonum, 4 boolean, 1 date, 2 text and 1 hyperlink. One of the text fields contains 1750 unique records which are text strings, Example (fh_88991, fh_88892, etc). Each of these records should correspond to a .mp4 video contained within the same folder as the database. The file name of the videos contain the same record name, Example: (fh_88991.mp4, fm_88992.mp4). 

    The problem- I need to create 1750 hyperlinks to the videos in the hyperlink field and prefer not to do it one at a time. Here is what I want to do. I need the code to cause the hyperlink cell to: query the folder containing the videos, if a file is found that has the unique text record name (fm_88891=fm_88891.mp4) the file name (only the file name, no path information) is displayed in the cell as a click hyperlink. If no file is found containing the text string (fm_88891) the cell is left empty. Then replicate it 1749 more times. 

    Help please, thank you in advance!!

    Bilstenson

    Friday, January 27, 2017 3:24 PM

All replies

  • Hi,

    Do you really need a hyperlink field? Are you expecting your users to open the table and click on the link? It's recommended best practice to keep users out of tables and use forms to interact with the data. If you use a form to display the data, you can have a hyperlink automatically created for you without having to store it in the table.

    Just a thought...

    Friday, January 27, 2017 4:26 PM
  • Hello,

        This database will be turned over to my client who works with the data tables.  There really is no need for a form in this application.  They will be importing the data into a GIS platform.  They have received this format in the past and like it and are requiring it again.

    Thank you

    Bilstenson

    Friday, January 27, 2017 5:14 PM
  • In which case, I guess you can't avoid using code to populate the table. What have you tried so far and where are you having a hard time?

    PS. I am not familiar with GIS platforms but do they accept hyperlink fields?

    Friday, January 27, 2017 5:20 PM
  • They do accept hyperlinks. 

    I have tried multiple things.  Unfortunately I am not a VB or Macro guy.  I can set up the hyperlink manually for each record but with 1750 of them it will take forever.  I really don't know if I am doing it right or not.  This is really not in my strike zone but I can say that last year I did a google search on this problem and found the solution.  It was supposed to be an ad-hoc project so I didn't document what I did.  Now I can't figure out what I did.  I remember the layman's terms for what I did. Now I just need to get it into code to finish this.  Here is my description of what I need to do:

      I need the code to cause the hyperlink cell to: query the folder containing the videos, if a file is found that matches the unique text record name (fm_88891=fm_88891.mp4) the file name (only the file name, no path information) is displayed in the cell as a click hyperlink. If no file is found containing the text string (fm_88891) the cell is left empty. 

    Sorry to be stupid here.

    Bilstenson

    Friday, January 27, 2017 7:51 PM
  • Hi,

    I was thinking you'll need some code but you might be able to use the following UPDATE query:

    UPDATE TableName
    SET HyperlinkFieldName=TextFieldName & "#FullPathToFile\" & TextFieldName & ".mp4
    WHERE Dir(FullPathToFile & "\" & TextFieldName & ".mp4")<>""

    (untested)

    Hope it helps...

    Friday, January 27, 2017 8:22 PM
  • Thank you so much, I will try it now.  Can you tell me if this is VB or if I need to create a macro.  And, how do I run it and copy the formula for all 1750 records?

    Bilstenson

    Friday, January 27, 2017 8:28 PM
  • If I understand your question correctly, are you saying that you have 1,750 rows in a table where you need to generate the hyperlink value for each row, based upon whether the value in a text field in that same row (a filename) exists in a file system folder?

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, January 27, 2017 8:35 PM
  • Exactly Paul.  The video files that need to be hyperlinked to are in the same folder as the database.  If the video file exist the file name will include the value that is in the text field in the same row as the hyperlink.    I'm trying to upload a picture of what I am looking at but I have not been "verified" yet so it wont allow me to do so.

    Thank you.

    Bill

    Friday, January 27, 2017 8:51 PM
  • Thank you so much, I will try it now.  Can you tell me if this is VB or if I need to create a macro.  And, how do I run it and copy the formula for all 1750 records?

    Bilstenson

    Hi,

    What I posted is an SQL statement for an UPDATE query. It's not VBA or macro - just a query. Try copying and pasting it in the SQL View of a new query. However, you'll have to replace the field names and folder paths to the actual names of your field and folder.

    Hope it helps...

    Friday, January 27, 2017 8:54 PM
  • Exactly Paul.  The video files that need to be hyperlinked to are in the same folder as the database.  If the video file exist the file name will include the value that is in the text field in the same row as the hyperlink.    I'm trying to upload a picture of what I am looking at but I have not been "verified" yet so it wont allow me to do so.

    Thank you.

    Bill

    The SQL UPDATE query posted by .theDBguy is probably more efficient, but the below VBA/DAO code should work as well:

    Sub CheckForFileAndCreateHyperlink()
    
    Dim db As DAO.Database
    Dim tbl As DAO.Recordset
    Dim dbFolderPath As String
    
    dbFolderPath = CurrentProject.Path & "\"
    
    Set db = CurrentDb
    Set tbl = db.OpenRecordset("TableName1", dbOpenTable)
    
    Do While Not tbl.EOF
        tbl.Edit
        If Dir(dbFolderPath & tbl.Fields("FileName").Value & ".mp4") <> vbNullString Then
            tbl.Fields("MediaLink").Value = "#" & tbl.Fields("FileName").Value & ".mp4"
        Else
            tbl.Fields("MediaLink").Value = vbNullString
        End If
        tbl.Update
        tbl.MoveNext
    Loop
    
    tbl.Close
    db.Close
    
    End Sub


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, January 27, 2017 10:06 PM
  • Hello,

       So I tried the code and got a Syntax Error.

    Here is the code

    UPDATE  SET;
    UPDATE MH_INSP_VID
    SET VIDEO_LINK=MH_NUM & "#P:\2016 DATA\PWSD 2016\2016 PWSD MH INSPECTIONS\Sub001 not processed yet\PWSD 2016 MH INSPECTION VIDEOS\2016 PWSD MH INSPECTION VIDEOS\" & MH_NUM & ".mp4
    WHERE Dir(P:\2016 DATA\PWSD 2016\2016 PWSD MH INSPECTIONS\Sub001 not processed yet\PWSD 2016 MH INSPECTION VIDEOS\2016 PWSD MH INSPECTION VIDEOS & "\" & TextFieldName & ".mp4")<>""

    My table is named: MH_INSP_VID

    The hyperlink field is named: VIDEO_LINK

    The text field is named: MH_NUM

    The file path is: P:\2016 DATA\PWSD 2016\2016 PWSD MH INSPECTIONS\Sub001 not processed yet\PWSD 2016 MH INSPECTION VIDEOS\2016 PWSD MH INSPECTION VIDEOS

    Bill

    Friday, January 27, 2017 10:07 PM
  • Hi Bill,

    Not sure why you have the following:

    UPDATE SET;

    Try using the following instead:

    UPDATE MH_INSP_VID
     SET VIDEO_LINK=MH_NUM & "#P:\2016 DATA\PWSD 2016\2016 PWSD MH INSPECTIONS\Sub001 not processed yet\PWSD 2016 MH INSPECTION VIDEOS\2016 PWSD MH INSPECTION VIDEOS\" & MH_NUM & ".mp4"
     WHERE Dir("P:\2016 DATA\PWSD 2016\2016 PWSD MH INSPECTIONS\Sub001 not processed yet\PWSD 2016 MH INSPECTION VIDEOS\2016 PWSD MH INSPECTION VIDEOS\" & MH_NUM & ".mp4")<>""

    Hope it helps...

    Friday, January 27, 2017 10:29 PM
  • Each of these records should correspond to a .mp4 video contained within the same folder as the database. The file name of the videos contain the same record name, Example: (fh_88991.mp4, fm_88992.mp4). 

    Hi Bilstenson,

    An alternative could be to leave the field just as text field. Clicking the field (or some other action) will construct the full file specification, that is used as parameter in ShellToFile (ShellExecute) to "display" the file. Search Google for more details on ShellToFile.

    Imb.

    Saturday, January 28, 2017 6:39 AM
  • Hi Bilstenson,

    I agree with the suggestion of .theDBguy.

    why you used ,"UPDATE SET;" ?

    try to run the query again without that line and check whether it gives you an error or it works correctly.

    also you can try to test the code suggested by paul may help you to solve your issue.

    let us know about your testing results. we will try to suggest you further to solve the issue.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 30, 2017 4:25 AM
    Moderator
  • I tried the new code and got an "undefined function 'DIR' in expression" error.

    Thanks again for your help.

    Bill

     
    Thursday, February 2, 2017 10:27 PM
  • I tried the new code and got an "undefined function 'DIR' in expression" error.

    Thanks again for your help.

    Bill

     

    Hi Bill,

    Sorry, I guess Dir() is a VBA only function. In this case, I would suggest creating a wrapper function for it. For example:

    Public Function GetDir(FilePath As String) As String
        GetDir = Dir(FilePath)
    End Function

    You can then replace "Dir" in the query with "GetDir."

    Hope it helps...

    Thursday, February 2, 2017 10:36 PM
  • Sorry to be stupid here, where would I put this code?   In the same update query?  I'm not sure what a wrapper function is.

    Thanks,

    Bill

    Friday, February 3, 2017 3:28 PM
  • Sorry to be stupid here, where would I put this code?   In the same update query?  I'm not sure what a wrapper function is.

    Thanks,

    Bill

    Hi Bill,

    You'll need to place the function code in a Standard Module.

    1. Hit Alt+F11 to open the code window

    2. From the main menu bar, select Insert > Module

    3. Copy and paste the code I posted above

    4. Modify your query to replace "Dir" with "GetDir"

    Hope it helps...

    Friday, February 3, 2017 3:31 PM
  • Hi Bilstenson,

    is your issue solved now?

    we didn't find any response from you after the last suggestion given by .theDBguy

    if your issue is solved now then try to mark the suggestion as an answer which helped you to solve your issue.

    if your issue is still exist then let us know about that, we will try to suggest you further.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, February 6, 2017 7:58 AM
    Moderator