Help with nested Do Loop(s)

[slammedtgs]

Help with nested Do Loop(s) - Solved

Hello Everyone,

I am working on improving a database that I inherited which has several routine update queries based on various criteria. I am working on automating this process and I have a working solution (band-aid). My current solution uses pages of code in place of a loop to run the update queries based on static criteria. I am looking to make this process more dynamic.

I've created two tables that list my criteria for each loop: Service Line and Financial Class.

My current code works until I get to the last service line and the last financial class and then I get a debug error, Run time 3021 - No Current Record. The error is on the active MsgBox() line. I have been successful in running an excel version of this code with nested foreach loops.

I think I just need to have something to trigger an Exit Do; I have tried an if rstFinancialClass.eof = true then exit do. That idea didnt work

Any pointers would be appreciated. Thanks.

Sub Concept()


Dim rstServiceLine As dao.Recordset
Dim SServierLine As String

Dim rstFinancialClass As dao.Recordset
Dim FFinancialClass As String


  
Set rstServiceLine = CurrentDb.OpenRecordset("Service_Lines", dbOpenDynaset) ' Set variable to database table Service_Lines (listing of all service lines to update
rstServiceLine.MoveLast
rstServiceLine.MoveFirst ' Select first record to set variable

' begin Service line

Do While Not rstServiceLine.EOF
  '''MsgBox ("Service Line: " & rstServiceLine!Service_Line_Grouper)
  rstServiceLine.MoveNext
  
' Begin Financial Class Code here

    Set rstFinancialClass = CurrentDb.OpenRecordset("Financial_Classes", dbOpenDynaset)
    rstFinancialClass.MoveLast
    'rstFinancialClass.MoveFirst
    
        
        Do While Not rstFinancialClass.EOF
            MsgBox ("Service Line: " & rstServiceLine!Service_Line_Grouper & " Financial Class: " & rstFinancialClass!Financial_Class)
            rstFinancialClass.MoveNext
        Loop
    rstFinancialClass.Close
    Set rstFinancialClass = Nothing
    
    ' End Financial Class Code Here

Loop
  
rstServiceLine.Close ' Close service line recordset
Set rstServiceLine = Nothing

End Sub

 

[StarGrabber]

Hi slammedtgs, welcome to the forum!

Your mistake was a small one. You put the command rstServiceLine.MoveNext at the beginning of your loop. Put it at the end and you will see, it works.

Reconsider two more things:

1. A MoveLast and a following MoveFirst is only necessary if you want to get a correct RecordCount. In your code a MoveFirst only will do.

2. It's really necessary to open and close rstFinancialClass in each cycle? Why you don't open it like rstFinancialClass before the loop and close it afterwards?
​

 

[slammedtgs]

StarGrabber, Thank you.

I realized that was my error after reviewing more code samples online and was able to make the solution work.

When I started writing the script for this I initially set out to count the records in the table and then for a loop for I = 0 to 'max' rowcount. That is similar to what I did in excel but I could not implement it in Access.

Now that I have a working solution that requires the use of 1 update query (vs 25) I think I can optimize the code. I agree I do not need to open the recordset each time, I thought that was somewhat inefficient but I guess there is a bit of a learning curve to this. I am not sure my current method is the most efficient, but Its better than my first working solution of hard coding 25 separate queries and running them all with a macro.

Here is my latest version, I'm not sure there is a benefit to 3 sub routines, but it breaks the code into smaller sections.

I tried to most all of the open recordset lines to the top of sub step_1() but it was not looping porperly. I will have to play around with that some more, I probably had the close commands in the wrong place.

Thanks again for the response.

Dim rstServiceLine As DAO.Recordset
Dim SServiceLine As String
Dim rstFinancialClass As DAO.Recordset
Dim FFinancialClass As String
Dim rstCdfCode As DAO.Recordset
Dim CCdfCode As String
Dim x As Integer

Sub Step_1()
x = 0
CCdfCode = ""
SServiceLine = ""
FFinancialClass = ""

Set rstCdfCode = CurrentDb.OpenRecordset("Entity_Codes", dbOpenDynaset)
rstCdfCode.MoveFirst ' Select first record to set variable

' begin loop
    Do While Not rstCdfCode.EOF
        CCdfCode = CStr(rstCdfCode(0))
       'Call Second step
        Call Step_2
    rstCdfCode.MoveNext
    Loop
  
rstCdfCode.Close ' Close Entity recordset
Set rstCdfCode = Nothing

End Sub
Sub Step_2()
Set rstServiceLine = CurrentDb.OpenRecordset("Service_Lines", dbOpenDynaset) ' Set variable to database table Service_Lines (listing of all service lines to update
rstServiceLine.MoveFirst ' Select first record to set variable

' begin Service line
    Do While Not rstServiceLine.EOF
      SServiceLine = CStr(rstServiceLine(0))
      'Call Second step
      Call Step_3
      rstServiceLine.MoveNext
    Loop
  
rstServiceLine.Close ' Close service line recordset
Set rstServiceLine = Nothing

End Sub

Sub Step_3()

Set rstFinancialClass = CurrentDb.OpenRecordset("Financial_Classes", dbOpenDynaset) ' Set variable to database table Financial_Classes
rstFinancialClass.MoveFirst ' Select first record to set variable

    Do While Not rstFinancialClass.EOF
        FFinancialClass = CStr(rstFinancialClass(0))
        'Call step 4 - Update query
        x = x + 1
        Debug.Print CCdfCode, SServiceLine, FFinancialClass, x
        rstFinancialClass.MoveNext
    Loop

rstFinancialClass.Close ' Close service line recordset
Set rstFinancialClass = Nothing

End Sub

Hi slammedtgs, welcome to the forum!

Your mistake was a small one. You put the command rstServiceLine.MoveNext at the beginning of your loop. Put it at the end and you will see, it works.

Reconsider two more things:

1. A MoveLast and a following MoveFirst is only necessary if you want to get a correct RecordCount. In your code a MoveFirst only will do.

2. It's really necessary to open and close rstFinancialClass in each cycle? Why you don't open it like rstFinancialClass before the loop and close it afterwards?
​

 

[StarGrabber]

I had a look at your 'latest version' (post # 3). Aside from using a third table there is no difference to your code of post # 1, if, yes if you start the loop with the first record of rstFinancialClass, what not happens as you've set the line rstFinancialClass.MoveFirst as a comment.

To split code into smaller sections creating separate procedures - with another word: refactoring - is a technique widely used by (good) professional programmers. You would take some benefit in your code if you open the recordsets of 'Step_2' and 'Step_3' with one or more parameters passed to the procedures. But in your case the benefit is only little. Therefore you can readily put all recordset opening and closing commands in 'Step_1'.

Option Compare Database
Option Explicit

Dim rstServiceLine As DAO.Recordset
Dim SServiceLine As String
Dim rstFinancialClass As DAO.Recordset
Dim FFinancialClass As String
Dim rstCdfCode As DAO.Recordset
Dim CCdfCode As String
Dim x As Integer

Private Sub Step_1()
    
    x = 0
    CCdfCode = ""
    SServiceLine = ""
    FFinancialClass = ""
    
    Set rstCdfCode = CurrentDb.OpenRecordset("Entity_Codes", dbOpenDynaset)
    
    ' Select first record to set variable
    rstCdfCode.MoveFirst
    
    ' Set variable to database table Service_Lines (listing of all service lines to update)
    Set rstServiceLine = CurrentDb.OpenRecordset("Service_Lines", dbOpenDynaset)
    
    ' Set variable to database table Financial_Classes
    Set rstFinancialClass = CurrentDb.OpenRecordset("Financial_Classes", dbOpenDynaset)

    ' begin loop
    Do
        CCdfCode = CStr(rstCdfCode(0))
        
       ' Call Second step
        Call Step_2
        
        rstCdfCode.MoveNext
    Loop While Not rstCdfCode.EOF
      
    ' Close Entity recordset
    rstCdfCode.Close
    Set rstCdfCode = Nothing

    ' Close service line recordset
    rstServiceLine.Close
    Set rstServiceLine = Nothing

    ' Close financial class recordset
    rstFinancialClass.Close
    Set rstFinancialClass = Nothing

End Sub

Private Sub Step_2()

    ' Select first record to set variable
    rstServiceLine.MoveFirst
    
    ' begin Service line
    Do
      SServiceLine = CStr(rstServiceLine(0))
      
      'Call third step
      Call Step_3
      rstServiceLine.MoveNext
    Loop While Not rstServiceLine.EOF
      
End Sub

Private Sub Step_3()
    
    ' Select first record to set variable
    rstFinancialClass.MoveFirst

    Do
        FFinancialClass = CStr(rstFinancialClass(0))
        
        'Call step 4 - Update query
        x = x + 1
        Debug.Print CCdfCode, SServiceLine, FFinancialClass, x
        rstFinancialClass.MoveNext
    Loop While Not rstFinancialClass.EOF

End Sub

I've noticed you've put some comments in your code. Good thing. Much more programmers should do that!

But I suggest you use more appropriate/descriptive procedure names, like 'GetEntityCodes', ''GetServiceLineGroupers' or something similar. The same with your counter variable 'x'. 'intCounter' would be better.