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?
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 |
+---------+-------+
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments