Results 1 to 10 of 10
  1. #1
    Join Date
    May 2006
    Posts
    31

    Unanswered: compare 2 recordsets - update differences

    I need to update TT_GeneralInfo with any changes found in tblEmployee. Here is what I have so far

    Code:
    Private Sub cmdValidateGeneralInfo_Click()
    
    On Error GoTo Err_cmdValidateGeneralInfo_Click
    
    Dim F As DAO.Field
    Dim rs As DAO.Recordset
    Dim rs1 As DAO.Recordset
    
    Set curDB = CurrentDb()
    
        If Me.DateModified = Date Then
            'Adds new employees to the TT_GeneralInfo table in the FTEI_PhoneBook.mdb - which is used thru out the AP databases.
         '   DoCmd.OpenQuery "qryEmpData_TT_General"
    
        strSQL = "SELECT Name, LastName, FirstName, MidName, PrfName, BEMS, NT_UserId," & _
                    " StableEmail, WrkPhNo, WrkPhNo2, PagerPh, Org, MS, Bldg, Cub, AltBldg, AltCub, RevDt, EmplClass" & _
                    " FROM TT_GeneralInfo" & _
                    " ORDER BY BEMS"
        Set rs = curDB.OpenRecordset(strSQL)
        
        strSQL1 = "SELECT [LastName] & ', ' & Left([FirstName],1) & '.' & IIf(IsNull([Midname]),'',Left([MidName],1) & '. ') & IIf(IsNull([PrfName]),'','(' & [PrfName] & ')') AS Name," & _
                    " LastName, FirstName, MidName, PrfName," & _
                    " BEMS, NT_UserId, StableEmail, WrkPhNo, WrkPhNo2, PagerPh, tblOrgListing_lkup.Org, MailStop as MS, Bldg_Primary as Bldg," & _
                    " Col_Cub_Primary as Cub, Bldg_Alt as AltBldg, Col_Cub_Alt as AltCub, Date() AS RevDt, tblEmplClass_lkup.EmplClass" & _
                " FROM (tblEmployee LEFT JOIN tblEmplClass_lkup ON tblEmployee.ClassCtr = tblEmplClass_lkup.ClassCtr) LEFT JOIN tblOrgListing_lkup ON tblEmployee.Mgr_OrgNo = tblOrgListing_lkup.OrgCtr" & _
                " WHERE (((tblOrgListing_lkup.UpdateGeneralInfo)=-1) AND ((tblEmployee.Active)=-1))" & _
                " ORDER BY BEMS"
        Set rs1 = curDB.OpenRecordset(strSQL1)
    
             While Not rs1.EOF
                For Each F In rs1.Fields
                    If F.value <> rs(F.Name) Then
                        rs.Edit
                        If Nz(F.value, "") <> "" Then
                          rs(F.value) = F.value
                        End If
                        rs.Update
                    End If
                Next F
                rs1.MoveNext
            Wend
            rs1.Close
        End If
    '
    '    stDocName = "QS_EMD_tblEmployee vs TT_GeneralInfo"
    '    DoCmd.OpenQuery stDocName, acNormal, acReadOnly
    
    Exit_cmdValidateGeneralInfo_Click:
        Exit Sub
    
    Err_cmdValidateGeneralInfo_Click:
        MsgBox Err.Description
        Resume Exit_cmdValidateGeneralInfo_Click
    
       On Error GoTo 0
       Exit Sub
    
    cmdValidateGeneralInfo_Click_Error:
    
        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdValidateGeneralInfo_Click of VBA Document Form_frmEmpMain"
        
    End Sub
    The problem is with this portion of the code:
    Code:
            While Not rs1.EOF
                For Each F In rs1.Fields
                    If F.value <> rs(F.Name) Then
                        rs.Edit
                        If Nz(F.value, "") <> "" Then
                          rs(F.value) = F.value
                        End If
                        rs.Update
                    End If
                Next F
                rs1.MoveNext
            Wend
    I get an Item not found in collection or Datatype conversion error.

    This leads me to believe that the syntax is incorrect for passing the new value to the recordset.

    ie.
    F.value = "Joe Smith"
    rs(F.name) = "Mike Jones"

    The current changes to the code does not allow for the update of the new data.

    Not sure that the F.value is the correct syntax for the updating of the data?

    or should it include the rs1, some how?

    rs(F.value) = F.value

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. F.Value is syntactically correct.

    2. You looop through the Fields collection of the recordset rs1 and address a field with a matching name in the recordset rs (which is correct), however both recordsets do not have the same number of fields and this is a problem:
    Code:
    rs1		        rs
    ------------------------------------
    LastName	<==>	LastName
    Name		<==>	Name
    LastName	<==>    [Appears twice]
    FirstName	<==>	FirstName
    MidName		<==>	MidName
    PrfName		<==>	PrfName
    BEMS		<==>	BEMS
    NT_UserId	<==>	NT_UserId
    StableEmail	<==>	StableEmail
    WrkPhNo		<==>	WrkPhNo
    WrkPhNo2	<==>	WrkPhNo2
    PagerPh		<==>	PagerPh
    Org		<==>	Org
    MailStop 	<==>	[MISSING]
    MS		<==>	MS
    Bldg		<==>	Bldg
    Cub		<==>	Cub
    AltBldg		<==>	AltBldg
    AltCub		<==>	AltCub
    RevDt		<==>	RevDt
    EmplClass	<==>	EmplClass
    When F.Name = "MailStop" no matching name can be found in the Fields collection of rs.

    Moreover, "LastName" appears twice in the Fields collection of rs1. Chances are that the second occurence of the fields receives an implicit alias (probably: "LastName1") which also does not have a match in the Fields collection of rs. This causes the error "Item not found in collection".
    Have a nice day!

  3. #3
    Join Date
    May 2006
    Posts
    31
    Thanks for your input, however, Last name is not a duplicate, the first lastname field you see is part of the concatenation of all the name parts as "NAME".

    SELECT [LastName] & ', ' & Left([FirstName],1) & '.' & IIf(IsNull([Midname]),'',Left([MidName],1) & '. ') & IIf(IsNull([PrfName]),'','(' & [PrfName] & ')') AS Name

    Also MailStop is renamed "MS" to match both recordsets. ie. MailStop as MS

    k

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're right!

    Look at:
    Code:
    rs(F.value) = F.value
    It should be:
    Code:
    rs(F.Name).Value = F.value
    Have a nice day!

  5. #5
    Join Date
    May 2006
    Posts
    31
    it is still return and update the same employee's data for all records. Don't I need something the says When BEMS (Employee ID)= BEMS then Update and compare records?

    K

  6. #6
    Join Date
    May 2006
    Posts
    31
    SOMETHING LIKE:
    Code:
            Do Until rs.EOF
                If rs.Fields("BEMS") = rs1.Fields("BEMS") Then
                    For Each F In rs.Fields
                        If rs.Fields(F.Name) <> rs1.Fields(F.Name) Then
                            rs.Edit
                                rs(F.Name).value = rs1(F.Name).value
                            rs.Update
                        End If
                    Next F
                rs.MoveNext
            Loop
                End If
    currently does not like the LOOP - probably in the wrong place???

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This is normal es you never change the current record of the recordset rs.You should add (in the loop):
    Code:
    rs.MoveNext
    Have a nice day!

  8. #8
    Join Date
    May 2006
    Posts
    31
    so what should the code look like?

  9. #9
    Join Date
    May 2006
    Posts
    31
    still doesn't like the LOOP.

    Code:
        Set rs1 = curDB.OpenRecordset(strSQL1)
            rs.MoveFirst
            Do Until rs.EOF
                If rs.Fields("BEMS") = rs1.Fields("BEMS") Then
                    For Each F In rs.Fields
                        If rs.Fields(F.Name) <> rs1.Fields(F.Name) Then
                            rs.Edit
                                rs(F.Name).value = rs1(F.Name).value
                            rs.Update
                        End If
                    Next F
                rs.MoveNext
            Loop
                End If

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try something like:
    Code:
    Do While Not rs1.EOF
        For Each F In rs1.Fields
            If F.value <> rs(F.Name) Then
                rs.Edit
                If Nz(F.value, "") <> "" Then
                  rs(F.Name).value = F.value
                End If
                rs.Update
            End If
        Next F
        rs1.MoveNext
        rs.MoveNext
    Loop
    However there will be a problem if both recordsets do not have the same number of records.
    Have a nice day!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •