Using MS Access/Saving attachments to external drive from Form
shannonsims wrote at 2013-04-04 20:14:47
Attached is the code Scottgem helped my with to Save attachments to a Shared Drive from a Form. Thanks a million Scott.
Private Sub AdminDocPath_DblClick(Cancel As Integer)
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
'the number of the button chosen
Dim FileChosen As Integer
FileChosen = fd.Show
'1) To set the caption of the dialog box,
' set the Title property
fd.Title = "Select Admin Document to Upload"
'2) Set the oddly named InitialFileName property to
' determine the initial folder selected
fd.InitialFileName = "C:\Users\swsims\Desktop\E-Pers Test\Admin Docs"
'3) Set the InitialView property to control how your files
' appear on screen (as a list, icons, etc.)
fd.InitialView = msoFileDialogViewSmallIcons
'4) To set the filters (you can have as many as you like)
' first clear any existing ones, then add them one by one
fd.Filters.Add "Excel macros", "*.xlsm"
' if there's more than one filter, you can control which
' one is selected by default
fd.FilterIndex = 1
'5) Set the ButtonName property to control the text on
' the OK button (the ampersand means the following
' letter is underlined and choosable with the ALT key)
fd.ButtonName = "Choose PDF file"
If FileChosen <> -1 Then
'didn't choose anything (clicked on CANCEL)
MsgBox "Upload Cancelled"
'display name and path of file chosen
'store the selected file to a variable
strSelectedFile = fd.SelectedItems(1)
'parse out the filename from the path
strFilename = Right(strSelectedFile, Len(strSelectedFile) - InStrRev(strSelectedFile, "\"))
'build the destination
strDestination = "C:\Users\swsims\Desktop\E-Pers Test\Admin Docs\" & strFilename
'copy the file to the new folder
FileCopy strSelectedFile, strDestination
'store file as part of the record
Me.AdminDocPath = strFilename