Locate File in a Folder and Insert into Table as Hyperlink

C

Carlos

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
 
D

Douglas J. Steele

Try

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

Carlos

Worked Great! Thank you Douglas.

Douglas J. Steele said:
Try

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

Hadi

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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top