Solved

Loop code not evaluating

Posted on 2002-07-02
13
250 Views
Last Modified: 2013-12-18
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
Comment
Question by:pucktarget2
  • 6
  • 3
  • 2
  • +1
13 Comments
 

Author Comment

by:pucktarget2
ID: 7125246
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
 
LVL 10

Expert Comment

by:zvonko
ID: 7125267
Hello pucky,

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

0
 

Author Comment

by:pucktarget2
ID: 7125360
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
 
LVL 24

Accepted Solution

by:
HemanthaKumar earned 100 total points
ID: 7125422
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
 
LVL 10

Expert Comment

by:zvonko
ID: 7125487
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
 

Author Comment

by:pucktarget2
ID: 7127371
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 18

Expert Comment

by:marilyng
ID: 7131244
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
 
LVL 18

Expert Comment

by:marilyng
ID: 7131267
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
 

Author Comment

by:pucktarget2
ID: 7146940
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
 

Author Comment

by:pucktarget2
ID: 7149486
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
 
LVL 18

Expert Comment

by:marilyng
ID: 7149662
congrat's -- give points to heman if that's what put you in the right direction..

marilyng
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 7160323
Thanks for the points Puck
0
 

Author Comment

by:pucktarget2
ID: 7162384
Sure thing.  Thanks for the help.  I think I am beginning to get the hang of some things in here.

Puck
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

This is an old article, please see an updated version of this article, located here: http://www.experts-exchange.com/articles/23619/Notes-8-5x-Windows-7-Notes-info-and-tips.html
Lack of Storage capacity is a common problem that exists in every field of life. Here we are taking the case of Lotus Notes Emails, as we all know that we are totally depend on e-communication i.e. Emails. This article is fully dedicated to resolvin…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now