Validating updated data - comparing 2 tables to verify all records have updated correctly

Posted on 2012-08-30
Last Modified: 2012-09-14
I am attempting to create a process that will allow the user to add new employees to an existing list and then after selecting which employees should be added, then validate that all record indeed did update.

I have tried various approaches without success.
1. Do loop statement
2. comparing recordsets
3. For Each

NOTE:  PK = BEMSID (employee ID) -  New HIre Table and BEMS (EmployeeID) - Employee table
If the value in NewHire table = Employee table then I want to delete the value from the New HIre table.

What would you recommend as the best approach - Note time is of the essence.

Here is what I have so far:
Private Sub cmdAddNewHire_Click()
Dim curDB As DAO.Database
Dim rs As DAO.Recordset, rs1 As DAO.Recordset
Dim strSql, strSql1 As String

   On Error GoTo cmdAddNewHire_Click_Error

Set curDB = CurrentDb()
   DoCmd.OpenForm "frmMessage", acNormal, , , acFormReadOnly, acWindowNormal
        Forms!frmMessage.lblMsg.Caption = _
            "Please Wait while the New Hire Data is being updated from selected records"
    'Updates employee data with New Hire data when Add = Yes
    strSql = "INSERT INTO tblEmployee ( BEMS, LastName, FirstName, MidName, MailStop, Bldg_Primary," & _
                    " Col_Cub_Primary, NT_UserId, WrkPhNo, WrkPhNo2, ServiceDt, StableEmail, UnitChief, ModifiedBy," & _
                    " Active, ClassCtr, StatCtr, Mgr_OrgNo )" & _
                " SELECT tblNewHirePossible_Temp.BEMSID, tblNewHirePossible_Temp.PREFSUR," & _
                    " tblNewHirePossible_Temp.PREFGIV, tblNewHirePossible_Temp.PREFMI," & _
                    " tblNewHirePossible_Temp.FFMS, tblNewHirePossible_Temp.BUILDING," & _
                    " tblNewHirePossible_Temp.ROOM, tblNewHirePossible_Temp.PREFUSERID," & _
                    " tblNewHirePossible_Temp.HOME_OFFICE_PHONE, tblNewHirePossible_Temp.MOBILE_PHONE," & _
                    " tblNewHirePossible_Temp.EFFECT_HIRE_DATE, tblNewHirePossible_Temp.STABLE," & _
                    " tblOrgListing_lkup.UnitChief, GetUserName() AS [User], tblNewHirePossible_Temp.Add," & _
                    " CInt(IIf([MGR]='Y',2,4)) AS EmpClass, StatusType([status],[RELATIONSHIPS]) AS STATUS_1," & _
                    " CInt([tblOrgListing_lkup].[OrgCtr]) AS org" & _
                " FROM tblNewHirePossible_Temp LEFT JOIN tblOrgListing_lkup ON" & _
                    " tblNewHirePossible_Temp.OrgCtr = tblOrgListing_lkup.OrgCtr" & _
                " WHERE (((tblNewHirePossible_Temp.Add)=-1) AND ((tblOrgListing_lkup.DoNotUse)=0))"
    curDB.Execute (strSql)
    'validates the newly added employees and will display any records that have not been updated when Add = Yes
    'Removes newly added Employees from temp table that currently exists in the Employee table.
    strSql = "Select BEMSID from tblNewHirePossible_Temp Where Add = -1"
    strSql1 = "Select BEMS from tblEmployee Where (Active=-1)"
    Set rs = curDB.OpenRecordset(strSql)
    Set rs1 = curDB.OpenRecordset(strSql1)
    Do Until rs.EOF
        If rs.Fields(BEMSID) = rs1.Fields("BEMS") Then
           curDB.Execute ("Delete * from tblNewHirePossible_Temp Where BEMSID = " & rs.Fields("BEMSID"))

        End If
    Set rs = Nothing
    Set rs1 = Nothing
    If IsLoaded("frmMessage") Then
        DoCmd.Close acForm, "FrmMessage"
    End If

   On Error GoTo 0
   Exit Sub


    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdAddNewHire_Click of VBA Document Form_frmEmpMain"

End Sub

Open in new window

Question by:Karen Schaefer
    LVL 77

    Accepted Solution

    I'm not really clear what problem you have - is something going wrong (see below)  or what?

    One thing that is going wrong is that your Left join will not be working.
    You are selecting from the 'right' side of the left join.  This destroys the left join and turns it into an inner join.

    To do such a selection you must use a subquery for the right side 'table'.


    Select a.*, b.* from a left join b on a.fieldx= b.fieldy
    where b.fieldz = 123
    does not work.

    You have to do...
    Select a.*, q1.* from a left join (SElect * from b where b.fieldz = 123) as q1
    on a.fieldx= q1.fieldy

    Author Closing Comment

    by:Karen Schaefer
    Abandoned this question - do to task was not pursued - award points for time and effort.  Thanks.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now