DAO Do-While Nested Loop ...

[dkinley]

Greets all ... I have something weird going on. I've got a nested do-while loop (code posted below) - rs2 is the outside loop and rs1 is the inside.

The outcome is that the code should loop once through rs2 then loop through every record in rs1 checking a condition. If true, assign a field value from rs1 to rs2, if not true assign "unknown" to rs2. Once a complete pass is done in rs1, it iterates to the next rs2 record and goes back through the sequence. It's not rocket science, but here is the issue ...

It only makes one group assignment in rs2 (meanining it finds a true for about 10 records). It places "unknown" in the rest and I know that it should through manual verification.

The label caption updates and repaints are to check the looping structure. The looping numbers come out correct. I've set breaks in the code and the iDataID assignments are correct and the records in rs1 are iterating for the conditions, but for some reason I can't figure out why it is not finding a true in the other remaining records.

Ideas?

-dK

    Dim dbs As DAO.Database
    Dim rs1 As DAO.Recordset
    Dim rs2 As DAO.Recordset
 
    Dim iDataID As Long
    Dim iCounter As Long
    Dim jCounter As Long
 
    iCounter = 0
    jCounter = 0
 
    Set dbs = CurrentDb
    Set rs1 = dbs.OpenRecordset("Table1", dbOpenTable)
    Set rs2 = dbs.OpenRecordset("Table2", dbOpenTable)
    rs2.MoveFirst
    Do While Not rs2.EOF
        iDataID = rs2.Fields(4).Value
 
        rs1.MoveFirst
        Do While Not rs1.EOF
            rs2.Edit
            If iDataID >= rs1.Fields(2).Value And iDataID <= rs1.Fields(3).Value Then
                rs2.Fields(1).Value = rs1.Fields(1).Value
            Else
                rs2.Fields(1).Value = "Unknown"
            End If
            rs2.Update
 
            jCounter = jCounter + 1
            Me.lblLabel1.Caption = "Total of " & jCounter & " inner loops executed."
            Me.Repaint
            rs1.MoveNext
        Loop
 
        iCounter = iCounter + 1
        Me.lblLabel2.Caption = "Total of " & iCounter & " outer loops executed."
        Me.Repaint
 
        rs2.MoveNext
    Loop
 
    MsgBox "Job complete.
 
    rs1.Close
    rs2.Close
 
    Set rs1 = Nothing
    Set rs2 = Nothing
    Set dbs = Nothing

 

[boblarson]

After the first LOOP put

rs1.MoveFirst

to get back to the beginning again.

 

[dkinley]

No dice.

To dumb it down just in case it was some sort of logic, I modified the condition and added a counter....

            If iDataID = rs1.Fields(2).Value Then
                cCounter = cCounter + 1
                rs2.Fields(1).Value = rs1.Fields(1).Value
            Else
                rs2.Fields(1).Value = "Unknown"
            End If

The result posted was there were trues; however, it only updated rs1 once. Arg! I will clean up the db (sensitive) and post the db, but it is in '07.

-dK

 

[boblarson]

The result posted was there were trues; however, it only updated rs1 once.

From what I see, it isn't supposed to update rs1, but update rs2. How is rs1 supposed to be updated?

 

[dkinley]

From what I see, it isn't supposed to update rs1, but update rs2. How is rs1 supposed to be updated?

Errrrrrr ... ... typo on my part. It should have been rs2 to be updated. I was swapping out the variables because the names of everything could be considered sensitive. I shall post the db in a moment.

What I did notice though, and true to what you posted is that only the last records data is rs1 is being assigned in rs2. Perhaps I still have the .MoveFirst in the wrong location?

-dK

 

[boblarson]

What I did notice though, and true to what you posted is that only the last records data is rs1 is being assigned in rs2. Perhaps I still have the .MoveFirst in the wrong location?

I don't know where you actually put it when I said to place it, but here:

Dim dbs As DAO.Database
    Dim rs1 As DAO.Recordset
    Dim rs2 As DAO.Recordset
 
    Dim iDataID As Long
    Dim iCounter As Long
    Dim jCounter As Long
 
    iCounter = 0
    jCounter = 0
 
    Set dbs = CurrentDb
    Set rs1 = dbs.OpenRecordset("Table1", dbOpenTable)
    Set rs2 = dbs.OpenRecordset("Table2", dbOpenTable)
    rs2.MoveFirst
    Do While Not rs2.EOF
        iDataID = rs2.Fields(4).Value
 
        rs1.MoveFirst
        Do While Not rs1.EOF
            rs2.Edit
            If iDataID >= rs1.Fields(2).Value And iDataID <= rs1.Fields(3).Value Then
                rs2.Fields(1).Value = rs1.Fields(1).Value
            Else
                rs2.Fields(1).Value = "Unknown"
            End If
            rs2.Update
 
            jCounter = jCounter + 1
            Me.lblLabel1.Caption = "Total of " & jCounter & " inner loops executed."
            Me.Repaint
            rs1.MoveNext
        Loop
            [COLOR="Red"]rs1.MoveFirst[/COLOR]
        iCounter = iCounter + 1
        Me.lblLabel2.Caption = "Total of " & iCounter & " outer loops executed."
        Me.Repaint
 
        rs2.MoveNext
    Loop
 
    MsgBox "Job complete.
 
    rs1.Close
    rs2.Close
 
    Set rs1 = Nothing
    Set rs2 = Nothing
    Set dbs = Nothing

 

[gemma-the-husky]

this bit

If iDataID >= rs1.Fields(2).Value And iDataID <= rs1.Fields(3).Value Then
rs2.Fields(1).Value = rs1.Fields(1).Value
Else
rs2.Fields(1).Value = "Unknown"
End If

i think youve pointed this out, but it tests and sets every value from the inner loop - therefore the current record in the outer loop will only get set to the last record in the inner loop - you probably need to break the inner loop when you find a value that suits you, i think

 

[dkinley]

I don't know where you actually put it when I said to place it, but here:

Yep, that's where I put it.

i think youve pointed this out, but it tests and sets every value from the inner loop - therefore the current record in the outer loop will only get set to the last record in the inner loop - you probably need to break the inner loop when you find a value that suits you, i think

Oh. Hmmm, good point. In my experience, I was under the impression that it would 'update as it went' and would not need to break it per say, to get it to update. Of course, I've never nested them in this fashion - I was looking for an easy way out by nesting these types of loops instead of doing it "manually" (using i's and j's and .MoveNext's).

-dK

 

[dkinley]

I should say that it does 'update as it goes' because it is placing the "Unknowns" in there.

Here is the db in '03 format.

Thanks again for looking!

-dK

 

[dkinley]

... therefore the current record in the outer loop will only get set to the last record in the inner loop - you probably need to break the inner loop when you find a value that suits you, i think

lol .. I am still trying to get the logic on this - I am just on the cusp. Perhaps some background is in order.

The reason I set the loops up the way I did is because the inner loop has fewer records than the outer loop - hence, fewer iterations. The inner loop carries a field that needs to be placed in the outter loop record when a match is found (in a range).

I could have went with a nested query - but there will be further data analysis in the same manner soon. I thought if I could create a mechanism so that other people (non-technical) at the client site could handle this if there were fewer steps involved (massage the spreadsheets thus so, import and run) and I could 'farm it out'.

Anyhow, back to the point - if I am following that logic correctly, I would need to reverse the table assignments and update the record outside the inner loop?

-dK

 

[RainLover]

What is happening is the data gets written as you want but on the next pass your previous update gets over written because it meets the else part of your statement.

You will only get an update on the last loop.

After your first if statement add
If rs2.Fields(1).Value <> rs1.Fields(1).Value Then

You will need an extra End If at the end.

 

[RainLover]

As an extra bit of info, if you run the process a second time it will change the values back to unknown.

Not sure how to solve this but maybe a goto statement might come in handy.

 

[RainLover]

I had to test my theory.

Try this.

    rs2.MoveFirst
    rs1.MoveFirst
    Do While Not rs1.EOF
        iDataID = rs1.Fields(2).Value
           Do While Not rs2.EOF
            If iDataID = rs2.Fields(2).Value Then
                rs2.Edit
                cCounter = cCounter + 1
                rs2.Fields(1).Value = rs1.Fields(1).Value
                rs2.Update
                   If rs2.Fields(1).Value = rs1.Fields(1).Value Then
                        GoTo exitLoop
                         Else
                            rs2.Edit
                            rs2.Fields(1).Value = "Unkown"
                            rs2.Update
                End If
            End If
exitLoop:
            jCounter = jCounter + 1
            Me.lblLabel2.Caption = "Total of " & jCounter & " inner loops executed."
            Me.Repaint
            rs2.MoveNext
        Loop
        rs2.MoveFirst
        iCounter = iCounter + 1
        Me.lblLabel1.Caption = "Total of " & iCounter & " outer loops executed."
        Me.lblLabel3.Caption = "Total of " & cCounter & " matches found."
        Me.Repaint
        
        rs1.MoveNext
       ' Debug.Print " I " & iCounter & " Matches " & cCounter
    Loop

 

[dkinley]

What is happening is the data gets written as you want but on the next pass your previous update gets over written because it meets the else part of your statement.

Oh my freaking gawd. Here comes that big Der! I have been feeling all night. I drove 4 hours last night and had this toss around my head and still couldn't get it.

Thanks for the clarification of what it seems that everyone has been telling me and I've been too thick-headed to get.

You see - here is where I messed up. In the original code, after it made the data write for a true statement, I had a .MoveLast to 'exit' the inner loop. But this would cause it to lock up for some reason. Driving, I was thinking it was from the update conflict. I will try to move the edit/update and reinserting the .MoveLast.

Failing that, will fudge a bit now that all of you have hung a big lightbulb over my head. Many thanks and will post final resolution (supposing there is a simple one).

-dK

 

[dkinley]

Resolved ....

The .Update and .MoveLast was the conflict with the hangup. Who'da thunk it?

My original code before I had posted was ...

rs2.Edit
If iDataID >= rs1.Fields(2).Value And iDataID <= rs1.Fields(3).Value Then
cCounter = cCounter + 1
rs2.Fields(1).Value = rs1.Fields(1).Value
[COLOR=red]rs2.MoveLast[/COLOR]
Else
rs2.Fields(1).Value = "Unknown"
End If
[COLOR=red]rs2.Update[/COLOR]

I had modified this to what was posted because it would cause it to hang up. I put the emphasis on those two lines because I didn't suspect it until I was on my drive. With verification, I have just learned that you cannot .MoveLast and must .Update first.

By moving .Update and reinserting .MoveLast, The resolved code is ...

rs2.Edit
If iDataID >= rs1.Fields(2).Value And iDataID <= rs1.Fields(3).Value Then
     cCounter = cCounter + 1
     rs2.Fields(1).Value = rs1.Fields(1).Value
[COLOR=red]    rs2.Update[/COLOR]
[COLOR=red]    rs1.MoveLast[/COLOR]
Else
     rs2.Fields(1).Value = "Unknown"
[COLOR=red]    rs2.Update[/COLOR]
End If

Which acts as a goto and satisfies the exiting of the inner loop so the record isn't overwritten with "Unknown".

I apologize for being blind, but thanks to all of you - I was able to see forest. Thanks many oodles.

-dK

 

[RainLover]

Glad to see you found a working solution.

 

[gemma-the-husky]

although stylish/elegant programming technique says dont use "goto"s, getting the same effect with a do/loop while/wend is often a bind, as you have to set control flags carefully etc, to exit the iteration - I now use goto's a lot just for speed of development.

The only thing to watch out for is spaghetti programming that can become tricky to maintain later

 

[RainLover]

Gemma

A lot of people say not to use GoTo then as their first line of code they use something like.

"On Error GoTo Err_ErrorTag"

But yes, as you said to watch out for "Spaghetti Code".