I've been looking all over for how to do this, and haven't been able to find out.
I want the user to be able to attach files to a database record (stored in the file system, not in the database), and then be able to access the files from a listbox on the form, and also be able to clear any files attached to the record.
So, it should look like this:
1. User clicks "attach file"
2. open/save dialog box comes up
3. user chooses file and hits save/attach/open/whatever
4. path to file is stored in database
5. file shows up on listbox in the form for that record.
6. user chooses file from listbox
7. user clicks "open file"
8. file opens in outside program
I found code (http://www.mvps.org/access/api/api0001.htm) to get the command button to bring up the open/save box, but when I click open, I get "runtime error: you can't assign a value to this object." I don't know how to get the file path to save in the record. I got the listbox to show the files, but I don't know how to then allow the user to run the files shown.
The following code opens a file picker dialog box with the appropriate jiggery...
Option Compare Database
Private Sub cmdSelectFile_Click()
On Error GoTo Err_cmdSelectFile_Click
Dim fDialog As Office.FileDialog
Dim varFile As Variant
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
'Set dialog box title
.Title = "This is the dialog box's title"
'Set to false to only allow user to select one item
.AllowMultiSelect = True
'Set the button caption
.ButtonName = "Attach File"
'If user hits ok then update default folder path
If .Show = True Then
For varFile = 1 To .SelectedItems.Count
<INSERT YOUR UPDATE SQL HERE>
MsgBox "Error Number: " & Err.Number & Chr(13) & _
"Error Description: " & Err.Description, vbCritical, "Unknown Error"
Now for point 4 you need to change the line that reads <INSERT YOUR UPDATE SQL HERE> to something along the lines of
"UPDATE <yourtable> SET <thefieldname> = '" & .Selected Items "'"
Note the single quotes in the above line.