How to delete a related record

[Chris lee]

My database has four tables, customer, products, orders
and order/product link. Is it possible to delete a
customer with a command button, I have tried a few
different ways but because the customer may have an order
the database will not allow the record to be deleted.

Many Thanx
Cris

 

[Tim Ferguson]

because the customer may have an order
the database will not allow the record to be deleted.

Then you'll have to delete all the orders related to the customer order,
and before that you will have to delete all the orderlines related to those
orders. This may or may not be compatible with your auditing system.

If this is okay, then the easy way is to set CASCADE DELETE on both
relationships. The better way is to create a little piece of code to reckon
up how much deleting is going to happen, and warn the user with dialog box
like this;

YOU ARE ABOUT TO DELETE 115 ORDERS TOTALLING $12309.55
<OK> <Cancel> <Help>


so there is a chance to back out of a potentially destructive operation.

Hope that helps


Tim F

 

[Adrian Jansen]

The safest way is to identify the orders belonging to that customer - eg
with a query, and then delete those, after asking for confirmation. Then
you can delete the customer. All this can be done in a couple of lines of
code. This gives you a chance to review what you are doing, and back out if
necessary.

You can also set "Allow cascade deletes" in the relationships window for the
relationship you have between Customers and Orders. Then deleteing the
Customer will automatically delete all the relevant Orders. But you get no
chance to back out.


--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control

 

[John Vinson]

My database has four tables, customer, products, orders
and order/product link. Is it possible to delete a
customer with a command button, I have tried a few
different ways but because the customer may have an order
the database will not allow the record to be deleted.

Many Thanx
Cris

Tim and Adrian have good advice: consider that you may not WANT to
delete a customer, if doing so deletes all that customer's orders! It
can make balancing the books at year's end more than a bit difficult.

You might want to consider instead adding a Yes/No field [Active] to
the Customer table. Set it to True by default, and to False to
"delete" a customer; your combo box listing customers, and your
customer form, might simply be based on a query selecting only
customers for whom Active is True.