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

    Unanswered: compare 2 recordsets - update differences

    Need help with the best approach to how to update identical tables (structure)
    with the differences in data. here is what I have so far see '>>>>>>>

    Code:
    Private Sub cmdValidateGeneralInfo_Click()
    
    On Error GoTo Err_cmdValidateGeneralInfo_Click
    
    Dim stDocName As String
    Dim F As DAO.Fields
    Dim nName As DAO.Fields
       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.
           curDB.Execute "qryEmpData_TT_General", dbFailOnError
     
    
       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"
       
       strSQL1 = "SELECT [LastName] & ', ' & Left([FirstName],1) & '.' & IIf
    (IsNull([Midname]),'',Left([MidName],1) & '. ') & IIf(IsNull([PrfName]),'','
    (' & [PrfName] & ')') AS Name," & _
                   " LastName, FirstName, MidName, PrfName," & _
                   " CStr([BEMS]) AS BEMSID, 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 tblOrgListing_lkup RIGHT JOIN ((tblEmployee LEFT JOIN
    qryUnitChief ON" & _
                       " UnitChief = qryUnitChief.UnitChiefID) LEFT JOIN
    tblEmplClass_lkup ON" & _
                       " ClassCtr = tblEmplClass_lkup.ClassCtr) ON
    tblOrgListing_lkup.OrgCtr = Mgr_OrgNo" & _
                   "WHERE ((tblOrgListing_lkup.UpdateGeneralInfo)=-1) AND (
    (Active)=-1))"
    
       Set gRS = curDB.OpenRecordset(strSQL)
       Set gRS1 = curDB.OpenRecordset(strSQL1)
    '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
            While Not gRS1.EOF
               For Each F In gRS1.Fields
                   If F.value <> gRS(F.Name) Then
                     gRS.Edit
                     'Set nName = gRS1(F.value)
                     gRS(F.value) = gRS1(F.value)
                     gRS.Update
                   End If
               Next F
               gRS.Close
               gRS1.MoveNext
           Wend
           gRS1.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

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    In your code, there are references to queries we don't know anything about ("qryEmpData_TT_General", "QS_EMD_tblEmployee vs TT_GeneralInfo"). Moreover, you don't provide any information on the tables involved in the process (name, columns name and data type). The only way we have to retrieve the information is by analyzing 2 complex dynamic SQL strings using several VBA functions (Left, IIF, IsNull, etc.).

    You don't provide any information about the form ("frmEmpMain")from which the "cmdValidateGeneralInfo_Click" procedure is supposed to run.

    Why did you chose the Recordset method, which probably is the most complex and inefficient method, instead of using SQL? Is there a special reason I did not notice when examining the code you posted?
    Have a nice day!

  3. #3
    Join Date
    May 2006
    Posts
    31
    The reason behind my choice for record set is due to the fact that one of the queries is not updatabale.. Note the values of the form and the value of the first query - does not affect the portion of the code I am attempting to fix.


    To restate my problem. I need to compare two tables, data and determine differences and where different - update the first table with the new data. I am looking for the best approach - a simple update query will not sufficient due to the fact the second record set is not an updatable query, hence the need to use the recordset method. Feel free to make any suggestions that would allow for a more streamline process.

    The issue is with field name and field value, what is the proper syntax for the See:

    nName = gRS1.Fields.Name
    gRS(F.value) = nName.value


    While Not gRS1.EOF
    For Each F In gRS1.Fields
    If F.value <> gRS(F.Name) Then
    gRS.Edit
    nName = gRS1.Fields.Name
    gRS(F.value) = nName.value
    gRS.Update
    End If
    Next F
    gRS.Close
    gRS1.MoveNext
    Wend
    gRS1.Close

    Thanks,

    Karen
    Last edited by kfschaefer; 02-13-12 at 12:54.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    A Recordset object has a Fields collection.
    - To refer to a Field object in the Fields collection of a Recordset (Rs) by its ordinal number, use the following syntax
    Code:
    Rs.Fields(0)
    Each Field object has a Name property.
    - To refer to a Field object in the Fields collection of a Recordset (Rs) by its Name property, use the following syntax:
    Code:
    Rs.Fields("name")
    ' Or:
    Rs.Fields!name
    However, because the Fields collection is the default collection of a Recordset object, you may also use:
    Code:
    Rs.(0)
    ' Or:
    Rs("name")
    ' Or
    Rs!name
    See also: Fields Collection *[Access 2007 Developer Reference]
    Have a nice day!

  5. #5
    Join Date
    May 2006
    Posts
    31
    still need assistances, I tried the various suggestion w/o success. What is the best approach to compare values between two recordsets and update where needed?

    K

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
  •