Hi,
I have a tblInvoice and tblInvoiceDetail. frmInvoice has a subform called sfrmInvoice which has child links on InvoiceNumberDetail and InvoiceNumber. All is working well until I delete and invoice. The invoice deletes but the corresponding files in tblInvoiceDetail do not delete. I tried creating a query (see below) that would delete the rows in tblInvoiceDetail but I get an error that says to identify the table to delete from. What am I doing wrong or is there a better way to do this?
Thanks,
Leesha
DELETE tblInvoiceDetails.InvoiceNumberDetail, tblInvoiceDetails.ClientID, tblInvoice.InvoiceNumber
FROM tblInvoice RIGHT JOIN tblInvoiceDetails ON tblInvoice.InvoiceNumber = tblInvoiceDetails.InvoiceNumberDetail
WHERE (((tblInvoice.InvoiceNumber) Is Null));
Specify Table On Delete
-
- Administrator
- Posts: 78596
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Specify Table On Delete
On the Database Tools tab of the ribbon, click Relationships.
Double-click the join line between tblInvoice and tblInvoiceDetails.
Make sure that the check boxes 'Enforce Referential Integrity', 'Cascade Update Related Fields' and 'Cascade Delete Related Records' are ticked, then click OK.
Access will then automatically delete the related records from tblInvoiceDetails when you delete a record from tblInvoice.
Double-click the join line between tblInvoice and tblInvoiceDetails.
Make sure that the check boxes 'Enforce Referential Integrity', 'Cascade Update Related Fields' and 'Cascade Delete Related Records' are ticked, then click OK.
Access will then automatically delete the related records from tblInvoiceDetails when you delete a record from tblInvoice.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: Specify Table On Delete
Thanks so much!
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: Specify Table On Delete
Well of course it's giving me an error saying the primary table doesn't have an index. Both tables do. I'm not sure what I'm doing wrong. I tried attaching the tables with hardly any data but it says the file is too big, even compressed and zipped. Neither of the names being linked are the index name. Does that have anything to do with it?
Thanks!
Thanks!
-
- Administrator
- Posts: 78596
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Specify Table On Delete
You should join the tables on the primary key field of tblInvoice and the corresponding field in tblInvoiceDetail.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: Specify Table On Delete
I switched the Primary Key in tblInvoice from that autonumber to the Invoice number and all is well. Thanks!