Attachment, ask for it, save it on a specific location, keep a hyperlink

[gmazziri]

I'm doing my first db, and have read and watch a lot of tutorial, so far I'm doing ok but I have a issue.
I need to keep attachments as support of some task but the db is getting too large.
Is there any way to have a form to ask for an attachment but not save it in the db , instead save it on a specific location (network drive) and just paste an hyperlink in the field?


Additional would be even better if access name those attachments that it pastes on the specific folder.
Thanks in advance

[ranman256]

this button click lets user pick the file, then it saves to your library...

private btnPickFile_click()
dim vFile, vTarg, f
dim I as integer
const kDIR = "\\server\folder\Documents\"

  'have user pick the file
vFile = UserPick1File()

if vFile<> "" then 
       txtBoxPath = vFile     'save the path in the db tbl

                                   'copy the file to the documents folder
i = InStrRev(psFilePath, "")
If i > 0 Then
  vDir = Left$(psFilePath, i)
  f = Mid$(psFilePath, i + 1)
else 
  exit sub
End If
    
    vTarg  = kdir & f
       filecopy vFile, vTarg
endif
end sub

Public Function UserPick1File( Optional pvPath)
Dim strTable As String
Dim strfilepath As String
Dim sDialog As String, sDecr  As String, sExt As String

If IsMissing(pvPath) Then pvPath = "c:"
With Application.FileDialog(msoFileDialogFilePicker)   'MUST ADD REFERENCE : Microsoft Office xx.0 Object Library
    .AllowMultiSelect = False
    .Title = "Locate a file to Import"
    .ButtonName = "Import"
    .Filters.Clear
    .Filters.Add sDecr, sExt
    .InitialFileName = pvPath
    .InitialView = msoFileDialogViewList    'msoFileDialogViewThumbnail
        If .Show = 0 Then
           'There is a problem
           Exit Function
        End If
    'Save the first file selected
    UserPick1File = Trim(.SelectedItems(1))
End With
End Function

[gmazziri]

Thnaks ranman, I'm a nwb on vb also, so I have to ask, it looks to me that the tbl is getting the original path and not the new one (copy by access) which is the one i would like to link
Thanks

[gmazziri]

My solution was:
Public Function UserPick1File(Optional pvPath)
Dim strTable As String
Dim strfilepath As String
Dim sDialog As String, sDecr As String, sExt As String
If IsMissing(pvPath) Then pvPath = "c:"
With Application.FileDialog(1) 'MUST ADD REFERENCE : Microsoft Office xx.0 Object Library
.AllowMultiSelect = False
.Title = "Locate a file to Import"
.ButtonName = "Import"
.Filters.Clear
.Filters.Add "Document", "*.pdf; *.jpg; *.png; *.xls; *.xlsx; *.doc; *.docx"
.InitialFileName = pvPath
.InitialView = 1 'msoFileDialogViewThumbnail
If .Show = 0 Then
'There is a problem
Exit Function
End If
'Save the first file selected
UserPick1File = Trim(.SelectedItems(1))
End With
End Function

Private Sub btnPickFile_Click()
Dim vFile, vTarg, f, ext
Dim I As Integer
Const kDIR = "C:\Users\gmazziri\Videos\Entrenamiento"
'have user pick the file
vFile = UserPick1File()
If vFile <> "" Then
'get the file extension
If Right$(vFile, 1) = x Then
ext = Right$(vFile, 5)
Else
ext = Right$(vFile, 4)
End If
'assign new name
vTarg = kDIR & IdPtsRecord & ext
'create copy with assign name in desired folder
PtsRecordAttachmentLink = vTarg
FileCopy vFile, vTarg
End If
End Sub