Hi there, this is a toughie!

I am in the process of building a new system using access and VBA. One of the major requests from users in terms of functionality for the new database is they would like the ability to store word documents, excel documents, PDF documents, scanned documents e.g. gif, tif, bmp etc against either a company or a contact within the database. I do not want to bloat the size of the database because initially it will be an Access back end. I would like to create a documents table and link it to the company table and on the company form i will then have a command button that will open up a documents form relevant to that company. On that form i would then like to be able to press a command button that will call the windows API, allow me to select the document and then store the path to that document in the documents table along with the name of the document. The documents table will then reside as a subform on the company form and i would like users to be able to see the document they have stored on the sub form and double click the document, at which time it will trace the path to the document and open it.

Ideally i would like to be able to save multiple documents to a single company in this way.

Thanks for your help, it is always much appreciated.

Rhys.

Rhys no problem,

Your looking for a file open dialog to select the file for storing in the database, and for displaying the file there is a Windows API call you can use thats like a double click in explorer.

The file open dialog... this code doesn't take into consideration the user pressing cancel.

Public Sub FileOpen()

Dim dlgOpen As FileDialog
Set dlgOpen = Application.FileDialog(msoFileDialogOpen)

'the file open can work with multiple file selections or with single file selections
With dlgOpen
.AllowMultiSelect = False
.Show
End With

'with both singular and multiple selections you have an array
'with singular selections there is only ever one item in the array
MsgBox dlgOpen.SelectedItems(1)

End Sub

To open any file in it's registered program copy the code below into a module and from anywhere in your code call OpenFile passing in the full path to the file you want to open. PDF's will open in Acrobat, Doc's in Word, and so on.

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

Public Sub OpenFile(file As String)
On Error GoTo OpenFile_Err
'open the document in whatever application it requires
ShellExecute 0, "open", file, vbNullString, vbNullString, vbNormalFocus
Exit Sub
OpenFile_Err:
MsgBox "An unexpected error has occurred.", vbOKOnly + vbCritical, "Error Conditon in Opening File"
End Sub

Hi Derek, where do i enter the 'fileopen' code? I would like to enter it on the on click event of a command button?

Thanks,

Rhys.

Rhys,

Create a module and copy the code I posted into it. Then in your command button OnClick event call FileOpen() passing in the full path to the file, i.e. FileOpen("C:\Images\NakedLady.jpg"). The file will open in whatever application the file type is registered too. You can pass any string as long as it's a valid path to a file that exists on the file system.

cheers Derek, got it all working now.

Thanks again!

Rhys.