Delete orphan records

[GL]

Hello

I have two tables named Customers and Contracts related with a one-to-many
relation.
When I delete a contract record I want the relevant customer’s record to
remain if the certain customer has other contracts. However if the certain
customers has no other contract, I want his record automatically erased. In
other words I don’t want to have orphan records in Customers table.
Do you know a way to do it?

Thank you

GL

 

[Andrew Tapp]

Try something like;


Dim rst as DAO.Recordset
Dim strSQL As String

strSQL = "SELECT Count(*) AS Count"
strSQL = strSQL & " FROM Contracts"
strSQL = strSQL & " WHERE CustomerID=" & Trim(Str(lngCustomerID)) & ";"

Set rst = db.OpenRecordset(strSQL, fbOpenForwardOnly)

If rst!Count <= 1 'either 1 or 0 record's exists, then delete current
contract and customer
strSQL = "DELETE * FROM Contracts WHERE CustomerID=" &
Trim(Str(lngCustomerID)) & ";"
db.Execute strSQL, dbFailOnError

strSQL = "DELETE * FROM Customers WHERE CustomerID=" &
Trim(Str(lngCustomerID)) & ";"
db.Execute strSQL, dbFailOnErrorEndIf

rst.Close
set rst = Nothing


Assumes that CustomerID is a numeric.

Hope this helps.

 

[GL]

I assume that the code is in a sub driven by a “Delete command button†of
the form. In this case there is an issue if a record is deleted when the
user clicks the keyboard “Delete†button isn’t it?
If CustomerID is string what is differentiated in the code?

 

[Andrew Tapp]

The answer to the first point is yes. If the record is being deleted through
the "Delete" button then you could use the Form_Delete event to drive the
deletion of the other records. If CustomerID is a string then instead of
using;

CustomerID=" & Trim(Str(lngCustomerID)) & ";"

you would use something like

CustomerID='" & strCustomerID & "';"

note the single quotes around strCustomerID.

 

[GL]

Thanks a lot.
Do you think that is also safe to use a query on start up like

DELETE Exists (SELECT CustomerID FROM Contracts WHERE Contracts.CustomerID =
Customers.CustomerID) AS Expr1
FROM Customers
WHERE (((Exists (SELECT CustomerID FROM contracts WHERE Contracts.CustomerID
= Customers.CustomerID))=False));

or it is not neccessary?