Delete records from 2 different tables
I have 2 tables item_cat_subcat which contains ITEMNMBR, CAT_ID, and SUBCAT_ID which will contain 1 record with that ITEMNMBR and item_attrib_attribvl which contains ITEMNMBR, ATTRIB_ID, and ATTRBVLU_ID which could contain multiple ITEMNMBR values. I also have a form frmAssignedItems based on a query (See Image) that displays the records in those tables. and a delete button next to each record on the form, I want to be able to delete the records from both tables based on the ITEMNMBR on the form with VBA.
ASKER
Pat, the only tables that i want the records deleted are item_cat_subcat and item_attrib_attribvl
ASKER
<Otherwise, you're on your own and will need to run separate delete queries on all tables that you want to delete from.> I don't necessarily have a problem with doing that as it is only the 2 tables, so how would I do that
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Pat, thanks I was able to do what I wanted by creating queries as you suggested.
You're welcome.
2. Create Referential Integrity using the relationships dialog.
3. Specify cascade delete on relationships where you want deletes to be propagated.
Based on the joins shown in the diagram, item_cat_subcat is the parent table to TWO child tables. If you want the delete to work on the parent record, then Cascade Delete must be defined on both of these relationships.
Otherwise, you're on your own and will need to run separate delete queries on all tables that you want to delete from.