Trying to link external files to an Access form.

[klufkee]

Hello all,

I've got a form that allows a user to scroll or search through records.
Each record has a .pdf or .doc file that is a scanned document related
to the record. I would like to have a hyperlink on the form that when
clicked would open the file requested. One of the fields on the form is
a 12 digit number that is the same as the name of the attachment.
(Minus the file extention.) I tried setting the field as a hyperlink
and pointing it to the directory where all the files are located but it
didn't seem to work. Wondering if anyone has some insight into this
type of thing. Or perhaps there's a better way that I'm not aware of.

Thanks,
Bill

 

[John Nurick]

Hi Bill,

Use a text field rather a hyperlink. To launch the document, you could
use a commandbutton on the form, or maybe have the user double-click on
the textbox that displays the text field.

Normally all that would then be needed is this, where XXX is the name of
the textbox and:

Application.FollowHyperlink "D:\Folder\" & Me.XXX.Value

But if you know the name of the file but not its extension things are a
little more complicated. I'd probably use something like this:

Dim PathFile As String
Dim Extension As Variant
Dim Extensions As Variant

PathFile = "D:\Folder\" & Me.XXX.Value
'Get list of possible extensions into an array
Extensions = Split(".pdf .doc", " ") 'add more extensions if desired

'Try each allowed extension in order
For Each Extension in Extensions
If Len(Dir(PathFile & Extension)) > 0 Then 'file exists
Application.FollowHyperlink PathFile & Extension
Exit For
End If
Next