Help! I can't get my VBA to work and your help is appreciated!
-Need to copy a set of folders from server to desktop.
-Folders on the server are for each item by model number.
-Within Access database, items can be selected with a checkbox within the Item table.
-"qFilter_LibrarySearchResults" is a query of the selected items. The query contains a field, "Location" which is a text field, and the text is the file path to the item's folder on the server.
I know VERY VERY little about VBA. I am attempting to use a form to select the items I need, then click a VBA controlled button to copy the needed folders from the server to my desktop so I can work with these items more easily.
Within my VBA I have created a dao recordset from the query field "Location" and then tried to use Scripting.FileSystemObject to copy the folder from A to B. But it doesn't work and my recordset only returns NOTHING.
Private Sub Command62_Click()
Dim db As Database
Set db = CurrentDb
Dim rst As dao.Recordset
Set rst = CurrentDb.OpenRecordset("Select Location From qFilter_LibrarySearchResults")
Dim objFSO As New FileSystemObject
Set objFSO = CreateObject("Scripting.FileSystemObject")
objFSO.copyFolder Location, "C:\Users\drawingcoordinator\Desktop\"
Do While Not rst.EOF
rst.MoveNext
Loop
End Sub
The above is supposed to create a record set of file paths (Location) for the selected items, and then move through the list copying one folder after the another to my desktop, then stop at the end of the list.
When I click the button in my form to run the VBA I get
Run-time error '5': Invalid procedure call or argument.
When I attempt to Debug, the line "objFSO.copyFolder Location, "C:\Users\drawingcoordinator\Desktop\" is highlighted yellow and hovering over "location" shows Location=Empty.
The copy operation must be inside the loop (between the lines Do While ... and rst.MoveNext) to be repeated for every location in the recordset.
Then you must use the field of the recordset. I.e., rst!Location instead of simply Location which must be some variable whose declaration I do not see (have you set Option Explicit on top of the code module? Which I strongly recommend.).
Private Sub Command62_Click()
Dim db As Database
Set db = CurrentDb
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("Select Location From qFilter_LibrarySearchResults")
Dim objFSO As Object
Set objFSO = CreateObject("Scripting.FileSystemObject")
Do While Not rst.EOF
objFSO.copyFolder rst!Location, "C:\Users\drawingcoordinator\Desktop\"
rst.MoveNext
Loop
End Sub
Also, I recommend to rename the buttons before you create the Click Subroutine, so that you get speaking names. E.g. for a button named CopyFoldersCommand you get a Sub named CopyFoldersCommand_Click which is more readable than Command62_Click.
Another issue is that you created the FileSystemObject twice. Once with New FileSystemObject and once with CreateObject("Scripting.FileSystemObject"). It does not create an error but is nonsensical.
Better declare the variable with Dim objFSO As Object and then create the object with CreateObject. This has the advantage that the Scripting library needs not to be referenced which increases the reliability of the application.