Prevent Orphan Records in Access Tables 1

[jjcpac]

I have a database that captures alumni information. Data entry is performed through a main form/sub form. The main form demographic information is in read only form and is linked to an outside system called BSR. The subform information is entered by the department and captures all contact the department has with the alumni (such as: date of initial contact, contact type, rep assigned, reason for contact, follow etc). What I want to be able to do is when an alumni is marked deceased in the outside BSR system the information is going to pass to the access database. I then want to be able to delete all records in the subform associated with that alumni that is deceased so there aren't any orphan records in the access table. Can this be done through and IIf statement? Or is there a different way? The primary key in both tables is a number generated by the outside BSR system. The relationship between the two tables is a one to many and the referential integrity is set to update and delete.

Thanks in advance for any help.

JJCPAC

 

[PHV]

Something like this ?
DELETE childTable.*
FROM childTable INNER JOIN parentTable ON childTable.FK = parentTable.PK
WHERE parentTable.Deceased = True;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jjcpac]

This is great!! Thanks so much for your help.

jjcpac