Locate File in a Folder and Insert into Table as Hyperlink

[Guest]

Hello,

I would like Access to search a specified folder for files (all of the files
in the specified folder are .doc files) and enter the filenames into a field
in a table as a hyperlink. I am currently using the following function:

Function LocateFile(strFileName As String)
Dim vItem As Variant
Dim db As DAO.Database
Set db = CurrentDb
With Application.FileSearch
.FileName = strFileName
.LookIn = "C:\Tests\Proposed"
.SearchSubFolders = True
.Execute
For Each vItem In .FoundFiles
db.Execute _
"INSERT INTO Tests (Proposed Tests) " & _
"VALUES(" & Chr(34) & vItem & Chr(34) & ")", _
dbFailOnError
Next vItem
End With
Set db = Nothing
End Function

This works fine at entering the filenames into the table, but they do not
work as hyperlinks to the files. I have the data type of the field set to
Hyperlink, but the text that is entered into the field is not linked to
anything.

Can anyone help me with this problem?

Thanks

 

[Douglas J. Steele]

Try

"INSERT INTO Tests (Proposed Tests) " & _
"VALUES(" & Chr(34) & vItem & "#" & _
vItem & "#" & Chr(34) & ")", _

 

[Guest]

Worked Great! Thank you Douglas.

Try

"INSERT INTO Tests (Proposed Tests) " & _
"VALUES(" & Chr(34) & vItem & "#" & _
vItem & "#" & Chr(34) & ")", _

 

[Guest]

Carlos,
I am doing something similar to your task below and had a couple of questions:

Is the vItem in the code the PrimaryKey field in your table and if not how
does the code mathup the .doc with the right record in your table?

What does the .doc name look like? is it just vitem.doc or how is that
working?