Access VBA nested for loop only works in first record

Question

I have an Access 2010 database, where one of the tables has a list of Beneficiaries, and Intermediaries. For QC, I need to calculate the ownership of all Beneficiaries, and omit the ownership of entities that act as links between the bottom Beneficiaries and top Intermediaries. So I have a table with 4 columns of intermediaries. I want the following steps to occur:

start with column "IMY_Up1", select the first intermediary ID number
look through the second "IMY_Up2" column to see if that ID exists there.
If it does - edit the "Linked IMY" column to "Yes", if it does not exist in that column - edit the "Linked IMY" column to "No".

Issue I'm facing: Current Code - Loops through accurately (Locals window shows 'i' and 'j' step through as expected). But only the first row has it's "Linked_IMy" value updated to "No".

Attempted Fix: put in 'Do While' loops. But this causes loop to continue to run, and no 'i' or 'j' value is reached in the Locals window.

My ultimate question: How can I change my code so it steps through and works as desired? Do I need to re-design the whole thing, and if so, how should I start?

Here is the VBA code:

Option Compare Database
Option Explicit

Public Sub modFieldlengthRegulate()

Dim i As Integer            'set "i" as integer for IMY_Up1 loop
Dim j As Integer            'set "j" as integer for IMY_Up2 loop
Dim db As DAO.Database          'imy database
Dim rs As DAO.Recordset         'QC_LoopTestTable as recordset


Set db = CurrentDb                              'imy database
Set rs = db.OpenRecordset("QC_LoopTestTable")   'QC_LoopTestTable as recordset

For i = 0 To rs.RecordCount - 1                 'set up for IMY_Up1 loop

rs.MoveFirst                                    'always start at first row

'Do While Not rs.EOF

    'Set rs.Fields("IMY_Up1").Value = refSelect             'wanted to see what value it was assigning, but causes errors, so comment out

    For j = 0 To rs.RecordCount - 1         'set up for IMY_Up2 loop
    rs.MoveFirst
        'Do While Not rs.EOF
        'Set rs.Fields("IMY_Up2").Value = compareSelect     'wanted to see what value it was assigning, but causes errors, so comment out
            If (rs.Fields("IMY_Up2").Value = rs.Fields("IMY_Up1").Value) Then
                rs.Edit
                rs.Fields("Linked_IMY") = "Yes"
                rs.Update

            Else:
                rs.Edit
                rs.Fields("Linked_IMY") = "No"
                rs.Update
            End If
        'Loop
    Next j

'Loop
Next i


rs.Close                    'close the recordset as part of the last step
Set rs = Nothing            'close the recordset as part of the last step
db.Close                    'close the database as part of the last step

End Sub

Sounds like you should be able to do it with just SQL. I can't see the image, so not sure — Darren Bartrup-Cook
– Darren Bartrup-Cook, Commented Dec 21, 2018 at 17:29
Also use rs.MoveNext to get it to move to the next record. — Darren Bartrup-Cook
– Darren Bartrup-Cook, Commented Dec 21, 2018 at 17:31

ComputerVersteher · Accepted Answer · 2018-12-21 18:20:03Z

2

Use the Power of SQL

A simple

SELECT QC_LoopTestTable.ID
    ,QC_LoopTestTable.IMY_Up1
    ,QC_LoopTestTable.IMY_Up2
    ,IIf(IsNull([QC_LoopTestTable_1].[id]), "No", "Yes") AS Linked_IMY
FROM QC_LoopTestTable
LEFT JOIN QC_LoopTestTable AS QC_LoopTestTable_1 
ON QC_LoopTestTable.IMY_Up2 = QC_LoopTestTable_1.IMY_Up1;

does the same much cleaner and faster (if IMY_Up1 and IMY_Up2 are indexed).

This fetchesQC_LoopTestTableand joins with itself (QC_LoopTestTable AS QC_LoopTestTable_1) where Up2 = Up1. If there is no match the result for the copied table (QC_LoopTestTable_1) isNULLwhat can be tested (Iif-Statement) and the corresponding value is (Yes/No) is used.

If you want to store the result ofLinked_IMY you can use a similarUPDATEquery, but this is not recommendet (usually don't store computed values), because if you edit a record, it needs to be updated again.

IfIMY_Up2matches multipleIMY_Up1those values will be double but can be filterd if necessary

Why your code fails

Your code is updating only first row, because you don't tell the recordset to move to the next record.
Then you would need two independent recordsets to loop one in the other (Inner loop with rs.MoveFirst would set outer loop rs to first row too (same variablers))
After you found a match, the inner loop needs to exit (Exit Do) or the next row will cause the revert as it will end in Elsepart writes toLinked_IMY.

AvoidIntegervariables as they fail above 2^15-1, useLonginstead.

UseDo Until rs.eof ... Loopto loop through recordsets.

Code could be like (just for demonstration, don't use):

Dim db As DAO.Database        
Dim rs1 As DAO.Recordset         
Dim rs2 As DAO.Recordset  

Set db = CurrentDb                              
Set rs1 = db.OpenRecordset("QC_LoopTestTable")   
Set rs2 = db.OpenRecordset("QC_LoopTestTable") 

Do Until rs1.eof
  Do Until rs2.eof
    If rs1.Fields("IMY_Up2").Value = rs2.Fields("IMY_Up1").Value Then 

    ...

    End If
    rs2.MoveNext
  Loop
rs1.MoveNext
rs2.MoveFirst
Loop

edited Dec 21, 2018 at 18:20

answered Dec 21, 2018 at 17:33

ComputerVersteher

2,6941 gold badge12 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

CircularReference Over a year ago

Thank you @ComputerVersteher, I tried both these solutions and they both worked. I did not know that you could make a copy of the table to query against (I already have a lot of tables in this DB and didn't want to split it - hence using VBA).

ComputerVersteher Over a year ago

@CircularReference Don't use the VBA part, it is only for demonstration. Table is not a real copy, it is just used twice

Collectives™ on Stack Overflow

Access VBA nested for loop only works in first record

1 Answer 1

Use the Power of SQL

Why your code fails

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Use the Power of SQL

Why your code fails

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related