I have a strange issue with the loop below. The loop is creating records in our database for every other record is reads from the iSeries. In other words we are getting records 1, 3, 5, 7, 9 etc. written to the Notes Db but we should be getting 1,2,3,4,5,6,7,8,9 etc. I’m sure it is something simple that I am just not seeing. Any help would be appreciated
If Not con.ConnectTo(dataSource, userName, password) Then
LogAction("Could not connect to P1EMPLY from Training.nsf" )
result.Close(DB_CLOSE)
con.Disconnect
Exit Sub ' added end if jmo 3/26/02
Else
Set qry.Connection = con
qry.SQL = "SELECT * FROM QS36F.P1EMPLY WHERE P1CLK >= '15342' ORDER BY P1CLK"
Set result.Query = qry
result.CacheLimit = DB_ALL
result.FetchBatchSize = 1000
result.Execute
If result.IsResultSetAvailable Then
Do
result.NextRow
'try to locate matching employee number in Training Db
ee = Cstr(result.GetValue("P1CLK"))
Set doc = EmpTrainTblView.GetDocumentByKey(Cstr(result.GetValue("P1CLK")), True )
If (doc Is Nothing) Then
'Adding record for Hourly employee in training db
NumberNewPeople = NumberNewPeople + 1
Set newpersondoc = New NotesDocument(db)
newpersondoc.Form = "Person"
newpersondoc.EmployeeNum = Cstr(result.GetValue("P1CLK"))
newpersondoc.JobID = "0000-0"
newpersondoc.New = "New"
Call newpersondoc.ComputeWithForm( False, False )
Call newpersondoc.Save(True,False)
Set emaildoc = New NotesDocument( db )
emaildoc.Form = "Memo"
emaildoc.Subject = "New hourly employee #" & Cstr(result.GetValue("P1CLK")) & " " & newpersondoc.FirstName(0) & " " & newpersondoc.MiddleName(0) & " " & newpersondoc.LastName(0)
'Adding record for Hourly employee in training db
Set rtitem = New NotesRichTextItem( emaildoc , "Body" )
Call rtitem.AppendText("New Employee: #" & Cstr(result.GetValue("P1CLK")))
Call rtitem.AppendText( " " & newpersondoc.FirstName(0) & " " & newpersondoc.MiddleName(0) & " " & newpersondoc.LastName(0) )
Call rtitem.AddNewLine( 1 )
Call rtitem.AppendText("Click Here to Open ")
Call rtitem.AppendDocLink( newpersondoc, "Click Here to Open")
Call emaildoc.Send(True, MailTo)
Else ' Employee Numbers Matched
'Update doc in training db: Active Status
If result.GetValue("P1TRMC") = "D" And doc.NonActiveEmployee(0) <> "Inactive" Then
Call SetRecordsToBeActiveOrInactive(doc,"Inactive",NumOfDocsModified)
'Changed to Inactive"
DeActivatedPersonCount = DeActivatedPersonCount + 1
End If
If result.GetValue("P1TRMC") <> "D" And doc.NonActiveEmployee(0) <> "Active" Then
Call SetRecordsToBeActiveOrInactive(doc,"Active",NumOfDocsModified)
'Changed to Active
ReActivatedPersonCount = ReActivatedPersonCount + 1
Set emaildoc = New NotesDocument( db )
emaildoc.Form = "Memo"
emaildoc.Subject = "Reactivated hourly employee #" & Cstr(result.GetValue("P1CLK")) & " " & doc.FirstName(0) & " " & doc.MiddleName(0) & " " & doc.LastName(0)
'Reactivated record for Hourly employee in training db
Set rtitem = New NotesRichTextItem( emaildoc , "Body" )
Call rtitem.AppendText("Reactivated Employee: #" & Cstr(result.GetValue("P1CLK")))
Call rtitem.AppendText( " " & doc.FirstName(0) & " " & doc.MiddleName(0) & " " & doc.LastName(0) )
Call rtitem.AddNewLine( 1 )
Call rtitem.AppendText("Click Here to Open ")
Call rtitem.AppendDocLink( doc, "Click Here to Open")
Call emaildoc.Send(True, MailTo)
End If
End If
Loop Until result.IsEndOfData
result.Close(DB_CLOSE)
con.Disconnect
End If
End If