Set delete criteria from table values

[judith]

I have a table "Sites" which holds numbers in a single field "SiteId".

I want to delete records in another table "Clients" where the field "Owner"
has a value that is logged in the siteID field from the sites table.

I have been trying something like ..... but it doesnt work

' for each site
' delete out owner records

Dim rSite As Recordset

Let rSite = CurrentDb.OpenRecordset("sites")

While Not rSite.EOF

Delete
FROM chainChasing
WHERE (((chainChasing.Owner) = siteId))

rSite.MoveNext
Wend

rSite.Close


Any suggestions please

 

[Brian Wilson]

I have a table "Sites" which holds numbers in a single field "SiteId".

I want to delete records in another table "Clients" where the field
"Owner"
has a value that is logged in the siteID field from the sites table.

I have been trying something like ..... but it doesnt work

' for each site
' delete out owner records

Dim rSite As Recordset

Let rSite = CurrentDb.OpenRecordset("sites")

While Not rSite.EOF

Delete
FROM chainChasing
WHERE (((chainChasing.Owner) = siteId))

rSite.MoveNext
Wend

rSite.Close


Any suggestions please

Dim dbs As DAO.Database
Dim strSQL As String
Dim lngDeleted As Long

strSQL="DELETE FROM Clients WHERE Owner IN " & _
"(SELECT SiteId FROM Sites)"
Set dbs=Currentdb
dbs.Execute strSQL, dbFailOnError
lngDeleted=dbs.RecordsAffected
Set dbs=Nothing
Msgbox CStr(lngDeleted) & " record(s) deleted"

 

[judith]

Perfect, thanks

Dim dbs As DAO.Database
Dim strSQL As String
Dim lngDeleted As Long

strSQL="DELETE FROM Clients WHERE Owner IN " & _
"(SELECT SiteId FROM Sites)"
Set dbs=Currentdb
dbs.Execute strSQL, dbFailOnError
lngDeleted=dbs.RecordsAffected
Set dbs=Nothing
Msgbox CStr(lngDeleted) & " record(s) deleted"