记录集编辑和更新错误的记录 [英] Recordset Edits and Updates the Wrong Record

问题描述

我有以下代码循环两个表并将它们合并到一个新表中：

I have the following code to loop through two tables and merge them into a new table:

Public Function MyFunction()
Dim Db As DAO.Database
Dim rst(1 To 3) As DAO.Recordset
Dim fld As DAO.Field
Dim fldname, fldtype As String
Dim PxID As Integer
Dim Iter, Counter As Integer

Set Db = CurrentDb
Set rst(1) = Db.OpenRecordset("Table1")

Call PrepTable                       ' Creates table named Test

rst(1).MoveFirst

Do While Not rst(1).EOF
    PxID = rst(1)!PersonID
    Set rst(2) = Db.OpenRecordset("SELECT * FROM Table2 WHERE PersonID=" & PxID)

    If rst(2).RecordCount > 0 Then
        rst(2).MoveLast
        'set limit to 4 records if recordcount > 4
        Iter = IIf(rst(2).RecordCount > 4, 4, rst(2).RecordCount)          
        rst(2).MoveFirst

        For Counter = 1 To Iter
            For Each fld In rst(2).Fields
                If fld.OrdinalPosition = 0 Then
                    fldname = "PersonID"
                Else
                    fldname = fld.Name & Trim(Str(Counter))
                End If

                If Not IsNull(fld.Value) Then
                    Set rst(3) = Db.OpenRecordset("Test")
                    'create new record on Test only if on new record on Table2
                    If (fldname = "PersonID" And Counter = 1) Then       
                        rst(3).AddNew
                    Else
                        rst(3).Move 0
                        rst(3).Edit
                    End If
                    rst(3)(fldname).Value = fld.Value
                    rst(3).Update
                    rst(3).Bookmark = rst(3).LastModified                     'not sure about this at all
                End If
            Next

        rst(2).MoveNext
        Next
        rst(3).Close
    End If

    rst(2).Close
    Set rst(2) = Nothing
    Set rst(3) = Nothing
    rst(1).MoveNext
Loop
rst(1).Close
Set rst(1) = Nothing

Debug.Print "Done."
Db.TableDefs.Refresh
DoCmd.OpenTable "Test", acViewNormal

End Function

表1包含10条记录。此函数在Test表上正确创建10条记录。但是，仅第一条记录正在更新（导致新数据覆盖旧数据）。除表Test的自动编号字段和PersonID字段外，最后9条记录为空白。

Table1 contains 10 records. This function correctly creates 10 records on the Test table. However, only the first record is being updated (causing new data to overwrite the old). The last 9 records are blank save for the autonumber field of table Test and the PersonID field.

基本问题是：如何移动到下一行

推荐答案

您要完成的工作实际上还不是很清楚。

据我了解，您正在尝试将 Table2 的前4条记录转置为表 Temp 。

What you are trying to accomplish is really not entirely clear.
From what I understand, you are trying to transpose the first 4 records of Table2 into columns in table Temp.

在这里，您要遍历的每个字段都打开 rs（3），但是您永远不要在那个循环中关闭它；您可以在循环外将其关闭，甚至不能将其打开。

Here, you are opening your rs(3) every for every field you loop through, but you never close it within that loop; you close it outside of the loop, at a level where it may not even be open...

因此，第一件事就是将在所有循环之外设置rst（3）= Db.OpenRecordset（ Test）。

So, first thing is to move that Set rst(3) = Db.OpenRecordset("Test") outside of all the loops.

然后，您不清楚为什么要这样做与 rst（3）。移动0 和 rst（3）。书签= rst（3）.LastModified 。

添加新记录后，您无需再次调用 Edit 或在记录和书本中移动。您需要做的就是确保您调用 rst（3）。在复制所有字段数据之后， 。



Then it's not clear why you are doing with the rst(3).Move 0 and the rst(3).Bookmark = rst(3).LastModified.
Once you have added a new record, you do not need to call Edit on it again, or move around records and bookmakrs. All you need to do is make sure you call rst(3).Update after you copied all the field data.

Public Function MyFunction()
Dim Db As DAO.Database
Dim rst(1 To 3) As DAO.Recordset
Dim fld As DAO.Field
Dim fldname, fldtype As String
Dim PxID As Integer
Dim Iter, Counter As Integer

Set Db = CurrentDb
Set rst(1) = Db.OpenRecordset("Table1")

Call PrepTable                       ' Creates table named Test

rst(1).MoveFirst
Set rst(3) = Db.OpenRecordset("Test")
Do While Not rst(1).EOF
    PxID = rst(1)!PersonID
    Set rst(2) = Db.OpenRecordset("SELECT * FROM Table2 WHERE PersonID=" & PxID)

    If rst(2).RecordCount > 0 Then
        rst(2).MoveLast
        'set limit to 4 records if recordcount > 4
        Iter = IIf(rst(2).RecordCount > 4, 4, rst(2).RecordCount)          
        rst(2).MoveFirst

        For Counter = 1 To Iter
            For Each fld In rst(2).Fields
                If fld.OrdinalPosition = 0 Then
                    fldname = "PersonID"
                Else
                    fldname = fld.Name & Trim(Str(Counter))
                End If

                If Not IsNull(fld.Value) Then
                    'create new record on Test only if on new record on Table2
                    If (fldname = "PersonID" And Counter = 1) Then       
                        rst(3).AddNew
                    End If
                    rst(3)(fldname).Value = fld.Value
                End If
            Next
            If rs(3).EditMode <> dbEditNone Then
                rst(3).Update
            End If
            rst(2).MoveNext
        Next
    End If
    rst(2).Close
    Set rst(2) = Nothing
    rst(1).MoveNext
Loop
rst(3).Close
rst(1).Close
Set rst(3) = Nothing
Set rst(1) = Nothing

Debug.Print "Done."
Db.TableDefs.Refresh
DoCmd.OpenTable "Test", acViewNormal

End Function

我并不是说这会起作用，您当然可以清理代码中的逻辑，但这应该会使它变得更好一些。

I'm not saying this will work, and you could certainly clean up the logic in that code, but this should make it a bit better already.

这篇关于记录集编辑和更新错误的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！