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

Loop code not evaluating

Hello all.  Built this code to loop through records from an ODBC dataset and write the results into a form.  It should pull in data from the first row and fill in the appropriate fields on the form.  Then go to next row and evaluate three fields (Obligor, Obligation and Item).  If all are equal to the same fields on the form, then it saves and closes the form and goes to the next row.  If obligor and obligation are equal but the item is different, it then appends the appropriate fields.  Then next row and evalutes again.  It will also see if obligor is equal and the obligation is different.  Hope that makes sense.  Thanks in advance.

Sub Initialize
      Dim s As New notessession    
      Dim db As NotesDatabase
      Dim doc As NotesDocument
      Dim Con As New ODBCConnection
      Dim qry As New ODBCQuery
      Dim result As New ODBCResultSet
      Dim item, colldesc1, colldesc2, Obligation, Obligor, CollType As NotesItem
      Dim ObligorCheck As String
      Dim ObligationCheck As String
      Dim ItemCheck As String
      
      Set db=s.currentdatabase
      Set qry.Connection=con
      Set result.query=qry
      con.ConnectTo("OARFAutomation")
      
      qry.SQL= "SELECT tblMaster.BANK, tblMaster.OBLIGOR, tblMaster.OBLIGATION, tblMaster.CUSTNAME, " _
      & "tblMaster.PROCTYPE, tblMaster.COLLTYPE, tblMaster.MATDATE, tblMaster.ITEM, " _
      & "tblMaster.FirstOfCOLLDESC1, tblMaster.FirstOfCOLLDESC2, tblMaster.OFFCRCODE, " _
      & "tblMaster.PREPAY, tblMaster.txt_EMAILADD, tblMaster.txtReadyToSendInd " _
      & "FROM tblMaster " _
      & "GROUP BY tblMaster.BANK, tblMaster.OBLIGOR, tblMaster.OBLIGATION, tblMaster.CUSTNAME, " _
      & "tblMaster.PROCTYPE, tblMaster.COLLTYPE, tblMaster.MATDATE, tblMaster.ITEM, " _
      & "tblMaster.FirstOfCOLLDESC1, tblMaster.FirstOfCOLLDESC2, tblMaster.OFFCRCODE, " _
      & "tblMaster.PREPAY, tblMaster.txt_EMAILADD, tblMaster.txtReadyToSendInd " _
      & "HAVING (((tblMaster.txtReadyToSendInd)=-1));"
      result.execute
      
      If result.IsResultSetAvailable Then
            
            result.FirstRow      
            
            Do
                  
                  Set doc=db.CreateDocument
                  
                  doc.txtCurrentDate=Date()
                  doc.txtBankNum=result.GetValue("BANK")
                  doc.txtObligor=result.GetValue("OBLIGOR")
                  doc.txtObligation=result.GetValue("OBLIGATION")
                  doc.txtCustomerName=result.GetValue("CUSTNAME")
                  doc.txtCollateralType=result.GetValue("COLLTYPE")
                  doc.txtNoteDate=result.GetValue("MATDATE")
                  doc.txtPrepayCode=result.GetValue("PREPAY")
                  doc.txtRM=result.GetValue("OFFCRCODE")
                  doc.txtItemNum=Str(result.GetValue("ITEM"))
                  doc.txtCollDesc1=result.GetValue("FirstofCOLLDESC1")
                  doc.txtCollDesc2=result.GetValue("FirstofCOLLDESC2")
                  doc.FormDescription="NEW-Unassigned"
                  
NextRow:
                  
                  result.nextrow
                  
                  doc.txtObligor=ObligorCheck
                  doc.txtObligation=ObligationCheck
                  doc.txtItemNum=ItemCheck
                  
                  If (ObligorCheck=result.GetValue("OBLIGOR")) And (ObligationCheck=result.GetValue("OBLIGATION")) And (ItemCheck=Str(result.GetValue("ITEM"))) Then
                        Goto NextRow            
                  End If
                  
                  If (Obligor=result.GetValue("OBLIGOR")) And (ObligationCheck=result.GetValue("OBLIGATION")) And (ItemCheck<>Str(result.GetValue("ITEM"))) Then
                        Set item=doc.GetFirstItem("txtItemNum")
                        Set colldesc1 = doc.getFirstItem("txtcolldesc1")
                        Set colldesc2 = doc.getFirstItem("txtcolldesc2")
                        Set CollType = doc.getFirstItem("COLLTYPE")
                        Call doc.AppendItemValue( "txtItem", Str(result.GetValue("ITEM")) )
                        Call doc.AppendItemValue( "txtCollDesc1", result.GetValue("FirstofCOLLDESC1"))
                        Call doc.AppendItemValue( "txtCollDesc2", result.GetValue("FirstofCOLLDESC2"))
                        Call doc.AppendItemValue( "txtCollalteralType" , result.GetValue("COLLTYPE"))
                        Goto NextRow
                        
                  Else
                        If (ObligorCheck=result.GetValue("OBLIGOR")) And (ObligationCheck<>result.GetValue("OBLIGATION")) Then
                              Set item=doc.GetFirstItem("txtItemNum")
                              Set colldesc1 = doc.getFirstItem("txtcolldesc1")
                              Set colldesc2 = doc.getFirstItem("txtcolldesc2")
                              Set CollType = doc.getFirstItem("COLLTYPE")
                              Set Obligation = doc.GetFirstItem("OBLIGATION")
                              Call doc.AppendItemValue( "txtItem", Str(result.GetValue("ITEM")) )
                              Call doc.AppendItemValue( "txtCollDesc1", result.GetValue("FirstofCOLLDESC1"))
                              Call doc.AppendItemValue( "txtCollDesc2", result.GetValue("FirstofCOLLDESC2"))
                              Call doc.AppendItemValue( "txtCollalteralType" , result.GetValue("COLLTYPE"))
                              Call doc.AppendItemValue( "txtObligation" , result.GetValue("OBLIGATION"))
                              Goto NextRow
                              
                        Else
                              If (ObligorCheck<>result.GetValue("OBLIGOR")) Then
                                    
                                    doc.Form = "Automated_OARF"
                                    doc.FormName ="OFFICER ACKNOWLEDGEMENT RELEASE FORM"
                                    Call doc.Save(True, True)
                                    
                              End If
                        End If
                  End If
            Loop Until result.IsEndOfData
      End If
End Sub
0
pucktarget2
Asked:
pucktarget2
  • 6
  • 3
  • 2
  • +1
1 Solution
 
pucktarget2Author Commented:
By the way...Currently it loops through recordset and creates a new document for each item on the list. Regardless of my criteria.

Thanks
0
 
zvonkoCommented:
Hello pucky,

why did you give a banana B to Heman for his last help on this?

0
 
pucktarget2Author Commented:
Because his code got the thing writing into the form and looping.  I reposted because it was almost 10 days since anyone had looked at it.  His code did help and I think he deserved the points.  This question is a little different in that I built the looping code it it myself but I can't seem to get it to evaluate.  In the previous question I had no idea where to even start.  

That or I have taken one too many pucks in the head (a definite possibility!!)

Puck
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
HemanthaKumarCommented:
Puck, It is not possible to close the document and still continue on the results from ODBC. Once you close the document you loose all of the objects that you built is lost with the object.

So you might have to think of doing it in backend.

~Hemanth
0
 
zvonkoCommented:
Hello Stuart,

please paste several records from your ODBC source and actions you like to have (schematically).

Do it in this way:
OBLIGOR;OBLIGATION;ITEM; action
obR1;ogN1;itM1; create doc
obR1;ogN1;itM2; append ITEM:>itM1,itM2
obR1;ogN2;itM3; save prev doc, create new doc
obR1;ogN2;itM4; append ITEM:>itM3,itM4
obR2;ogN3;itM5; save prev doc, create new doc
obR2;ogN3;itM5; !!! this is what I do not understan in your description...

Why save and close when all three are equal?




0
 
pucktarget2Author Commented:
0
 
marilyngCommented:
Hi pucktarget2

Just browsing through and can be wrong, but it looks like ObligorCheck, ObligationCheck and ItemCheck are all null.

               doc.txtObligor=ObligorCheck
               doc.txtObligation=ObligationCheck
               doc.txtItemNum=ItemCheck

when you instantiate these, you've just created the doc, so it seems that in each of your evaluations, you are comparing NULL to a value:

YOur Code:
  If (ObligorCheck=result.GetValue("OBLIGOR")) And (ObligationCheck<>result.GetValue("OBLIGATION"))

It reads:
  If (NOTHING =  

Also, Try:


Dim totalRows as Integer
result.LastRow
totalRows = result.currentRow

SET VALUES for


for j = 1 to totalRows
    result.currentRow = j






next


0
 
marilyngCommented:
Whoops, hit the return by accident.

Your Code:
 If (ObligorCheck=result.GetValue("OBLIGOR")) And (ObligationCheck<>result.GetValue("OBLIGATION"))

It reads:
 If (NOTHING = value for "OBLIGOR") and (Nothing <> value for OBLIGATION), so the result is false. Same for rest.

Also, Try:


Dim totalRows as Integer

result.LastRow
totalRows = result.currentRow

SET VALUES for:
              ObligorCheck=
              ObligationCheck=
              ItemCheck=

for j = 1 to totalRows
   result.currentRow = j
   
   then follow Zvonko's order suggestion..


next


Maybe I'm missing something, but can't see where you've set the initial values for the evaluations.

Marilyng
0
 
pucktarget2Author Commented:
Ok.  I think I made it easier.  This new code has cut down on the number of forms being built, but it does not seem to evaluate correctly.  I am now bringing in two recordsets with the same data, and comparing the info in the two recordsets instead of the info in the form.

Sub Initialize
      Dim s As New notessession    
      Dim db As NotesDatabase
      Dim doc As NotesDocument
      Dim Con As New ODBCConnection
      Dim Con2 As New ODBCConnection
      Dim qry As New ODBCQuery
      Dim qry2 As New ODBCQuery
      Dim result As New ODBCResultSet
      Dim result2 As New ODBCResultSet
      
      Set db=s.currentdatabase
      Set qry.Connection=con
      Set qry2.Connection=con2
      Set result.query=qry
      Set result2.query=qry2
      con.ConnectTo("OARFAutomation")
      con2.ConnectTo("OARFAutomation")
      
      qry.SQL= "SELECT tblMaster.BANK, tblMaster.OBLIGOR, tblMaster.OBLIGATION, tblMaster.CUSTNAME, " _
      & "tblMaster.PROCTYPE, tblMaster.COLLTYPE, tblMaster.MATDATE, tblMaster.ITEM, " _
      & "tblMaster.FirstOfCOLLDESC1, tblMaster.FirstOfCOLLDESC2, tblMaster.OFFCRCODE, " _
      & "tblMaster.PREPAY, tblMaster.txt_EMAILADD, tblMaster.txtReadyToSendInd " _
      & "FROM tblMaster " _
      & "GROUP BY tblMaster.BANK, tblMaster.OBLIGOR, tblMaster.OBLIGATION, tblMaster.CUSTNAME, " _
      & "tblMaster.PROCTYPE, tblMaster.COLLTYPE, tblMaster.MATDATE, tblMaster.ITEM, " _
      & "tblMaster.FirstOfCOLLDESC1, tblMaster.FirstOfCOLLDESC2, tblMaster.OFFCRCODE, " _
      & "tblMaster.PREPAY, tblMaster.txt_EMAILADD, tblMaster.txtReadyToSendInd " _
      & "HAVING (((tblMaster.txtReadyToSendInd)=-1));"
      result.execute
      
      qry2.SQL= "SELECT tblDlyOARFQueue.txtBankNum, tblDlyOARFQueue.txtObligorNum, tblDlyOARFQueue.txtObligationNum, " _
      & "tblDlyOARFQueue.txtCustomerName, tblDlyOARFQueue.txtProcessType, tblDlyOARFQueue.txtCollateralType, " _
      & "tblDlyOARFQueue.txtMaturityDate, tblDlyOARFQueue.txtItemNumber, tblDlyOARFQueue.txtCollateralDescription1, " _
      & "tblDlyOARFQueue.txtCollateralDescription2, tblDlyOARFQueue.txtServiceUnit, tblDlyOARFQueue.txtAssignmentUnit, " _
      & "tblDlyOARFQueue.txtOfficerCode, tblDlyOARFQueue.txtPrepay, tblDlyOARFQueue.txtEmail, tblDlyOARFQueue.txtSentDate, " _
      & "tblDlyOARFQueue.txtReadyToSendInd FROM tblDlyOARFQueue; " 
      result2.execute
      
      If result.IsResultSetAvailable And result2.IsResultSetAvailable Then
            
            result.FirstRow      
            result2.FirstRow
            
            Do
                  
                  Set doc=db.CreateDocument
                  
                  doc.txtCurrentDate=Date()
                  doc.txtBankNum=result.GetValue("BANK")
                  doc.txtObligor=result.GetValue("OBLIGOR")
                  doc.txtObligation=result.GetValue("OBLIGATION")
                  doc.txtCustomerName=result.GetValue("CUSTNAME")
                  doc.txtCollateralType=result.GetValue("COLLTYPE")
                  doc.txtNoteDate=result.GetValue("MATDATE")
                  doc.txtPrepayCode=result.GetValue("PREPAY")
                  doc.txtRM=result.GetValue("OFFCRCODE")
                  doc.txtItemNum=Str(result.GetValue("ITEM"))
                  doc.txtCollDesc1=result.GetValue("FirstofCOLLDESC1")
                  doc.txtCollDesc2=result.GetValue("FirstofCOLLDESC2")
                  doc.FormDescription="NEW-Unassigned"
                  
NextRow:
                  
                  result2.nextrow
                  
                  If (result.GetValue("OBLIGOR") <> result2.GetValue("txtObligorNum")) Then
                        Goto CloseDoc
                  Else
                        If (result.GetValue("ITEM")=result2.GetValue("txtItemNumber")) Then
                              result.NextRow
                              Goto NextRow
                        Else
                              Set item=doc.GetFirstItem("txtItemNum")
                              Set colldesc1 = doc.getFirstItem("txtcolldesc1")
                              Set colldesc2 = doc.getFirstItem("txtcolldesc2")
                              Set CollType = doc.getFirstItem("COLLTYPE")
                              Call doc.AppendItemValue( "txtItem", Str(result2.GetValue("ITEM")) )
                              Call doc.AppendItemValue( "txtCollDesc1", result2.GetValue("FirstofCOLLDESC1"))
                              Call doc.AppendItemValue( "txtCollDesc2", result2.GetValue("FirstofCOLLDESC2"))
                              Call doc.AppendItemValue( "txtCollalteralType" , result2.GetValue("COLLTYPE"))
                              result.NextRow
                              Goto NextRow
                              
CloseDoc:
                              result.NextRow
                              doc.Form = "Automated_OARF"
                              doc.FormName ="OFFICER ACKNOWLEDGEMENT RELEASE FORM"
                              Call doc.Save(True, True)
                              
                        End If
                  End If
            Loop Until result.IsEndofData
      End If
End Sub

I cut down on the parameters just to make it easier for now.  Once it works I may re-expand.  Here is an example of what I need.  Here are the first three records.  This should produce one document with two item descriptions.  It is producing one document but only with one item.

txtBankNum      txtObligorNum      txtObligationNum      txtCustomerName      txtProcessType      txtCollateralType      txtMaturityDate      txtItemNumber      txtCollateralDescription1      txtCollateralDescription2      txtServiceUnit      txtAssignmentUnit      txtOfficerCode      txtPrepay      txtEmail      txtSentDate      txtReadyToSendInd
01      0000000986      0011283215      PROVIDENCE STEE      5151      322      5/23/2002      18      BLNKT: ST MICH ONLY      #D074703 FLD 3/18/96      03058      220      WHV01      0      Stuart_A_Landes@keybank.com      6/12/2002      -1
01      0000000986      0011283215      PROVIDENCE STEE      5151      322      5/23/2002      18      BLNKT: ST MICH ONLY      #D074703 FLD 3/18/96      03058      220      WHV01      2      Stuart_A_Landes@keybank.com      6/12/2002      -1
01      0000000986      0011283215      PROVIDENCE STEE      5151      322      5/23/2002      34      1999 INT'L V#1HTSDAAN9WH603538      MICHIGAN 221J3160236 11/21/98      03058      220      WHV01      2      Stuart_A_Landes@keybank.com      6/12/2002      -1

Thanks

Puck
0
 
pucktarget2Author Commented:
Ok.  SUCCESS.  I am not really sure how to distribute points for this.  Hemanth kind of pointed me in the right direction with telling me to do this back end.  That in turn gave me the idea of pulling twin datasets from ODBC and pulling doing the compare there instead of on the form itself.  Here is my final code, which worked perfectly!!  Please help points-wise, I want to make sure the credit goes where it should!

Thanks!!!!!
Puck

Here is my looping code:

result.FirstRow      
     result2.FirstRow
     
     Do
         
          Set doc=db.CreateDocument
         
          doc.txtCurrentDate=Date()
          doc.txtBankNum=result.GetValue("txtBankNum")
          doc.txtObligor=result.GetValue("txtObligorNum")
          doc.txtObligation=result.GetValue("txtObligationNum")
          doc.txtCustomerName=result.GetValue("txtCustomerName")
          doc.txtCollateralType=result.GetValue("txtCollateralType")
          doc.txtNoteDate=result.GetValue("txtMaturityDate")
          doc.txtPrepayCode=result.GetValue("txtPrepay")
          doc.txtRM=result.GetValue("txtOfficerCode")
          doc.txtItemNum=Str(result.GetValue("txtItemNumber"))
          doc.txtCollDescA=result.GetValue("txtCollateralDescription1")
          doc.txtCollDescB=result.GetValue("txtCollateralDescription2")
          doc.FormDescription="NEW-Unassigned"
          Call doc.Save(False, False)
         
NextRow:
          result2.nextrow
         
          If (result.GetValue("txtObligorNum") <> result2.GetValue("txtObligorNum")) Then
               Goto CloseDoc
          Else
               If (result.GetValue("txtItemNumber")=result2.GetValue("txtItemNumber")) Then
                    result.NextRow
                    Goto NextRow
               Else
                    Set item=doc.GetFirstItem("txtItemNum")
                    Set colldescA = doc.getFirstItem("txtCollDescA")
                    Set colldescB= doc.getFirstItem("txtCollDescB")
                    Set CollType = doc.getFirstItem("txtCollateralType")
                    Call Item.AppendToTextList(Str(result2.GetValue("txtItemNumber")) )
                    Call colldescA.AppendToTextList(result2.GetValue("txtCollateralDescription1"))
                    Call collDescB.AppendToTextList(result2.GetValue("txtCollateralDescription2"))
                    Call CollType.AppendToTextList(result2.GetValue("txtCollateralType"))
                    Call doc.Save (False,False)
                    result.NextRow
                    Goto NextRow
                   
CloseDoc:
                    result.NextRow
                    doc.Form = "Automated_OARF"
                    doc.FormName ="OFFICER ACKNOWLEDGEMENT RELEASE FORM"
                    Call doc.Save(True, True)
                   
               End If
          End If              
     Loop Until result.IsEndofData
0
 
marilyngCommented:
congrat's -- give points to heman if that's what put you in the right direction..

marilyng
0
 
HemanthaKumarCommented:
Thanks for the points Puck
0
 
pucktarget2Author Commented:
Sure thing.  Thanks for the help.  I think I am beginning to get the hang of some things in here.

Puck
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now