June,
Canned it and started from scratch. With much research and some mentorship from another site we were able to put together the following that works well for uploading the files from a Form. Still working on automating the generation of a hyperlink while maintaining the original name of the file. Here it is in hopes it may help someone else.
Code:
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\....."
'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.Clear
fd.Filters.Add "PDF macros", "*.pdf"
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
' 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"
Else
'display name and path of file chosen
MsgBox fd.SelectedItems(1)
End If
'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\........" & strFilename
'copy the file to the new folder
FileCopy strSelectedFile, strDestination
'store file as part of the record
Me.AdminDocPath = strFilename
End Sub