How to store only filename instead of entire path in a field in access 2007?

[prashantdixit]

Hi,

I have a form named as "Add New Stock" and it has some controls.
There is control name as "Reference" along with "Browse folder" button. So user can select a file through this Browse button and the entire file path displays in "Reference" (Textbox) control.

Also this Reference control is bound field which is bounded to a field(type is Hyperlink) in table.

1. Now what i want is like, this Reference control should only show the "FILENAME" and NOT the entire path when user select any file through "Browse" button.
But at the same time, this filename should be clickable so that when user click on this Filename, file should get opened.

The idea is to make this control user friendly as we should show only Filename to him and not the entire path but he wants to open the file he can see it by clicking it.
The code on BrowseButton click event is below:

 Private Sub ButtonTraceRef_Click()
 
    ChDrive "M"
    ChDir "M:\Certificates"
 
     Dim fDialog As Office.FileDialog
     Dim varFile As Variant
     Dim strSelectedFile As String
     Dim CellText As String
 
       ' Clear listbox contents. '
       Me.ComboTracabilityRef.RowSource = ""
 
       ' Set up the File Dialog. '
       Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
 
       With fDialog
 
          ' Allow user to make multiple selections in dialog box '
          .AllowMultiSelect = False
 
          ' Set the title of the dialog box. '
          .Title = "Please select a file"
 
          ' Clear out the current filters, and add our own.'
          .Filters.Clear
          .Filters.Add "All Files", "*.*", 1
          .Filters.Add "PDF File", "*.pdf", 2
          .Filters.Add "JPG File", "*.jpg", 3
          .Filters.Add "Access Databases", "*.MDB", 4
          .Filters.Add "Access Projects", "*.ADP", 5
 
 
          ' Show the dialog box. If the .Show method returns True, the '
          ' user picked at least one file. If the .Show method returns '
          ' False, the user clicked Cancel. '
          If .Show = True Then
 
             'Loop through each file selected and add it to our text box. (Just one in our case)'
                For Each varFile In .SelectedItems
                strSelectedFile = varFile & "#" & varFile
                Next varFile
                CellText = strSelectedFile
                Do While InStr(1, CellText, "\") > 0
                SlashPosition = InStr(1, CellText, "\")
                CellText = Right(CellText, Len(CellText) - SlashPosition)
                Loop
                Me.ComboTracabilityRef.Value = CellText
                Me.strTracability_Reference.Value = strSelectedFile
                Else
                End If
       End With
       'The hyperlinkadded variable must be set to true after populating the trace ref combobox so that the code within the "Private Sub ComboTracabilityRef_Change()" subroutine does not run and remove the hyperlink.
    HyperlinkAdded = True
    End Sub

Thanks
Prashant

[NeoPa]

See How to Programmatically Create a Hyperlink on a Form for controlling what is shown and what is linked to in a HyperLink control.

[prashantdixit]

@NeoPa
Thanks
It worked fine now

[NeoPa]

I'm very pleased to hear that Prashant :)