Deleting Records on an Outer Join

[MattJM]

I have 4 tables, an Order table and a Invoice table each with respective 'details' tables (OrderDetails and InvoiceDetails) and linked by the order number and invoice number. The invoice details and order details are linked by an ID on each detail line. Also, the order (header) and invoice (header) are connected by order number and invoice number in a fifth registry table. There is a gap of time before the Invoice for an order comes through so not every order always has a connected invoice.

My problem is that I am using a form with a sub form for the detail lines. This worked very well until I changed the sub form on the Order form so that it used a query which joined the OrderDetail and InvoiceDetail tables, in order to include an invoice number if each order line has been connected. As not every order has a connected invoice I have had to do an outer join showing all the order records but only the records from the invoice details which are connected to the order detail recods.

It seems that having done this means that I can't delete records from the sub form anymore. Adding works fine, but deleting them just removes them until I refresh the form.

Any ideas anyone?

Matt

 

[lameid]

While not as efficient as a Join, you could use DLOOKUP to return the invoice number. A subform or an unbound combo box that is requeried and uses the orderdetail subform for criteria are also possibilites.