Full Version: Loop Issue
UtterAccess Discussion Forums > Microsoft® Access > Access Queries
georgegreiner
Oct 2 2009, 10:04 AM
Hello,

I have an issue regarding a nested loop that I had to add to one of my procedures. This loops exports data into a pipe delimited text file. The issue is that in one of the nested loops I have to pass a key from a prior recordset to pull the correct data. The relationship is a defendant (defendant table) and the addresses (address table). What occurs is that if there is no data record found it just shuts the loop down. The first code below is just the loop in question and the second is the entire loop. Any help would be great.

CODE
       rs2.MoveFirst

      

     ' 'If matching record(s)are found for Line B then write record to text file

     '    and then the coorsponding B1 records

            

               Do Until rs2.EOF

              

                   strCrit1 = rs2![interestID]     [color="red"]this is passed correctly if there is a record  [/color]

                  

                   Set rs5 = CurrentDb.OpenRecordset(sSQL5 & "interestID = " & Chr(34) & _

                    strCrit1 & Chr(34), dbOpenDynaset, [dbSeeChanges])

                   sTemp = ""

                  

                   For Each fld In rs2.Fields

                        

                        

                        sTemp = fld.Value & "|"

                        logfile.Write sTemp

                   Next

                        logfile.Writeline

                                    

                rs5.MoveFirst

                    

                    Do Until rs5.EOF

                        sTemp = ""

                        For Each fld In rs5.Fields

                            sTemp = fld.Value & "|"

                            logfile.Write sTemp

                        Next

                            logfile.Writeline

                            rs5.MoveNext

                       Loop

                      

               rs2.MoveNext

               Loop


CODE


    If rs1.EOF Then

         Err.Raise vbObjectError, csSource, "No Records Match Criteria"

    Else

         Set fso = CreateObject("Scripting.FileSystemObject")

         If fso.FileExists(strMailerExportPath & "\" & strMailerExportFile) Then

            Kill (strMailerExportPath & "\" & strMailerExportFile)

         End If

        



        

        

         'Create the text file

         fso.CreateTextFile (strMailerExportPath & "\" & strMailerExportFile)  ' check this

            ''Write the fieldNames

        Set logfile = fso.OpenTextFile(strMailerExportPath & "\" & strMailerExportFile, 8)

        sTemp = ""

      

      

'Set rst = db.OpenRecordset(strSql)



'Do While Not rst.EOF

'  strCrit = rst!CaseNumber



  ' Set rst2 = db.OpenRecordset(strSQL2 & " WHERE CaseNumber = " & strCrit)

'

   'Do While Not rst2.EOF

  

  

   rs1.MoveFirst

    'loop through each record in the first recordset

     Do Until rs1.EOF

     'set rs1 CaseNumber to strCrit for use throughout loop

       strCrit = rs1![CaseNumber]

      

       SQL1 = " UPDATE dbo_FileInfo "

       SQL2 = " SET dbo_FileInfo.SumOut = [dbo_FileInfo.SumNotes] & chr$(34) & [1] & chr$(34)" & _

            " nz([dbo_FileInfo.ParcelNum],"") & chr$(34) & [2] & chr$(34) &  _

            " nz([dbo_FileInfo.PriorBRT1],"") & chr$(34) & [3] & chr$(34) & _

            " nz([dbo_FileInfo.PriorBRT1From],"") & chr$(34) & chr&(45) & _

            " chr$(34) & nz([dbo_FileInfo.PriorBRT1To],"") & chr$(34) & [4] & _

            " chr$(34) & nz([dbo_FileInfo.PriorBRT1Open],"") & chr$(34) & [5] & _

            " chr$(34) & nz([dbo_FileInfo.AssessedBeg],"") & chr$(34) & chr$(32) & _

            " chr$(32) & chr$(34) & nz([dbo_FileInfo.AssessedDim],"") "

       SQL3 = " WHERE dbo_FileInfo.caseNumber = strCrit "

       sqlstring = SQL1 & SQL2 & SQL3

       DoCmd.RunSQL sqlstring

       'open second recordset referencing current CaseNumber

      

       Set rs2 = CurrentDb.OpenRecordset(sSQL2 & "CaseNumber = " & Chr(34) & _

                strCrit & Chr(34), dbOpenDynaset, [dbSeeChanges])

       Set rs3 = CurrentDb.OpenRecordset(sSQL3 & "CaseNumber = " & Chr(34) & _

                strCrit & Chr(34), dbOpenDynaset, [dbSeeChanges])

       Set rs4 = CurrentDb.OpenRecordset(sSQL4 & "CaseNumber = " & Chr(34) & _

                strCrit & Chr(34), dbOpenDynaset, [dbSeeChanges])

       Set rs5 = CurrentDb.OpenRecordset(sSQL5 & "CaseNumber = " & Chr(34) & _

                strCrit1 & Chr(34), dbOpenDynaset, [dbSeeChanges])

      ' If matching record(s)are found for Line A then write record to text file

      

      

               For Each fld In rs1.Fields

              

                    

                    

                    sTemp = fld.Value & "|"

                    logfile.Write sTemp

                Next

            logfile.Writeline

            

        

        

       rs2.MoveFirst

      

     ' 'If matching record(s)are found for Line B then write record to text file

        ' and then the coorsponding B1 records

               Do Until rs2.EOF

              

                   strCrit1 = Nz(rs2![interestID], 1)

                  

                   Set rs5 = CurrentDb.OpenRecordset(sSQL5 & "interestID = " & Chr(34) & _

                    strCrit1 & Chr(34), dbOpenDynaset, [dbSeeChanges])

                   sTemp = ""

                  

                   For Each fld In rs2.Fields

                        

                        

                        sTemp = fld.Value & "|"

                        logfile.Write sTemp

                   Next

                        logfile.Writeline

                                    

                rs5.MoveFirst

                    

                    Do Until rs5.EOF

                        sTemp = ""

                        For Each fld In rs5.Fields

                            sTemp = fld.Value & "|"

                            logfile.Write sTemp

                        Next

                            logfile.Writeline

                            rs5.MoveNext

                       Loop

                      

               rs2.MoveNext

               Loop

              

       rs3.MoveFirst

      

       ' If matching record(s)are found for Line C then write record(s) to text file

      

                Do Until rs3.EOF

                

                    sTemp = ""

                    For Each fld In rs3.Fields

                        sTemp = fld.Value & "|"

                        logfile.Write sTemp

                    Next

                     logfile.Writeline

                    

                      

                    

          rs3.MoveNext

       Loop

      

       'If matching record(s) are found for line D then write record(s) to text file

        rs4.MoveFirst

                    Do Until rs4.EOF

                      sTemp = ""

                      For Each fld In rs4.Fields

                          sTemp = fld.Value & "|"

                          logfile.Write sTemp

                    Next

                      logfile.Writeline

             rs4.MoveNext

          Loop

        

     rs1.MoveNext

   Loop



EDIT: Added break lines in the code to prevent forum blowout. Doug

Edited by: DougY on Fri Oct 2 11:41:21 EDT 2009.
jzwp11
Oct 2 2009, 11:04 AM
I usually do a check to make sure there are records in the recordset before trying to do the loop. If the recordset's BOF property and EOF property are true then there are no records in the recordset.

IF rs2.BOF and rs2.EOF Then

msgbox "No records:

Else

Do until....

End if
georgegreiner
Oct 2 2009, 12:03 PM
Sure I do that with rs1 but with the issue at hand I want the nested loop writing the addresses (rs5) to just ignore any defendants(rs2) without multiple addresses.

For instance I could have 5 defendants and defendant 1 and 3 have 3 addresses each but 2,4 and 5 do not so I need the loop to ignore the fact that those others do not have addresses and pass through it. I do not want anything to stop at that point but rather just pass through that table.


If I went ahead and did an if then statement how would i go about just doing nothing if there are no records that pertain to defendant 2,4 and 5.

If rs5.BOF and rs5.EOF Then

??-- how would i put something here that would just disregard the fact it has no corresponding record?

Else

sTemp = ""

For Each fld In rs5.Fields

sTemp = fld.Value & "|"

logfile.Write sTemp

Next

logfile.Writeline

rs5.MoveNext

Loop


End if
jzwp11
Oct 2 2009, 02:24 PM
You could use the Not keyword as shown below. If no records are in the recordset, the code should pass over the loop


If Not rsX.BOF And Not rsX.EOF Then
--run your loop here
End If
georgegreiner
Oct 2 2009, 02:27 PM
Thank you so much that works perfectly!
jzwp11
Oct 2 2009, 02:42 PM
You're welcome! Good luck with your project.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.