[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 391
  • Last Modified:

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

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

Karen Schaefer
Karen Schaefer
1 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
Karen SchaeferAuthor Commented:
Abandoned this question - do to task was not pursued - award points for time and effort.  Thanks.

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now