Deleting records based on another table

[Rockn]

I have created a table based on two queries to get a list of the records I
want to delete in another table called purge.

I would like to create a form with a button coded to loop through the
records in the purge table and delete records in another table where the PK
in that table matches the field in the purge table.

Do I need to create two different recordsets, one for the ount of records in
the purge table and increment through it deleting records in the other table
with each loop?

Thanks

 

[Stefan Hoffmann]

hi,

I have created a table based on two queries to get a list of the records I
want to delete in another table called purge.

Why do you create a table? Isn't a query here sufficent?

I would like to create a form with a button coded to loop through the
records in the purge table and delete records in another table where the PK
in that table matches the field in the purge table.

You can do it with one statement:

Dim SQL As String

SQL = "DELETE FROM purge p " & _
"WHERE EXISTS(" & _
"SELECT * FROM query q WHERE q.primarykey = p.primarykey " & _
")"
CurrentDb.Execute SQL, dbFailOnError
MsgBox "Deleted " & CurrentDb.RecordsAffected



mfG
--> stefan <--