asked on

How do I make a command button that will allow a user to attach a document to a record in a database in Access 2003?

I have an asset repository database that I have created in Access 2003. I am wanting to add some kind of command button or something that will allow the user of the database to attach a document that is pertaining to their "Asset"....

What would be the easiest way to do this? I am brand new at Access, so please bare with me.

ASKER CERTIFIED SOLUTION

rockiroads

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rockiroads

Sample code that will prompt the user for a file. Place this in a module somewhere. Remember to ensure the module name is not the same as any of the function names (add via vba editor)

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 PromptForFile(ByVal sFilter As String, lHwnd As Long) As String

Dim OpenFile As OPENFILENAME
Dim lReturn As Long

OpenFile.lStructSize = Len(OpenFile)
OpenFile.hwndOwner = lHwnd
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 document"
OpenFile.flags = 0
lReturn = GetOpenFileName(OpenFile)
If lReturn = 0 Then
PromptForFile = ""
Else
PromptForFile = Trim(Left(OpenFile.lpstrFile, InStr(1, OpenFile.lpstrFile, vbNullChar) - 1))
End If
End Function

Now in your form, you can prompt like this

Dim sFile As String
Dim sFilter As String

sFilter = "Documents (*.doc)" & Chr(0) & "*.doc" & Chr(0) & _
"All Files (*.*)" & Chr(0) & "*.*" & Chr(0)

sFile = PromptForFile(sFilter, Me.hwnd)
If sFile <> "" Then
'CODE TO SAVE TO DB HERE
End if

Regarding saving to the db

1) Using Ole

If sFile <> "" Then
Me.MyOleField.SourceDoc = sFile
Me.MyOleField.Action = acOLECreateEmbed
End If

2) Using Linked Path
Dim sFilename As String

If sFile <> "" Then
sFilename = Dir$(sFile)
FileCopy sFile, "C:\mycentraldir\" & sFilename
Me.MyFile = sFilename
End If

With number 2, it is better to rename the file to say the id of the record. This is so it avoids any duplicate clashes
So say you had a autonumber field on display in the form called ID

If sFile <> "" Then
sFilename = Dir$(sFile)
FileCopy sFile, "C:\mycentraldir\" & Me.ID & ".doc"
Me.MyFile = Me.ID
End If

Note, dont store C:\mycentraldir in the database. Make it flexible and store elsewhere. If you decide to change it, it is only one place to change.