DELETE from 2 tables

[rsch]

I have created a 'Super User' form that allows supervisors to add/remove/edit data. The form has a Tab subform associated with it.

On tab 1 (Company), there are 2 list boxes - one for companies and one for the companies' staff. What I would like to do is create a DELETE function that allows a user to select a company and delete the company and its underlying staff records.

Since the company and staff data are stored on two tables, I cannot figure out how to do this. Currently, I am considering running two SQL DELETE statements - one to (first) delete the associated staff records and the other to (then) delete the company record.

Is that the best way or is there a way to create one SQL DELETE statement that will do this?

Thank you, in advance.

 

[jimb0ne]

I think I would probably stick with the 2 seperate statements, although SQL isn't exactly my strong point so someone else might have a better plan. The way I would go about it is to delete the record in code, reference the second table using code and then delete the associated records from the second table using the referenced recordset.

 

[BNPMike]

If your business model is that when a company is deleted then all its staff are logically deleted as well, then you might as well capture this in the data model. Go into the relationships screen and declare the relationship between company and employee, and add referential integrity with cascade delete. Jet will then automatically remove all company employees when you delete a company.

 

[rsch]

Great catch, BNPMike. All of my tables have referential integrity, but I forgot to add the Cascade features.