在 MS Access 中使用 VBA 将附件从一个记录集中的记录复制到另一个记录集中

我几乎已经完成了这项工作。我在想我真的只需要另一双眼睛。我认为我的问题只是一个简单的编程结构问题。循环过多或以错误的顺序打开/关闭记录集。

我正在尝试将记录中包含的所有附件文件从一个 dao 记录集中复制到另一个 dao 记录集中的相应记录中。两个记录集都从同一个表中提取数据。第一个记录集 (rstOld) 包含具有去年日期值的记录，这些记录可以包含任意数量的附件。第二个记录集 (rstNew) 包含具有今年日期值的记录，并且这些记录不包含任何附件。

为此，我开始循环遍历 rstNew 中的每条记录。对于 rstNew 中的每条记录，我将收集 Name 字段的值，然后开始第二个循环。第二个循环将在 rstOld 中找到具有匹配名称字段的记录。从那里我只需将所有附件从 rstOld 中的记录复制到 rstNew 中的记录。

奇怪的是，它会在 rstNew 中找到匹配项的第一条记录上正常工作。之后，它不再适用于任何后续记录。

到目前为止，这是我的代码：

    Dim db As Database
    Dim strOldSQL As String
    Dim rstOld As DAO.Recordset2
    Dim strNewSQL As String
    Dim rstNew As DAO.Recordset2
    Dim rstOldAttachments As DAO.Recordset2
    Dim rstNewAttachments As DAO.Recordset2
    Dim strCurrentSiteName As String
    Dim strOldSiteName As String
Set db = CurrentDb()

    'First let's open a recordset that contains all of the records from this year.
    strNewSQL = "SELECT tblAuditForms.SiteName, tblAuditForms.Attachments, tblAuditForms.AuditYear FROM tblAuditForms WHERE AuditYear = #" & Format(cboMyDate, "mm/dd/yyyy") & "# ORDER By tblAuditForms.SiteName;"
    Set rstNew = db.OpenRecordset(strNewSQL)
    rstNew.MoveFirst
    rstNew.Edit
    
    Do While Not rstNew.EOF 'Now we need to loop through these records.
    
        strCurrentSiteName = rstNew.Fields("SiteName").Value 'Get the name of the site for the current record that we're on. We'll use this to compare with the sites in the previous audit.
                    
        'Now let's open a recordset that contains all records from the previous audit.
        strOldSQL = "SELECT tblAuditForms.SiteName, tblAuditForms.Attachments, Year([AuditYear]) FROM tblAuditForms WHERE Year([AuditYear]) = " & Me.cboPreviousDate & " ORDER BY tblAuditForms.SiteName;"
        Set rstOld = db.OpenRecordset(strOldSQL)
        rstOld.MoveFirst
        
        Do While Not rstOld.EOF 'Loop through each of the records from the previous audit until we find a record that matches the current site name.
        
            strOldSiteName = rstOld.Fields("SiteName").Value
        
            If strCurrentSiteName = strOldSiteName Then 'If this is true, then we've found a record from the previous audit that matches the one from our current audit.
                'Now it's just a matter of copying the attachments from the old record into the new one.  Working with attachments is annoying though.
                
                'This next block should loop through the attachments (if any) in the old record and copy them into the new record.
                Set rstOldAttachments = rstOld.Fields("Attachments").Value
                rstOldAttachments.MoveFirst
                
                Set rstNewAttachments = rstNew.Fields("Attachments").Value

                Do While Not rstOldAttachments.EOF
                    
                    rstNewAttachments.AddNew
                    rstNewAttachments.Fields("FileData").Value = rstOldAttachments.Fields("FileData").Value
                    rstNewAttachments.Fields("FileName").Value = rstOldAttachments.Fields("FileName").Value
                    rstNewAttachments.Fields("FileType").Value = rstOldAttachments.Fields("FileType").Value
                    rstNewAttachments.Update
                
                    rstOldAttachments.MoveNext
                Loop
                
                'Now that we've found the site from the previous audit and copied its attachments into the new record we can close the old recordset and move onto the next site in the current audit.
                rstOldAttachments.Close
                rstNewAttachments.Close
                Exit Do
            
            End If
                        
            rstOld.MoveNext
        Loop
         
        rstOld.Close
        rstNew.Update
        rstNew.MoveNext
    Loop
        
    'If we've gotten this far then we've looped through all of the new records that we just created from the weekly staffing workbook.
    rstNew.Close
    
    

就像我之前说的，这段代码将在通过 rstNew 记录集的第一个循环上工作，但在任何后续循环上都不起作用。我是否过早地摆脱了循环？或过早关闭记录集？