使用VBA的MS Access复制记录集 [英] MS access copying recordset using vba

问题描述

已更新:

我正在尝试设置一个任务"表单，当您单击一个按钮时，它将获取[状态]为10的所有记录并将其复制，然后以前的记录将被标记为[状态] 100.

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:

因此，该代码将复制[状态]为10(进行中")的所有记录，将旧记录标记为[状态] 100(已完成")，并为[日期已完成]字段加上时间戳.

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.

代码如下:

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

推荐答案

您似乎没有对Recordset做任何事情.

You dont appear to be doing anything to the Recordset.

这些:

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

这篇关于使用VBA的MS Access复制记录集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！