Read updated DAO.Recordset

[marlan]

I'll make it short:

Dim ID as Long
Dim rst as DAO.Recordset
Set rst = CurrentDB.OpenRecorset("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.Execuet("INSERT INTO Table1 (GroupID, Val) VALUES (" & ID & ", 1)")
    rst.Requery()' Chnge value of first rst?...
Loop
'next line of code...

what would I have to do so that the code exits the loop?
NOTE: Table1 has no primary key.

 

[JHB]

Explain what you're trying to do!
Remove the codeline rst.Requery(), and insert rst.MoveNext on it's place.

 

[marlan]

Explain what you're trying to do!

I would to increase the value of ValSum so that it is not the lowest, and the value of the first/top record in rst.Fields(0) changes do tto changes in the Data-Base, without having to move rst Pointer.

 

[JHB]

Ok, good luck.

 

[marlan]

Explain what you're trying to do!

I'll try again:
I would like to increase the value of ValSum so that it is not the lowest, and the value of rst.Fields(0) in the first/top record changes, do to these new inserts.
What I would need in this case is the value of the new rst.Fields(0) that refers to the new lowest ValSum. rst.movenext won't help me, next my not be the lowest. I need some sort of dynamic recordset (is that the term?), that gets modified when underlying table is modified too.
Is that to much to expect?

 

[moke123]

as an aside, since Val() is a vba function it probably is not a very good choice for a field name.

 

[marlan]

OK, the code works fine, it was the code modifying the DB (as the INSERT in the example above) that didn't work.