我幾乎已經完成這個工作了。我想我真的需要另一雙眼睛來關注這個問題。我認為我的問題只是一個簡單的程式結構問題。循環太多或以錯誤的順序開啟/關閉記錄集。
我正在嘗試將一個 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 記錄集的第一個循環中起作用，但在任何後續循環中不起作用。我是否太快脫離循環了？或過早關閉記錄集？
答案1
我已經想通了！我知道我已經很接近了。我了解到，一旦執行了 recordset.update （或在我的例子中為 rstNew.update）語句，記錄集 editmode 屬性將返回到 0。在任何後續循環中。所以我要做的就是將「rstNew.Edit」語句直接移到「Set rstNewAttachments = rstNew.Fields("Attachments").Value」行上方。
新程式碼如下所示：
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
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
rstNew.Edit
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