Help deleting a record from a joined query in visual basic

[Coll]

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.

[Allen Browne]

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" <co*********@hotmail.comwrote in message
news:40**********************************@s8g2000p rg.googlegroups.com...

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.