VBA访问从一个DAO.Recordset插入到另一个DAO

Question

在以下代码中

    Dim sqlStr As String
    Dim OrgID As Long
    Dim wrk As DAO.Workspace
    Dim db As DAO.Database
    Dim orgRS As DAO.Recordset
    Dim staffRS As DAO.Recordset
    Set wrk = DBEngine.Workspaces(0)
    Set db = CurrentDb
    On Error GoTo trans_Err
        InsertOrganizationIntoTemp
    'if staff fields are not blank Insert Staff into Temp
        If (addStaffClickCheck = True) Then
            staffNumber = staffNumber + 1
            InsertStaffIntoTemp staffNumber
        End If
    wrk.BeginTrans
        sqlStr = "INSERT INTO tblOrganization(OrganizationName, Affiliate, " _
            & " UnionCouncil, DateJoined, OrganizationPhoneNumber, OrganizationFaxNumber, " _
            & " MembershipGroup, TradeGroup, URL) " _
            & " SELECT OrganizationName, Affiliate, CouncilUnion, DateJoined, OrganizationPhone, " _
            & " OrganizationFax, MemberGroup, Trade, OrganizationWebsite " _
            & " FROM tblTempOrganization;"
        db.Execute sqlStr
        OrgID = db.OpenRecordset("SELECT @@IDENTITY")(0)
        sqlStr = "INSERT INTO tblAddresses(StreetName, CityName, StateName, " _
            & " ZipCode, LocationID, OrganizationID) " _
            & " SELECT OrganizationAddress, OrganizationCity, OrganizationState, " _
            & " OrganizationZIP, OrganizationLocationType, '" & OrgID & "' " _
            & " FROM tblTempOrganization;"
        db.Execute sqlStr
        'pull all staff and phones into two recordsets
        'loop through staff and for each staff member add associated phone information
        'Recordsets for temporary staff tables
        Dim staffTempInfoRS As DAO.Recordset
        Dim phoneTempInfoRS As DAO.Recordset
        Set staffTempInfoRS = db.OpenRecordset("SELECT * FROM tblTempStaff", dbOpenDynaset)
        'Recordsets for permanant staff tables

        Dim StaffAddressID As Long
        Dim StaffID As Long
        'Check to see if the recordset actually contains rows
        If Not (staffTempInfoRS.EOF And staffTempInfoRS.BOF) Then
            staffTempInfoRS.MoveFirst 'Unnecessary in this case, but still a good habit
            Do Until staffTempInfoRS.EOF = True
                'address information
                Dim staffAddressDBRS As DAO.Recordset
                Set staffAddressDBRS = db.OpenRecordset("tblStaffAddresses", dbOpenDynaset)
                    With staffAddressDBRS
                        .AddNew
                            .Fields("StaffStreet") = staffTempInfoRS.Fields("StaffStreet")
                            .Fields("StaffCity") = staffTempInfoRS.Fields("StaffCity")
                            .Fields("StaffState") = staffTempInfoRS.Fields("StaffState")
                            .Fields("StaffZip") = staffTempInfoRS.Fields("StaffZip")
                            .Update
                    End With
                StaffAddressID = staffAddressDBRS.LastModified
                staffAddressDBRS.Close
                Set staffAddressDBRS = Nothing
                'staff information
                Dim staffInfoDBRS As DAO.Recordset
                Set staffInfoDBRS = db.OpenRecordset("tblStaff", dbOpenDynaset)
                    With staffInfoDBRS
                            .AddNew
                                .Fields("StaffFirstName") = staffTempInfoRS.Fields("StaffFirstName")
                                .Fields("StaffLastName") = staffTempInfoRS.Fields("StaffLastName")
                                .Fields("Email") = staffTempInfoRS.Fields("Email")
                                .Fields("StaffAddressID") = StaffAddressID
                                .Fields("OrganizationID") = OrgID
                                .Fields("Position") = staffTempInfoRS.Fields("StaffPosition")
                                .Update
                        End With
                Dim currPos As Long
                currPos = staffTempInfoRS.Fields("StaffNumber")
                StaffID = staffInfoDBRS.LastModified
                staffInfoDBRS.Close
                Set staffInfoDBRS = Nothing
                'staff phone information
                Set phoneTempInfoRS = db.OpenRecordset("SELECT * FROM tblTempPhones WHERE StaffNumber = " & currPos & ";")
                    If Not (phoneTempInfoRS.EOF And phoneTempInfoRS.BOF) Then
                    phoneTempInfoRS.MoveFirst 'Unnecessary in this case, but still a good habit
                        Do Until phoneTempInfoRS.EOF = True
                            Dim phoneInfoDBRS As DAO.Recordset
                            Set phoneInfoDBRS = db.OpenRecordset("tblStaffPhoneNumbers", dbOpenDynaset)
                            With phoneInfoDBRS
                            .AddNew
                                .Fields("PhoneNumber") = phoneTempInfoRS.Fields("StaffPhoneNumber")
                                .Fields("PhoneTypeID") = phoneTempInfoRS.Fields("PhoneType")
                                .Fields("StaffID") = StaffID
                                .Update
                            End With
                            phoneTempInfoRS.MoveNext
                        Loop
                    Else
                        Resume Next
                    End If
                    MsgBox "Finished looping through phone records."
                    phoneTempInfoRS.Close
                    Set phoneTempInfoRS = Nothing

                'Move to the next record. Don't ever forget to do this.
                staffTempInfoRS.MoveNext
            Loop
        Else
            MsgBox "There are no records in the staff recordset."
        End If
        MsgBox "Finished looping through staff records."
        staffTempInfoRS.Close 'Close the recordset
        Set staffTempInfoRS = Nothing 'Clean up
        wrk.CommitTrans
trans_Exit:
    'Clean up
    wrk.Close
    Set db = Nothing
    Set wrk = Nothing
    Exit Function
 trans_Err:
    'Roll back the transaction
    MsgBox "Whoops! We got errors"
    wrk.Rollback

恢复传输退出（_E）

当我通过这个步骤时，我在这一行中得到一个错误：

.Fields（"StaffStreet"）=staffTempInfoRS.Fields

上面写着：在集合中找不到项。

但是，这正是记录集设置为打开的表中的字段名。

Answer 1

30个小时后，我似乎犯了一个新手错误，尽管反复检查，但没有发现字段名称确实拼写错误。

我还将记录集设置在循环之外，并在退出片段中全部取消设置。。。