MS access copying recordset using vba

Question

UPDATED:

I'm attempting to setup a form "Tasks", where when you click a button it will take all records with a [Status] of 10 and copy them, The previous records would then get marked as [Status] 100.

So what I have is the following "before" datasheet view:

Once you run the code it will look as follows:

So the code will duplicate all records that have a [Status] of 10 ("In Progress") mark the old records as [Status] 100 ("Completed") and timestamp the [Date Completed] Field.

Hopefully this helps clarify a bit more of what I'm trying to do.

Code is as follows:

Public Sub Duplicate_Click()
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblTasks")
rs.MoveFirst

If Not (rs.EOF And rs.BOF) And Me.Status = 10 Then

Do Until rs.EOF = True
'Copy/Paste In Process Tasks
    DoCmd.RunCommand acCmdSelectRecord
    DoCmd.RunCommand acCmdCopy
    DoCmd.RunCommand acCmdPasteAppend
    [Status].Value = 0
    rs.MoveNext
Loop
Else
    MsgBox ("Nothing Done")
'do nothing
End If

    MsgBox ("Complete")

rs.Close 'Close the recordset
Set rs = Nothing 'Clean up

End Sub

Answer 1

Why would you not use a simple insert query for this task?

e.g.:

insert into tblTasks
select * from tblTasks where tblTasks.status = 10

Answer 2

You dont appear to be doing anything to the Recordset.

these:

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdPasteAppend

Would only affect the active forms datasheet. Not the Recordset. also they dont move around to do what they do. These three commands would select the record, copy the record, then paste that same record at the end, and repeat.

you could open the source and the destination as recordsets and while you move through the source you add them to the destination as they have the appropriate value so something like

Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset

Set rs1 = CurrentDb.OpenRecordset("Source")
Set rs2 = CurrentDb.OpenRecordset("Destination")

rs1.MoveFirst

If Not (rs1.EOF And rs1.BOF) And Me.Status = 10 Then

Do Until rs1.EOF = True
    If(rs1![Field] = 10) Then
       With rs2
            .AddNew
            ![Field]=rs1![Field]
            .Update
       End With
    End If
    rs1.MoveNext
Loop
Else
    MsgBox ("Nothing Done")
'do nothing
End If

    MsgBox ("Complete")

rs1.Close 'Close the recordset
Set rs1 = Nothing 'Clean up
rs2.Close 'Close the recordset
Set rs2 = Nothing 'Clean up
End Sub

Answer 3

These

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdPasteAppend

work on the record currently in your form. They don't care about the recordset.

But that is probably not the only problem. I recommend that you change your question or submit a new one to include your table(s) and what exactly you are trying to accomplish with example of desired result, because I am pretty sure that even if the above lines worked as you thought they do, you still wouldn't get the desired result.