Loop inside a loop

[Guest]

I am trying to compare data in 2 recortsets and if there is no match then
delete the record from rst2. I've got myself confused! Can someone help?
Thankyou.
rst1.MoveFirst
Do Until rst1.EOF
Do Until rst2.EOF
If rst1!ID = rst2!ID Then
Exit Do
End If
rst2.MoveNext
rst1.Delete
Loop
rst1.MoveNext
Loop
rst2.Update

 

[Guest]

Hi Greg,

That code will NOT work - if you run that code you will delete your data in
rst1, which is not what you said you wanted.

If you want to delete items in Table2 that don't appear in Table1, you could
use a delete query like this:

delete * from Table2 where Table2.ID not in (select distinct ID from Table1)

As an aside, if you set up a relationship between Table1 and Table2 to
enforce referential integrity, you couldn't end up with a record in Table2
without a matching record in Table1.

Hope this helps.

Damian.

 

[Guest]

These are tables though, they are recordsets.

Hi Greg,

That code will NOT work - if you run that code you will delete your data in
rst1, which is not what you said you wanted.

If you want to delete items in Table2 that don't appear in Table1, you could
use a delete query like this:

delete * from Table2 where Table2.ID not in (select distinct ID from Table1)

As an aside, if you set up a relationship between Table1 and Table2 to
enforce referential integrity, you couldn't end up with a record in Table2
without a matching record in Table1.

Hope this helps.

Damian.

 

[Guest]

Yes, they are recordsets, but they must be getting their data from somewhere...

Alright, let's have a go at the two loop version - but be warned, this is
very inefficient and will take a long time if there are 1000's of records

Try this code:

dim blnFound as boolean

rst1.MoveFirst
Do while not rst1.EOF
Do while (not rst2.EOF) and (not blnFound)
If rst1!ID = rst2!ID Then
blnFound = true
End If
rst2.MoveNext
Loop

if not blnFound then
rst1.delete
endif

rst1.MoveNext
rst2.movefirst
blnFound = false
Loop

How does that go?

Damian.

 

[Guest]

That looks like it! Thanks heaps.

Yes, they are recordsets, but they must be getting their data from somewhere...

Alright, let's have a go at the two loop version - but be warned, this is
very inefficient and will take a long time if there are 1000's of records

Try this code:

dim blnFound as boolean

rst1.MoveFirst
Do while not rst1.EOF
Do while (not rst2.EOF) and (not blnFound)
If rst1!ID = rst2!ID Then
blnFound = true
End If
rst2.MoveNext
Loop

if not blnFound then
rst1.delete
endif

rst1.MoveNext
rst2.movefirst
blnFound = false
Loop

How does that go?

Damian.

 

[Stefan Hoffmann]

hi Greg,

I am trying to compare data in 2 recortsets and if there is no match then
delete the record from rst2. I've got myself confused! Can someone help?
Thankyou.
rst1.MoveFirst
Do Until rst1.EOF
Do Until rst2.EOF
If rst1!ID = rst2!ID Then
Exit Do
End If
rst2.MoveNext
rst1.Delete
Loop
rst1.MoveNext
Loop
rst2.Update

rst1.MoveFirst
Do While Not rst1.Eof
rst2.FindFirst "ID = " & rst1![ID]
If rst2.NoMatch Then
rst1.Delete
End If
rst1.MoveNext
Loop



mfG
--> stefan <--

 

[Guest]

Better still! Thankyou.

hi Greg,

I am trying to compare data in 2 recortsets and if there is no match then
delete the record from rst2. I've got myself confused! Can someone help?
Thankyou.
rst1.MoveFirst
Do Until rst1.EOF
Do Until rst2.EOF
If rst1!ID = rst2!ID Then
Exit Do
End If
rst2.MoveNext
rst1.Delete
Loop
rst1.MoveNext
Loop
rst2.Update

rst1.MoveFirst
Do While Not rst1.Eof
rst2.FindFirst "ID = " & rst1![ID]
If rst2.NoMatch Then
rst1.Delete
End If
rst1.MoveNext
Loop



mfG
--> stefan <--