Copy files from and to changing path

[aadpors]

I automated a copy-paste procedure that runs upon clicking a button, see code underneeth.

Private Sub Command122_Click()
Dim rs As dao.Recordset, strSource, strDest
strSource = "D:\FolderA\"
 strDest = "D:\FolderB\"
Set rs = CurrentDb.OpenRecordset("select * from tbl_archief where nieuw=true")

If rs.EOF Then Exit Sub

Do Until rs.EOF
       FileCopy strSource & rs![file], strDest & rs![file]

rs.MoveNext
Loop
End Sub

All of this works fine, as long as I have the data in the folder "D:\FolderA\". However, since this database is going to be used by others as well I have created the table tbl_path with two fields (1 record): 'source' and 'destination'. These cells contain the respetive paths to the source and destination folders. How do I link strSource and strDest to these values?

 

[AOB]

Assign the string variables with a DLookup?

strSource = DLookup("[source]", "[tbl_path]", "[Criteria] = True")
strDest = DLookup("[destination]", "[tbl_path]", "[Criteria] = True")

Or use a second recordset?

Dim rsPath as Recordset
 
Set rsPath = CurrentDb.OpenRecordset("SELECT [tbl_path].[source], [tbl_path].[destination] " & _
                                       "FROM [tbl_path] " & _
                                       "WHERE [Criteria] = True")
 
....
 
Do Until rs.EOF
 
    FileCopy rsPath![source] & rs![file], rsPath![destination] & rs![file]
 
rs.MoveNext

But strongly advise you add some handling to validate that both source & destination directories exist prior to attempting to copy from one to the other...

 

[aadpors]

Thanks for the reply! I tried the first option (removed the criteria) and added an error handler. If a folder or file is not found Access jumps to the debugger anyway.