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))"
'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"))
Set rs = Nothing
Set rs1 = Nothing
If IsLoaded("frmMessage") Then
DoCmd.Close acForm, "FrmMessage"
On Error GoTo 0
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdAddNewHire_Click of VBA Document Form_frmEmpMain"