I want to click on a button that lets me browse for a file, then stores the path...

[JessiRight]

Hello, all.



I understand that using the "attachments" feature will drastically bloat my database file, so I'd like to use links instead.

In my Inventory table, I have created a hyperlink field that contains the complete file path to an "Owners Manual" PDF file. When clicked, this hyperlink field opens the PDF file for viewing.

I want to simplify the process of inserting the really long file paths, so I wondered if it was possible to:

1. Click on a button that allows me to browse my computer for a file.
2. After I select the file, the complete path for this file will be stored in the hyperlinked field (so it can be opened later).

Is this hard to do? Any help would be greatly appreciated!!

[ItsMe]

Maybe something like the msoFileDialogFilePicker?
https://www.accessforums.net/program...tml#post229060

[JessiRight]

THANKS so much for your help, ItsMe! I think I've almost reached my goal, but have one more question. Here's what I did:

I created a command button on my form (cmdPickFile).
In the "On Click" event, I chose an Event Procedure and pasted the following code that I adapted for my situation (strTable = “tblItems”). tblItems is the name of the table that contains the field where I want to store the filename:

[code]Private Sub cmdPickFile_Click()

Dim strTable As String
Dim strFilePath As String

With Application.FileDialog(msoFileDialogFilePicker)

.AllowMultiSelect = False
.Title = "Locate a file to Import"
.ButtonName = "Import"
.Filters.Clear
.InitialFileName = "\\DM-SERVER\DM-Data\Inventory\Items\picPickFile.JPG"
.InitialView = msoFileDialogViewThumbnail

If .Show = 0 Then
'There is a problem
Exit Sub
End If

'Save the first file selected
strFilePath = Trim(.SelectedItems(1))

End With

strTable = "tblItems"

End Sub [\code]

I then created a reference to Microsoft Office 15.0 Object Library.

When I clicked on the button to select the file, it seemed to work fine… it let me browse, select a file, and click to “Import” it.

NOW…. THIS IS WHERE I’M STUCK, HOWEVER. After I clicked the “Import” button, the dialog box disappeared, and I think that the filepath is now stored in the variable strFilePath.

HOW DO I TRANSFER the value of the strFilePath variable obtained above to the contents of the textbox ([fldNotesLink])? I want the complete filepath to be stored in the hyperlinked fldNotesLink field so that I can click on it and open the file.

Your continued advice will be greatly appreciated!!

[atuhacek]

file dialog returns a variant. try converting it to string first with cstr().

[June7]

You want to store a hyperlink string in a hyperlink type field? Review http://www.allenbrowne.com/casu-09.html

And http://allenbrowne.com/func-GoHyperlink.html

[JessiRight]

I'm so sorry... I'm a complete novice at programming, and your kind suggestions went "over my head."

1. I do not know how to convert the variant to a string in the code.
2. And, yes... I do want to store the filename path captured in strFilePath in a hyperlinked field (so I can later click on the field and re-open the file).

[atuhacek]

CStr(.SelectedItems(1))

You have multiselect set to false so all you have to do is change strFilePath to Variant

Dim strFilePath as variant

Then change you assignment to

strFilePath = .SelectedItems(1)

[June7]

A hyperlink string has specific structure as explained by Allen Browne's article. If you want to save into hyperlink field the string must be in that structure.

Setting a variable to the dialog value can be done with:

Dim varFile As Variant
    If .Show = -1 Then
        For Each varFile In .SelectedItems
            strFilePath = Trim(.SelectedItems(1))
        Next
    End If

[atuhacek]

this will return the path selected not sure on the hyperlink. I may be wrong but if the data is stored with htttp:\\ or file:\\ it should be treated as hyperlink

Function cmdPickFile() As Variant
Dim objDialog As Object


Set objDialog = Application.FileDialog(3)


With objDialog
    .AllowMultiSelect = False
    .Show
    If .SelectedItems.Count = 0 Then
        MsgBox "No file selected."
    Else
        cmdPickFile = .SelectedItems(1)
    End If
End With
Set objDialog = Nothing


End Function

[atuhacek]

Function cmdPickFile() As VariantDim var As String
Dim objDialog As Object
Dim rst As Object


Set rst = CurrentDb.OpenRecordset("Table1")
Set objDialog = Application.FileDialog(3)




With objDialog
    .AllowMultiSelect = False
    .Show
    If .SelectedItems.Count = 0 Then
        MsgBox "No file selected."
    Else
        var = .SelectedItems(1)
    End If
End With
Set objDialog = Nothing


With rst
    .AddNew
    !xx = fGetBaseFileName(var) & "#" & var
    .Update
End With


rst.Close


cmdPickFile = var


End Function


 Public Function fGetBaseFileName(strFilePath As String) As String
'This Function accepts the Absolute Path to a File and returns the Base File
'Name (File Name without the Extension)
 
'Make absolutely sure that it is a valid Path/Filename
If Dir$(strFilePath) = "" Then Exit Function
 
Dim strFileName As String
Dim strBaseFileName As String
 
strFileName = Right$(strFilePath, Len(strFilePath) - InStrRev(strFilePath, "\"))
 
strBaseFileName = Left$(strFileName, InStr(strFileName, ".") - 1)
  fGetBaseFileName = strBaseFileName
End Function

Here! This will open the dialog and then save the selection to table "Table1" and field "xx" and save to a hyperlink field.

[ItsMe]

...HOW DO I TRANSFER the value of the strFilePath variable obtained above to the contents of the textbox ([fldNotesLink])? I want the complete filepath to be stored in the hyperlinked fldNotesLink field so that I can click on it and open the file....

If you use that VBA code behind a bound form you can assign the value that is the string variable to a bound control or directly to the field in the table.

Me.fldNotesLink = strFilePath
or
Me!fldNotesLink = strFilePath

It should be that simple. I have not tested it with data type Hyperlink fields because I use data type Text. However, I feel confident that there are not any special things you need to do to get the results you desire.

The existing code is designed to retrieve ONE file (.AllowMultiSelect = False). Because you are retrieving only ONE file, there is not a need for type Variant. The string variable strFilePath is perfectly suitable for this application. There is not an array because the user selects only one file/path. No array = no need for type Variant.

strFilePath = Trim(.SelectedItems(1)) This line is looking at a Variant type. However, the "1" is a specific item within the array. The string variable is set to equal only one item, the first and ONLY item within the array. No data is being truncated because of the String variable.

[atuhacek]

You have to remove the file extension or the field wont recognize it as a hyperlink. I was thinking along the same lines. My setup wouldn't recognize the link until I removed the file extension.

[ItsMe]

You have to remove the file extension or the field wont recognize it as a hyperlink. I was thinking along the same lines. My setup wouldn't recognize the link until I removed the file extension.

EDIT:
I do not see that as a solution. The only thing that I could get to work is to include the # qualifiers and additional text, like described in the link June provided.

[JessiRight]

THANKS SO MUCH for everyone’s responses… I really DO appreciate the help. Because there were three responses, I attempted to digest them all, and I have listed the results below:

___________________

ItsMe’s Response: I added the line Me.fldNotesLink=strFilePath to my first code. This solution partially worked. The command button does let me select a file, but when I subsequently click on my hyperlink field (fldNotesLink), nothing happens… it doesn’t open the file like a hyperlink should. HOWEVER, when I edit the field, I can see that the filepath was indeed inserted in the “Text to Display” portion, just NOT the “Address” portion that it needs to execute the hyperlink.

So, I guess the final question is: how do I get the strFilePath variable to transfer to the “Address” part of the hyperlink field instead of the “Text to Display” part? Here’s what I have so far:

Private Sub cmdPickLink_Click()
Dim strFilePath As String
 
With Application.FileDialog(msoFileDialogFilePicker)
 
    .AllowMultiSelect = False
    .Title = "Locate a file to Import"
    .ButtonName = "Import"
    .Filters.Clear
    .InitialFileName = "\\DM-SERVER\DM-Data\Inventory\Items\picPickFile.JPG"
    .InitialView = msoFileDialogViewThumbnail
   
        If .Show = 0 Then
        'There is a problem
        Exit Sub
        End If
   
    'Save the first file selected
    strFilePath = Trim(.SelectedItems(1))
   
End With
 
strTable = "tblItems"
Me.fldNotesLink = strFilePath
 
End Sub

__________________________________________________ _

Atuhacek and June 7 responses:

If I understand y’all’s responses correctly, the returned file path in my first code was stored in a string variable called strFilePath, but in order to be compatible with the hyperlink format, I need for it to be in the “variant” format.

Therefore, I tried modifying my code as follows:
1. Line 3: I changed strFilePath to VARIANT (instead of String).
2. Line 19: I used the CStr to convert it to a string.
3. Line 20: Then I used the procedure that June7 gave me to assign it to a variable (that I want to eventually assign to my text box):

Obviously I didn’t understand because when I clicked my command button, I got a compile error at Line 19 (CStr) with the following code:

Private Sub cmdPickFile_Click()
 
Dim strTable As String
Dim strFilePath As Variant
 
With Application.FileDialog(msoFileDialogFilePicker)
 
    .AllowMultiSelect = False
    .Title = "Locate a file to Import"
    .ButtonName = "Import"
    .Filters.Clear
    .InitialFileName = "\\DM-SERVER\DM-Data\Inventory\Items\picPickFile.JPG"
    .InitialView = msoFileDialogViewThumbnail
   
        If .Show = 0 Then
        'There is a problem
        Exit Sub
        End If
   
    'Save the first file selected
    strFilePath = Trim(.SelectedItems(1))
   
End With
 
strTable = "tblItems"
 
CStr(.SelectedItems(1))
 
Dim varFile As Variant
    If .Show = -1 Then
        For Each varFile In .SelectedItems
            strFilePath = Trim(.SelectedItems(1))
        Next
    End If
 
End Sub

______________________________



I then tried the code that Atuhacek gave me, and I changed “Table1” to my “tblItems”, and “XX” to “fldNotesLink” (my hyperlink field) to match my situation.

BUT I’m not sure how to enter this code into the command button. If I choose the Event Procedure option on the “On Click” event, it inserts the code between a Private Sub and End Sub commands, then it gives me a compile error: End Sub Expected. If I delete the beginning Private Sub and ending End Sub commands, then nothing happens.

So, I guess my question is: Do I use the Event Procedure to enter this code, or should I be doing something different?

Function cmdPickFile() As Variant
Dim var As String
Dim objDialog As Object
Dim rst As Object
 
Set rst = CurrentDb.OpenRecordset("Table1")
Set objDialog = Application.FileDialog(3)
 
With objDialog
    .AllowMultiSelect = False
    .Show
    If .SelectedItems.Count = 0 Then
        MsgBox "No file selected."
    Else
        var = .SelectedItems(1)
    End If
End With
Set objDialog = Nothing
 
With rst
    .AddNew
    !xx = fGetBaseFileName(var) & "#" & var
    .Update
End With
 
rst.Close
 
cmdPickFile = var
 
End Function
 
 Public Function fGetBaseFileName(strFilePath As String) As String
'This Function accepts the Absolute Path to a File and returns the Base File
'Name (File Name without the Extension)
 
'Make absolutely sure that it is a valid Path/Filename
If Dir$(strFilePath) = "" Then Exit Function
 
Dim strFileName As String
Dim strBaseFileName As String
 
strFileName = Right$(strFilePath, Len(strFilePath) - InStrRev(strFilePath, "\"))
 
strBaseFileName = Left$(strFileName, InStr(strFileName, ".") - 1)
  fGetBaseFileName = strBaseFileName
End Function

[atuhacek]

Just copy and past the one I uploaded over your old code and change those two fields. You may want to add

me.requery

just before the first

end function