What's wrong with this code?

[White Bilky]

A97.

Simplified description: I have a query which returns one field containing
numbers.

I have a table where one field contains the same numbers as the query
(amongst others). The query and the table are not related in any way.

I want to loop through each record in the query, find matching records in
the table and delete them. The code counts the correct number of records
(5073) provided I comment out the delete command - if the delete command is
executed then the number of records deleted is about 3400. Here's an
extract from the code:

With rs1 'The Query
.MoveFirst
Do Until .EOF Or .BOF 'Do this until the end of the query
strCriteria = "[DRG_VER] = '" & rs1!DRG_VER & "'" 'Get the DRG_VER
from the query
rs2.MoveLast
rs2.MoveFirst
rs2.FindNext strCriteria 'Find a DRG_VER in the BUILD_ACT_TBL
that matches that from the query
If rs2.NoMatch = False Then 'There is a match
intCount = intCount + 1
Debug.Print intCount & " " & rs2![DRG_VER]
'rs2.Delete
End If 'There was no match
'rs2.FindNext strCriteria
.MoveNext 'Move to the next record in the query
Loop
End With

I've tried liberal sprinklings of MoveLasts and MoveFirsts and even tried
wrapping it all up into a transaction but to no avail. Can anyone advise?

Many thanks,
Keith.
www.keithwilby.com

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

It might be a better idea to use a DELETE query that would delete all
occurences in BUILD_ACT_TBL where it's DRG_VER = the query's DRG_VER.
Is that what you want? If so, perhaps this (untested):

DELETE B.*
FROM BUILD_ACT_TBL As B INNER JOIN Query As Q
ON B.DRG_VER = Q.DRG_VER

Then you'd save that code in a QueryDef and your VBA code would be like
this (DAO):

Dim db As DAO.Database
Dim qd As DAO.QueryDef
Set db = CurrentDB
Set qd = db.QueryDef("<query name>")
qd.Execute

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQe+TKYechKqOuFEgEQLzPwCg9JyJQqNsXvPtdJq3pmg5z8 S4VCMAoJQX
TLK80cK4+seyBfggI8K+47P2
=TWS9
-----END PGP SIGNATURE-----
White Bilky wrote:

A97.

Simplified description: I have a query which returns one field containing
numbers.

I have a table where one field contains the same numbers as the query
(amongst others). The query and the table are not related in any way.

I want to loop through each record in the query, find matching records in
the table and delete them. The code counts the correct number of records
(5073) provided I comment out the delete command - if the delete command is
executed then the number of records deleted is about 3400. Here's an
extract from the code:

With rs1 'The Query
.MoveFirst
Do Until .EOF Or .BOF 'Do this until the end of the query
strCriteria = "[DRG_VER] = '" & rs1!DRG_VER & "'" 'Get the DRG_VER
from the query
rs2.MoveLast
rs2.MoveFirst
rs2.FindNext strCriteria 'Find a DRG_VER in the BUILD_ACT_TBL
that matches that from the query
If rs2.NoMatch = False Then 'There is a match
intCount = intCount + 1
Debug.Print intCount & " " & rs2![DRG_VER]
'rs2.Delete
End If 'There was no match
'rs2.FindNext strCriteria
.MoveNext 'Move to the next record in the query
Loop
End With

I've tried liberal sprinklings of MoveLasts and MoveFirsts and even tried
wrapping it all up into a transaction but to no avail. Can anyone advise?

[White Bilky]

MGFoster <[e-mail]> wrote:

It might be a better idea to use a DELETE query that would delete all
occurences in BUILD_ACT_TBL where it's DRG_VER = the query's DRG_VER.
Is that what you want?

Many thanks for such a quick response. Unfortunately, owing to the
complexity of the table relationships within the app, I'm unable to join
the recordsets in a query since it would not be updatable. This is why I
was attempting to do it the way I have.

Regards,
Keith.

[Eric Schittlipz]

"White Bilky" <ke*********@AwayWithYerCrap.com> wrote in message
news:Xn************************@10.15.188.42...

A97.

Simplified description: I have a query which returns one field containing
numbers.

I have a table where one field contains the same numbers as the query
(amongst others). The query and the table are not related in any way.

I want to loop through each record in the query, find matching records in
the table and delete them. The code counts the correct number of records
(5073) provided I comment out the delete command - if the delete command
is
executed then the number of records deleted is about 3400. Here's an
extract from the code:

With rs1 'The Query
.MoveFirst
Do Until .EOF Or .BOF 'Do this until the end of the query
strCriteria = "[DRG_VER] = '" & rs1!DRG_VER & "'" 'Get the DRG_VER
from the query
rs2.MoveLast
rs2.MoveFirst
rs2.FindNext strCriteria 'Find a DRG_VER in the BUILD_ACT_TBL
that matches that from the query
If rs2.NoMatch = False Then 'There is a match
intCount = intCount + 1
Debug.Print intCount & " " & rs2![DRG_VER]
'rs2.Delete
End If 'There was no match
'rs2.FindNext strCriteria
.MoveNext 'Move to the next record in the query
Loop
End With

I've tried liberal sprinklings of MoveLasts and MoveFirsts and even tried
wrapping it all up into a transaction but to no avail. Can anyone advise?

Many thanks,
Keith.
www.keithwilby.com

Could you not use a subquery to get something like:

"DELETE FROM Table1 WHERE Table1.ID
IN (SELECT Table2.ID FROM Table2
WHERE Table2.SomeField="Whatever")