If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How to delete a related record
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 |
#2
|
|||
|
|||
How to delete a related record
"Chris lee" wrote in
: 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 |
#3
|
|||
|
|||
How to delete a related record
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 "Chris lee" wrote in message ... 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 |
#4
|
|||
|
|||
How to delete a related record
On Thu, 29 Apr 2004 14:17:43 -0700, "Chris lee"
wrote: 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. John W. Vinson[MVP] Come for live chats every Tuesday and Thursday http://go.compuserve.com/msdevapps?loc=us&access=public |
Thread Tools | |
Display Modes | |
|
|