Can I check Value in DAO Recordset field during update process?

Question

I would like to end a loop of updating data in a table, by seeing some affect on a RecordSet.
In this case: Say I have Table1 with fields: GroupID, a Long, and Val, a Double. no Primary key no limit to the values referred to a GroupID, no limit to number of records for a single GroupID.

Dim ID as Long
Dim rst as DAO.Recordset
Set rst = CurrentDB.OpenRecordset("SELECT GroupID, SUM(Val) As ValSum FROM Table1 Order By SUM (Val) ASC Group By GroupID")
rst.MoveFirst
ID = rst.Fields(0)
Do while ID = rst.Fields(0)
    CurrentDB.Execute("INSERT INTO Table1 (GroupID, Val) VALUES (" & ID & ", 1)")
    rst.Requery()' Change value of first rst?...
Loop
'next line of code...

rst is first pointing at the GroupID with the lowest SUM(Val), I then add records witch should add to the value SUM(Val), and eventually have some other GroupID in the first record of rst. That should cause while ID = rst.Fields(0) to value False, and exit the loop.

in practice, I get an endless loop.

Is there a way to update value of a DAO.Recordset while running?

Answer 1

Are you trying to win a competition for the most confusing code or what...?

Anyway, your code works for me after correcting some errors (your SQL had wrong syntax, Val is a reserved word).

Sub TestConfusingRS()

    Dim ID As Long
    Dim S As String
    Dim rst As DAO.Recordset
    Dim i As Long

    S = "SELECT GroupID, SUM(myVal) As ValSum FROM Tabelle1 Group By GroupID Order By SUM(myVal) ASC"
    Set rst = CurrentDb.OpenRecordset(S)
    ID = rst.Fields(0)

    Do While ID = rst.Fields(0)
        i = i + 1

        Debug.Print i, "Inserting GroupID " & ID
        CurrentDb.Execute "INSERT INTO Tabelle1 (GroupID, myVal) VALUES (" & ID & ", 1)"
        rst.Requery

        ' Prevent endless loop
        If i > 100 Then Exit Do

    Loop

End Sub

With

+---------+-------+
| GroupID | myVal |
+---------+-------+
|       1 |     6 |
|       2 |     2 |
|       2 |     1 |
|       3 |     5 |
+---------+-------+

I get

 1            Inserting GroupID 2
 2            Inserting GroupID 2

and it stops here:

+---------+-------+
| GroupID | myVal |
+---------+-------+
|       1 |     6 |
|       2 |     2 |
|       2 |     1 |
|       2 |     1 |
|       2 |     1 |
|       3 |     5 |
+---------+-------+