DO LOOP

[Guest]

Hi,
I am trying to run "DO LOOPS" within a "DO LOOP" also called nested loops,
but I see that somehow only the last do loop within the tree works.
See the code below:

Do Until rst2.EOF
Do Until rst3.EOF
Do Until rst1.EOF
''execute code here for every record in rst1,
''but for every record in rst3. Every record
''in rst3 has one or more records in rst1
rst1.MoveNext
Loop
rst3.MoveNext
Loop
rst2.MoveNext
Loop

I see that only the code for the first record in rst3 is being executed, but
rst3 has more than 3 records. What should I do so that the code is being
executed for every record in rst1, belonging to a record in rst3?

You can see it as a parent-child realtionship, where rst3 is the parent and
rst1 is the child.

Thanks.

 

[Guest]

You have a recordset for each loop but nothing that stops them from looping
threw all there records.


Do Until rst2.EOF 'this will go threw all records and not stop untill the
end of file
'the same for the reset of these loops as well
Do Until rst3.EOF
Do Until rst1.EOF
'You need a test that will only run your code when rst1
record = rst3 record
If rst1!ID = rst3!ID then
'this code will only run if the rst1 record matches the
rst3 record. But you still are going to loop threw all the records in rst1
for each rst3 for each rst2 which will slow down your code. It will not be
noticeable unless you have a lot of records.
' example rst2 * rst3 * rst1 , 3 * 4 * 2 = 24 records you are looping threw

end if
rst1.MoveNext
Loop
rst3.MoveNext
Loop
rst2.MoveNext
Loop

 

[Guest]

Thanks,
I already use a filter to generate those recordsets and I think that for my
solution the recordsets would not conatin more than 10 records, but still it
can be 10 * 10 * 10 = 1000 records. But that will be o.k. Because it is
something like a batch processing to do calculations for a assurance
application I am bussy with.
I see what my problem was, rst1 depends on rst2, so if rst2 has another
value for a specific field, I have to reopen rst1 to get the new values.

 

[Troy]

If you're using subforms, then you don't need to re-open the rst if you use
RecordsetClone UNLESS you change the recordsource entirely.

So the subform's RecordsetClone will be updated to the current subset of
records when you move from record to record on the parent form....So instead
of building a new RST per subform each time you move to a new record on the
parent, make the rsts recordsetclones instead.

Set rst1 = subformControlName.Form.Recordsetclone

You should only need to do it once and not need to refresh or reload your
recordset multiple times.....

HTH

--
Troy

Troy Munford
Development Operations Manager
FMS, Inc.
www.fmsinc.com


Thanks,
I already use a filter to generate those recordsets and I think that for my
solution the recordsets would not conatin more than 10 records, but still it
can be 10 * 10 * 10 = 1000 records. But that will be o.k. Because it is
something like a batch processing to do calculations for a assurance
application I am bussy with.
I see what my problem was, rst1 depends on rst2, so if rst2 has another
value for a specific field, I have to reopen rst1 to get the new values.

 

[Douglas J. Steele]

As soon as you've read all of the records in rst1 for the first record in
rst3, rst1.EOF is true. The next time you come in with the 2nd record in
rst3, rst1.EOF is still true.

You need your code to open rst3 between Do Until rst2.EOF and Do Until
rst3.EOF, and you need your code to open rst1 between Do Until rst3.EOF and
Do Until rst1.EOF.