Deleting records based on another table

  • Thread starter Thread starter Rockn
  • Start date Start date
R

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
 
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.
Click to expand...
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.
Click to expand...
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 <--
 
Back
Top