nested while loops

[Craig]

I have a loop that looks like the following...

Set tbl1 = db.OpenRecordset("SAP NAT_cleanse",
dbOpenDynaset)
Set tbl2 = db.OpenRecordset("SAP NAT", dbOpenDynaset)

Do While Not tbl1.EOF
Do While Not tbl2.EOF
If tbl2(0) = tbl1(0) Then
tbl2.Delete
tbl2.MoveNext
Else
tbl2.MoveNext
End If
Loop
tbl1.MoveNext
Loop

tbl1.Close
tbl2.Close


My problem is that for every record in the first table, I
want to check each record of the second table (and either
delete it or move on).

My question is: would the loop shown do what I want or
would it only check each record in table two for the FIRST
record in table 1? i.e. not reset from the EOF condition
after the first iteration through the table?

If anyone knows the answer to this, please e-mail me at
(e-mail address removed) or post a reply here. Thank you
for your help!!

Craig

 

[Wayne Morgan]

When you do the tbl1.MoveNext, check to see if all of the records are gone from tbl2, if
not then MoveFirst. Since you know that tbl2 is at EOF (that's why you exited the loop)
then check for BOF. If both are true, then there are no records. If you don't MoveFirst,
then the 2nd time though, you will already be at EOF and the loop won't run.

Now, for the tough question. What are you trying to accomplish? You may find a delete
query to be much quicker.

Set tbl1 = db.OpenRecordset("SAP NAT_cleanse",
dbOpenDynaset)
Set tbl2 = db.OpenRecordset("SAP NAT", dbOpenDynaset)

Do While Not tbl1.EOF
Do While Not tbl2.EOF
If tbl2(0) = tbl1(0) Then
tbl2.Delete
tbl2.MoveNext
Else
tbl2.MoveNext
End If
Loop
tbl1.MoveNext

If Not tbl2.BOF Then tbl2.MoveFirst