File Path Hyperlink

[SolerPower]

Hello
I’m using MS Access 2010. I have a code that I’m using that loops through a folder gather all the names of the files in the folder and inserts them into a table (shown below). The table is named “tblFiles” there are two columns in the table titled file name and file path. What I want to do is to also loop through the same folder to find the file path of the files and insert them into the file path column has a hyperlink where users can just click on the hyperlink and the file opens. Any help would be greatly appreciated.
thank you.

Private Sub Form_Load()
Dim fs, f, f1, fc, s
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("tblFiles")
folderspec = "S:\Scott\ Guide Revisions\09-2013"
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(folderspec)
Set fc = f.Files
For Each f1 In fc
rs.AddNew
rs.Fields("FileName") = f1.Name
rs.Update
Next
Set rs = Nothing
DoCmd.Requery "tblfiles subform"
End Sub​

 

[CJ_London]

Do you need to use a hyperlink?

Put this in the click or doubleclick event of your control and the file will open. You can always make the control look like a hyperlink by formatting it.

ShellExecute 0, "Open", """" & myPath & "\" & myControl & """", vbNullString, vbNullString, 1

Note this example assumes all files are in the myPath directory and myControl is the filename and extension

 

[SolerPower]

Do I have to declare shellExecute in a function or something? I keep receiving a compile error: Sub or Function not defined. Below is the code I have for the on click function.

Dim db As DAO.Database
Set db = CurrentDb
Dim doc As Document
Dim mypath As String
Dim mycontrol As String
mypath = "S:\Scott\FA Guide Revisions\09-2013"
mycontrol = Me.FileName

shellExecute 0, "Open", """" & mypath & "\" & mycontrol & """", vbNullString, vbNullString, 1

 

[CJ_London]

Apologies, yes you do:

put this in a module (not in your form module)

Public Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hWnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

 

[SolerPower]

that works perfect. Appreciate your help thank you so much!!