FileCopy command for each record in recordset

[Wouter Raats]

I have a problem with trying to execute a command for each record in a recordset. What I'm trying to build is a file distribution system. I have a form with the path where the source file is and a subform with a couple of records where the destination path is defined. I use the code you will find below, but it will only copy the file to the destination from the first record. Can somebody help me to solve the problem? So the code will do the filecopy command for every destination.

  Dim Sourcepath, Destinationpath
  Sourcepath = Forms![Item distribution]![Item source path]
  Destinationpath = Me.Destination_path
  Dim rs As DAO.Recordset
  Set rs = Me.Form.Recordset

  If Not (rs.BOF And rs.EOF) Then
    rs.MoveFirst
    FileCopy Sourcepath, Destinationpath
  Else
    Do While Not rs.EOF = True
      rs.Edit
      FileCopy Sourcepath, Destinationpath
      rs.Update
      rs.MoveNext
      Loop
  End If

 

[namliam]

If Not (rs.BOF And rs.EOF) Then
This is meant to detect if there is only one record, by putting NOT you are saying if there are more records... remove the NOT here.

Do While Not rs.EOF = True
Try changing that to
Do While Not rs.EOF

No need for the rs.edit and rs.update, you are not changing any data.

 

[contractor]

Hi Wouter,

Try to change your code as following :

If rs.RecordCount <> 0 Then
    rs.MoveFirst
    Do Until rs.EOF
        rs.Edit
        FileCopy Sourcepath, Destinationpath
        rs.Update
        rs.MoveNext
    Loop
End If

 

[Wouter Raats]

If Not (rs.BOF And rs.EOF) Then
This is meant to detect if there is only one record, by putting NOT you are saying if there are more records... remove the NOT here.

Do While Not rs.EOF = True
Try changing that to
Do While Not rs.EOF

No need for the rs.edit and rs.update, you are not changing any data.

Thanks for your reply, I did change the code as you told me to but it is still only copying to the destination from the first record. I do not understand why because there are 3 records in total.

 

[namliam]

You are only picking up your destination once...
Destinationpath = Me.Destination_path

and from your form, ever are you getting any destination from your RS

 

[Wouter Raats]

You are only picking up your destination once...
Destinationpath = Me.Destination_path

and from your form, ever are you getting any destination from your RS

So I should change the rs, so that is does contain the source and the destination.

 

[Wouter Raats]

You are only picking up your destination once...
Destinationpath = Me.Destination_path

and from your form, ever are you getting any destination from your RS

So i have changed the code, see below. But now I receive a message Too few parameters. Expected 1.

Dim Sourcepath, Destinationpath
  Dim rs As DAO.Recordset
  Set rs = CurrentDb.OpenRecordset("Param")
  Sourcepath = rs!Source
  Destinationpath = rs!DEST
  If (rs.BOF And rs.EOF) Then
    rs.MoveFirst
    FileCopy Sourcepath, Destinationpath
  Else
    Do While Not rs.EOF
      FileCopy Sourcepath, Destinationpath
      rs.MoveNext
    Loop
  End If

 

[namliam]

no... that is if your source is only one then you are fine using the
Sourcepath = Forms![Item distribution]![Item source path]
Getting your sourcepath from your form is fine...

You however are also getting your destination from your form, though in another format... Me.Destination_path
Insteat you should be getting your destination from your recordset (rs.Destination_path)
And not only get it one time but each time you have a new record inside your Do While / Loop

 

[namliam]

Something like:

  Dim Sourcepath, Destinationpath
  Sourcepath = Forms![Item distribution]![Item source path]
  Dim rs As DAO.Recordset
  Set rs = Me.recordsetclone

  If (rs.BOF And rs.EOF) Then
    rs.MoveFirst
    Destinationpath = RS.Destination_path
    FileCopy Sourcepath, Destinationpath
  Else
    Do While Not rs.EOF
      Destinationpath = RS.Destination_path
      FileCopy Sourcepath, Destinationpath
      rs.MoveNext
      Loop
  End If

 

[Wouter Raats]

Something like:

  Dim Sourcepath, Destinationpath
  Sourcepath = Forms![Item distribution]![Item source path]
  Dim rs As DAO.Recordset
  Set rs = Me.recordsetclone
 
  If (rs.BOF And rs.EOF) Then
    rs.MoveFirst
    Destinationpath = RS.Destination_path
    FileCopy Sourcepath, Destinationpath
  Else
    Do While Not rs.EOF
      Destinationpath = RS.Destination_path
      FileCopy Sourcepath, Destinationpath
      rs.MoveNext
      Loop
  End If

Many Thanks for all your replies and your expensive time. But the code is still not doing his job. Below you'll find the code and attached a screenshot from the form. The code will be used if i hit the buton Command11.

Private Sub Command11_Click()
Dim Sourcepath, Destinationpath
  Sourcepath = Forms![Item distribution]![Item source path]
  Dim rs As DAO.Recordset
  Set rs = Me.RecordsetClone
  If (rs.BOF And rs.EOF) Then
    rs.MoveFirst
    Destinationpath = rs![Destination path]
    FileCopy Sourcepath, Destinationpath
  Else
    Do While Not rs.EOF
      Destinationpath = rs![Destination path]
      FileCopy Sourcepath, Destinationpath
      rs.MoveNext
      Loop
  End If
End Sub

 

[namliam]

Hey, You are dutch too, greets from Amsterdam

Try this

Private Sub Command11_Click()
Dim Sourcepath as string, Destinationpath as string
  Sourcepath = Forms![Item distribution]![Item source path]
  Dim rs As DAO.Recordset
  Set rs = Me.RecordsetClone
rs.movelast
rs.MoveFirst
  If (rs.BOF And rs.EOF) Then
    Destinationpath = rs![Destination path]
    FileCopy Sourcepath, Destinationpath
  Else
    Do While Not rs.EOF
      Destinationpath = rs![Destination path]
Debug.print SourcePath, DestinationPath
      FileCopy Sourcepath, Destinationpath
      rs.MoveNext
      Loop
  End If
End Sub

 

[DavidAtWork]

You may have to reconsider the logic in what your trying to do.
If each item can have multiple destination paths, then you need to query the source data to retrieve all the destination paths for that item. Use that query SQL as the recordset source and iterate through those records, setting the destination path value for each record and then use your

FileCopy Sourcepath, Destinationpath

David

 

[Wouter Raats]

Hey, You are dutch too, greets from Amsterdam

Try this

Private Sub Command11_Click()
Dim Sourcepath as string, Destinationpath as string
  Sourcepath = Forms![Item distribution]![Item source path]
  Dim rs As DAO.Recordset
  Set rs = Me.RecordsetClone
rs.movelast
rs.MoveFirst
  If (rs.BOF And rs.EOF) Then
    Destinationpath = rs![Destination path]
    FileCopy Sourcepath, Destinationpath
  Else
    Do While Not rs.EOF
      Destinationpath = rs![Destination path]
Debug.print SourcePath, DestinationPath
      FileCopy Sourcepath, Destinationpath
      rs.MoveNext
      Loop
  End If
End Sub

YES! THIS WORKS! MANY MANY THANKS. Greetings from Moerdijk!