Specify Table On Delete

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Specify Table On Delete

Post by Leesha »

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));

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Specify Table On Delete

Post by HansV »

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.
Best wishes,
Hans

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: Specify Table On Delete

Post by Leesha »

Thanks so much!

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: Specify Table On Delete

Post by Leesha »

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!

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Specify Table On Delete

Post by HansV »

You should join the tables on the primary key field of tblInvoice and the corresponding field in tblInvoiceDetail.
Best wishes,
Hans

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: Specify Table On Delete

Post by Leesha »

I switched the Primary Key in tblInvoice from that autonumber to the Invoice number and all is well. Thanks!