I am attempting to create a button on one of my forms in Access that will move a file from one folder to another. The filepath of the item is stored in the database. My current approach is using VB and is displayed here.
Private Sub Command21_Click()
Dim d As Database
Dim r As Recordset
Dim path As Field
Dim fromPath As String
Dim toPath As String
Set d = CurrentDb()
Set r = d.OpenRecordset("Documents")
Set path = r.Fields("Action Items Location")
While Not r.EOF
fromPath = path
Set toPath = My.Computer.FileSystem.GetParentPath(fromPath) 'Error line
toPath = toPath & "\to folder"
My.Computer.FileSystem.MoveFile fromPath, toPath
Wend
End Sub
I keep getting an error saying object required on the line marked Error line. How do I fix this error, or am I even going about it the correct way?
Thanks for the replies, though after a bit more research, and the suggestion of @Basdwarf, I was able to find a solution. Here's the finished code
Private Sub Command21_Click()
Dim d As Database
Dim r As Recordset
Dim path As Field
Dim fromPath As String
Dim toPath As String
Dim fileName As String
Dim filesystem As Object
Set filesystem = CreateObject("Scripting.FilesystemObject")
Set d = CurrentDb()
Set r = d.OpenRecordset("Documents")
Set path = r.Fields("Action Items Location")
fromPath = path
fileName = filesystem.GetFileName(path)
toPath = filesystem.GetParentFolderName(filesystem.GetParentFolderName(fromPath)) & "\to folder" & "\" & fileName
MsgBox (fromPath)
MsgBox (toPath)
FileCopy fromPath, toPath
Kill fromPath
End Sub