Linking to external files

[dbl]

We store all the documents related to a claim number, claim 12345 would have
a folder 12345 where we would store all documents sent out or received
related to this claim as pdf docs on our shared "L" drive.



Is it possible to open a report in Access 2003 and then view thumb nails of
these pdf documents that relate to each claim number? I have seen it done
in other software packages, so can it be done in Access and if so where do I
start?



Or could I buy an add on to do this?



Thanks Bob

 

[PatHartman]

A simple way is to make use of the common dialog. Below is some code that
uses the Windows API to invoke the dialog which eliminates the need to
distribute the ActiveX control with your applications.

Here is the code to invoke the dialog -- place it in the click event of a
button. You can set the view options of the dialog to Icons to view
thumbnails of the documents.

Private Sub cmdBrowseEmployees_Click()
On Error GoTo Err_Proc
Me.txtFileNameEmployees = LaunchCD(Me) <-------------------- to
invoke the dialog
Exit_Proc:
Exit Sub
Err_Proc:
MsgBox Err.Number & "-" & Err.Description
Resume Exit_Proc
End Sub


In a standard module --------
Option Compare Database
Option Explicit

Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _
"GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long

Private Type OPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type

Function LaunchCD(strform As Form) As String
Dim OpenFile As OPENFILENAME
Dim lReturn As Long
Dim sFilter As String
OpenFile.lStructSize = Len(OpenFile)
OpenFile.hwndOwner = strform.hwnd
If strform.Name = "frmImportSpreadsheet" Then
If Application.Version >= 12 Then
sFilter = "Excel Files (*.xls; *.xlsx)" & Chr(0)
Else
sFilter = "Excel Files (*.xls)" & Chr(0)
End If
Else
If Application.Version >= 12 Then
sFilter = "Access Files (*.mdb; *.accdb)" & Chr(0)
Else
sFilter = "Access Files (*.mdb)" & Chr(0)
End If
End If
OpenFile.lpstrFilter = sFilter
OpenFile.nFilterIndex = 1
OpenFile.lpstrFile = String(257, 0)
OpenFile.nMaxFile = Len(OpenFile.lpstrFile) - 1
OpenFile.lpstrFileTitle = OpenFile.lpstrFile
OpenFile.nMaxFileTitle = OpenFile.nMaxFile

OpenFile.lpstrInitialDir = "C:\"

OpenFile.lpstrTitle = "Select a file"
OpenFile.flags = 0
lReturn = GetOpenFileName(OpenFile)
If lReturn = 0 Then
MsgBox "A file was not selected!", vbInformation, _
"Select a Spreadsheet to import"
Else
LaunchCD = Trim(OpenFile.lpstrFile)
End If
End Function

 

[dbl]

Thanks for the reply Pat but this is a bit over my head (I am a novice at
this). So could we work through it so I get an understanding of what its
doing.

Me.txtFileNameEmployees = LaunchCD(Me) Is this the line of code that is
tell Access where to look for the file?

Me.txtFileNameEmployees is this a field name I need to add i.e.
Me.txtViewPaperPortDoc?

How does it find the file which would typically be in a folder L:\Open
Files\OPEN 38001 TO 38100\38001

in this folder there could be 20 different pdf documents. How would it
pick up which Customer record we are it?

As I see it, it would need to look up the CustomerID from the current
record then look for a matching folder on the L:\ drive and then open the
folder to display its contents. All the docs are pdf files. Even if we
could just get it to take us to folder that would be a big help.

Regards Bob