Hi, I've tried to create a VBA script for attaching Files to note entries.. I can select a file and get the link no problem, but I need to take it one step further and copy the file into the current project path, and then store the link.

Im getting Error 2465 "Microsoft Access can't find the field '|1' referred to in your expression. Ive checked the string values I am getting for Source and Destination and I think they are ok.. but I don't really understand the CopyFile function and ive just found that code posted elsewhere.

The line of code highlighted in the debug is:

fso.CopyFile Source, Destination, [overwrite]

Here is my code.. Referenced functions are below.

Private Sub btnAddFile_Click()

Dim fso As Object
Dim Source As String
Dim Destination As String
Dim FN As String

    Set fso = VBA.CreateObject("Scripting.FileSystemObject")
    
    Source = PickFile()
    
    FN = FileNameFromPath(Source)
        
    Destination = CurrentProject.Path & "\Note Attachments\" & Format(Date, "yyyy") & "\" & Format(Date, "mmmm") & "\"
    
        If Len(Dir(Destination, vbDirectory)) = 0 Then
        myMakeDir Destination
        End If
    
    fso.CopyFile Source, Destination, [overwrite]
    
    NoteLinkAdd = Destination & FN
    
    Set fso = Nothing

End Sub

from debug.print of the variables

Source = \\olddept.isi.aplace.comm\Win10_Migration\Users\myname\Desktop\myfile.pdf

Destination = C:\Local Docs\Note Attachments\2022\October\



Public Function PickFile() As String

    Dim FO As Object
    Set FO = Application.FileDialog(3)
    FO.Show
    PickFile = FO.SelectedItems(1)
    
End Function


Public Sub myMakeDir(strFolderName As String)

On Error Resume Next

Dim a, t As String, i As Integer

a = Split(strFolderName, "\")
t = a(0)

For i = 1 To UBound(a)
  
  t = t & "\" & a(i)
  MkDir t

Next

End Sub