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.