iterate a query

[Guest]

I need to be able to iterate a query for as long as it takes until the update
query has zero records to update.

is there any way to continually to run a query so that it will stop when
there are no more records to update?

what my query is doing is basically "crawling" upstream to look for a node
of a certain unit type and updating a record that is connected to that node
with the node's id.

for instance a certain node ABC123 has a node with unittype SUMP connected
to it that has a ID 234. The SUMP with ID 234 has a node connected that has
unittype of SED with ID 345. So the query will update node ABC123 with the
ID for SED 345.

i typically have to run the query 4-5 times to account for all of the
"chains" of nodes that are eventually connected to a "SED", but I would like
to only run the query once. ruben

 

[tina]

well, i've never used the RecordsAffected property, but you might try this
out, as

Do
CurrentDb.Execute "QueryName"
Loop Until CurrentDb.RecordsAffected = 0

hth


"Jose Ruben Gonzalez-Baird"

 

[Guest]

tina,

thank you so much i am going to try this and i'll let you know if it works!!

 

[John Spencer]

I think it will fail since you are using currentdb which goes out of
scope as soon as the line is executed.

Try

Dim DbAny as DAO.Database

Set DbAny = CurrentDb()

Do
DbAny.Execute "QueryName", dbFailOnError
Loop Until DbAny.RecordsAffected = 0



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================