Function Employee_Add_New()
'Read qry_PEGA_Employees
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
'Set rs = New ADODB.Recordset
Set rs = db.OpenRecordset("select * from qry_PEGA_Employees")
'DoCmd.GoToRecord , "data", acNewRec
rs.OpenRecordset
Exist_Flag = 0
With rs
While (Not .EOF)
CompleteName1 = rs.Fields("[CompleteName]")
CompleteName1 = Replace(CompleteName1, "'", "")
Email1 = rs.Fields("[pxCreateOperator]")
Email1 = Replace(Email1, "'", "")
FirstName1 = rs.Fields("[FirstName]")
FirstName1 = Replace(FirstName1, "'", "")
LastName1 = rs.Fields("[LastName]")
LastName1 = Replace(LastName1, "'", "")
'Reading to verify the record exist in Employees Table***********
Dim db3 As DAO.Database
Dim rs3 As DAO.Recordset
Set db3 = CurrentDb
'Set rs = New ADODB.Recordset
Set rs3 = db3.OpenRecordset("select * from Employees")
'DoCmd.GoToRecord , "data", acNewRec
rs3.OpenRecordset
With rs3
While (Not .EOF)
CompleteName3 = rs3.Fields("[CompleteName]")
If CompleteName1 = CompleteName3 Then ' FLAG is Yes to Exist
Exist_Flag = 1
CompleteNameFLAG1 = CompleteName1
EmailFLAG1 = Email1
FirstNameFLAG1 = FirstName1
LastNameFLAG1 = LastName1
GoTo ContinueProcess
End If ' FLAG is Yes to Exist
.MoveNext
Wend
End With
Exist_Flag = 0 'FLAG is No to Exist
CompleteNameFLAG1 = CompleteName1
EmailFLAG1 = Email1
EmailFLAG1 = Replace(EmailFLAG1, "'", "")
FirstNameFLAG1 = FirstName1
LastNameFLAG1 = LastName1
StatusFLAG1 = "Active"
LastUpdateFLAG1 = Date
ContinueProcess:
rs3.Close
If Exist_Flag = 0 Then ' FLAG is NO to Exist
'INSERT INTO Employees
Set db2 = CurrentDb
'Set rs = New ADODB.Recordset
Set rs2 = db2.OpenRecordset("select * from Employees")
'DoCmd.GoToRecord , "data", acNewRec
rs2.OpenRecordset
StrSQL = "INSERT INTO Employees (CompleteName, FirstName, LastName, Email, Status, LastUpdate) " & _
" VALUES ('" & CompleteNameFLAG1 & "', '" & LastNameFLAG1 & "', '" & FirstNameFLAG1 & "', '" & EmailFLAG1 & "', '" & StatusFLAG1 & "', '" & LastUpdateFLAG1 & "' );"
DoCmd.SetWarnings False
DoCmd.RunSQL StrSQL
DoCmd.SetWarnings True
rs2.Close
End If ' FLAG is NO to Exist
.MoveNext
Wend
End With
rs.Close
MsgBox "Load Completed Successfully"
End Function