Results 1 to 6 of 6
  1. #1
    newbieX is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Posts
    111

    Navigate to file and display only filename as hyperlink

    I have a button on an Access form that searches for a file and populates a text box if it is not populated when loading the form (i.e. that field is null in the table). I need the literal path name for other purposes in my program so I remove hyperlinks when populating the box. However, I want to update the table with the new hyperlink to the file if I had to search for and populate the text box with this new value. Thus I created a variable (varFieldUpdate) to pass back to the table if the cmdUpdateMap button is clicked.



    The table is updated but it displays the entire path, not just the file name. How can I alter my text to accomplish this?

    Code:
    Private Sub cmdFindMap_Click()
    Dim dlg As FileDialog
    Dim strSelectedFile As String
    Set dlg = Application.FileDialog(msoFileDialogFilePicker)
    
    ' Find pdf map file.
    currDir = Application.CurrentProject.Path
    With dlg
        .Title = "Select pdf file of map"
        .InitialFileName = currDir & "\PDF Maps" ' set default location
        .AllowMultiSelect = False
        .Filters.Clear
        .Filters.Add "pdf", "*.pdf", 1
        If .Show = True Then
            For Each varItem In .SelectedItems 'There will only be 1
                strSelectedFile = varItem
                strHyperlinkFile = fGetBaseFileName(strSelectedFile) & "#" & strSelectedFile
                varFieldUpdate = strHyperlinkFile ' Want to use this variable to update hyperlink field in table
                Me.txtMapLink = HyperlinkPart(strHyperlinkFile, acAddress) ' The whole path without hyperlink info for use in other code
            Next varItem
        Else
            Exit Sub
        End If
    End With
    End Sub
    Code:
    Private Sub cmdUpdateMap_Click()
    varLocationsID = Me.txtLocationsID.Value
    If Me.txtMapLink.Value = "" Or IsNull(Me.txtMapLink.Value) Then
        MsgBox Me.txtMapLink.ControlTipText & " is a empty. Cannot update"
    Else
        strSQL = "SELECT Locations.MapLink FROM Locations WHERE Locations.LocationsID = " & varLocationsID
        Set rs = CurrentDb.OpenRecordset(strSQL)
        rs.Edit
        rs!MapLink = varFieldUpdate
        rs.Update
        rs.Close
        Set rs = Nothing
        MsgBox Locations map has been updated."
    End If
    End Sub
    As usual, many thanks.

  2. #2
    Gicu's Avatar
    Gicu is online now VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Have you looked at varFieldUpdate in the immediate window to see how it looks like? Compare what you get with debug.print varFieldUpdate with the values stored in the table.

    Cheers,
    Vlad

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Use string manipulation functions.

    Let x represent path\filename:
    Right(x, Len(x)-InStrRev(x,"\"))
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Not sure which of these procedures is likely to run first, but you do have the file name only in the top one. Why not make use of strSelectedFile = varItem? Seems to me that one or maybe even both of the click events should be calling a single function. If the function call is from btnA, update. If not, get the file and write strSelectedFile to the table, but use the same function. The code lines could probably be condensed that way.
    EDIT - as an afterthought, one button is probably enough. Doesn't matter if the textbox is empty or not - get the file and either update or append accordingly after populating the textbox. Plus I'm not saying that only the file name is being passed back by the dialog - just that things probably can be combined and/or passed between functions.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    newbieX is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Posts
    111
    I changed code to this and it works. I am not sure why as it looks like it is referencing the same values to me. Gremlins?

    Code:
        If .Show = True Then
            For Each varItem In .SelectedItems 'There will only be 1
                strSelectedFile = varItem
                strHyperlinkFile = fGetBaseFileName(strSelectedFile) & "#" & strSelectedFile
                varFieldUpdate = strHyperlinkFile
                Me.txtMapLink = strSelectedFile
            Next varItem
        Else

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Well, the difference seems to be that you're passing a value to a function that we can't see, that you were not before.
    I don't see where varItem is declared, which indicates you are not forcing explicit declaration of variables. Plus, when the data type of an object or collection is variant, better to declare the variable you're going to pass it to as variant and not something else, like a string. While it may work some of the time, it's not good practice IMHO.

    I also wonder why the author of the code constructs a For Next loop for a single list item return. Why not just
    Code:
    If .Show = True Then
       strSelectedFile = .SelectedItems(1)
       ...
    Else
       Exit Sub
    End If

Please reply to this thread with any new information or opinions.

Similar Threads

  1. VBA code to open a file with a dynamically changing filename
    By orangeman2003 in forum Programming
    Replies: 1
    Last Post: 09-09-2014, 06:17 PM
  2. Display the Filename in a Report.
    By GordonT in forum Access
    Replies: 1
    Last Post: 01-27-2013, 08:35 AM
  3. Export table to txt file with a variable filename
    By macollins7 in forum Import/Export Data
    Replies: 6
    Last Post: 07-12-2012, 09:44 AM
  4. Exporting and saving file as [FORM]![FORM1]![FILENAME]
    By Elbows in forum Import/Export Data
    Replies: 1
    Last Post: 10-18-2011, 10:02 AM
  5. Display hyperlink as Icon??
    By Rosier75 in forum Access
    Replies: 2
    Last Post: 03-08-2011, 06:44 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums