Using result of max query in Access VB

[alberon]

I'm trying to help a friend with an access 2000 database, and have run into a problem ....

I have the following code which runs an append query 10 times - each time the query runs it adds 1 row to a table, using data from the previous row and from another table.


LoopControl = 10
Counter = 0
While Counter < LoopControl
Counter = Counter + 1
stDocName = "Append Rolling balance 2"
DoCmd.OpenQuery stDocName, acNormal, acEdit
Wend


I'd like to control the number of times the query runs using a further query. I'd like to change the first line of the code above to:

LoopControl = select max(id) from transactions

Can it be done?

I'd be very grateful for any help on this one.

best wishes

Tim Ault
Oxford, UK

 

[ritchieroo]

This should do it

LoopControl = CurrentDB.OpenRecordset("select max(id) as MaxID from transactions")!MaxID

 

[alberon]

Thank you!