The simplest way to do this would be with cascading delete.
In the Relationships window (Tools menu), create a relation by dragging the
key field from tbl_JCN and dropping it onto the matching field in tbl_SOC.
Access opens the Create Relation dialog. Check the boxes for Referential
Integrity (always), and Cascading Deletes (only when you really want this.)
Now when you delete a record in tbl_JCN, the matching record(s) in tbl_SOC
automatically delete. No additional code is needed.
BTW, that wizard-generated code for the Delete button is pretty horrid. This
might work better:
If Me.Dirty Then Me.Undo
If Not Me.NewRecord Then RunCommand acCmdDeleteRecord
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Coll" <col_mcmanus@hotmail.comwrote in message
news:40183963-072f-4bf8-ac60-31634e680aa0@s8g2000prg.googlegroups.com...
Quote:
I'm working on a database that someone else created. I have a form -
frm_main that is based on a query that is based on two tables. The
join is such that the first table tbl_JCN has all records included in
the query, but the second table tbl_SOC only has matching records
displayed. On the form (frm_main), there is a combo box - you can
make a selection and the form filters to display that record. There is
also a delete record button. When you click the button the record is
deleted from the first table - tbl_JCN, but not from the second table
(tbl_SOC). Here is the code that is executed when the delete record
button is clicked...
>
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
>
How can I adjust the code so the record is also deleted from the 2nd
table (tbl_SOC). I tried a round about approach without luck, so I"m
hoping someone can give me an easier, more direct solution. Thanks.
