0

We have an ORDERS table and a CHARGES table. For each order there may be a charge. If the order is deleted we want the charge to automatically be deleted.

If there would be a single relationship between the two tables, we could define the relationship to cascade delete the charge when the order is deleted.

Unfortunately, the tables are related using two fields.

How would one do this using a data macro?

Inspired by Delete Records from Table Before Delete Trigger I tried the following:

enter image description here

where DeleteCharge is a VBA function

It works, but:

  • I would prefer not to have to use VBA
  • If the user cancels the first deletion, the triggered deletion still gets carried out.
Community
  • 1
  • 1
E Mett
  • 2,272
  • 3
  • 18
  • 37
  • Access will allow you to create a relationship based on 2 fields in each table and enforce referential integrity with `CASCADE DELETE`. – HansUp Apr 30 '15 at 15:31
  • @HansUp I tried that, but Access wants a unique index on the primary table. In my case the fields on both sides cannot be unique. – E Mett Apr 30 '15 at 15:36
  • Not sure I understand. Does that mean that 2 or more ORDERS rows may be associated with the same set of CHARGES rows? – HansUp Apr 30 '15 at 15:43
  • @HansUp In principle yes, but in practice not. In practice there is only one order per individual per distribution. Same for charge. However should there be multiple charges for this one order per individual per distribution, we would want all those charges deleted in the event the order is deleted. – E Mett Apr 30 '15 at 15:53

1 Answers1

0

Without any table structures provided, it is hard to give you a very good answer. It sounds like your tables are not normalized enough... maybe has an unnecessary field in the "charges" table... Are you using the "charges" table as an order details table? If so, Something like this should work just fine:

Order_OrderDetails_Products

In this picture, the unit price is input into the orderdetails table using the form to generate the order, not by relationship (if the unit price increase next month, we don't want to be editing the price in old orders. In this sort of setup, the cascade delete should be enough to take care of the problem. Could you post your table design? without it, it would be hard for anyone to give you a good answer.

Rahul Sharma
  • 2,867
  • 2
  • 27
  • 40
Malone Dunlavy
  • 1