Set delete criteria from table values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
judith said:
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
Click to expand...


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"
 
Perfect, thanks

Brian Wilson said:
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"
Click to expand...
 
Back
Top