Chapter 13: Accessing Data with VBA Code
519
In a one-to-many relationship each child record (in tblSalesLineItems) must be related to
one record (and, only one record) in the parent table (tblSales). But, each sales record in tbl-
Sales may be related to more than one record in tblSalesLineItem.
When you enforce referential integrity on a one-to-many relationship, you’re telling Access that a
record in tblSales can’t be deleted if records with the same invoice number value exist in tbl-
SalesLineItems. If Access encounters a delete request that violates referential integrity, Access
displays an error message and the delete will be canceled, unless cascading deletes have been
enabled un the Edit Relationships dialog box (refer to Figure 13.20).
As you’ll recall from Chapter 3, you have the option of setting Cascade Update Related Fields and
Cascade Delete Related Fields in the Edit Relationships dialog box. By default, these options are
not enabled — and for good reason. If cascading deletes is turned on, when you use VBA code to
delete a sales record, all the related records in tblSalesLineItems and tblSalesPayments
are also deleted. Depending on the situation, this may or may not be a good thing. In the case of a
canceled sales order, there is probably no harm done by deleting the unsold sales line items.
However, when working on a canceled order where payment has been made, deleting the custom-
er’s payment history may be an issue. Surely, they’ll expect a refund of payments made on the
order, but Access just deleted the payment records.
In most cases, you’re far better off using an Active field (Yes/No data type) to indicate a
parent record’s status. The Active field is set to Yes when the order is placed, and only set
to No when the order has been canceled or completed. You might also consider adding a
CancellationDate field to tblSales, and set it to the date on which an order is canceled.
If CancellationDate is null, the order has not been canceled.
When you write ADO code to delete a record, you need to first check to see if there are any one-to-
many relationships between the table containing the record to delete and any other tables in the
database. If there are dependent tables, the records in the dependent tables need to be deleted
before Access allows you to delete the record in the parent table.
Fortunately, you can write a single procedure using ADO code to delete records in both the depen-
dent table(s) and the parent table. Figure 13.21 shows the code for the cmdDelete command
button in frmSales.
The cmdDelete_Click event procedure deletes records in tblSalesPayments, tblSales-
LineItems, and tblSales that have an invoice number matching the current invoice number.
The first statement in cmdDelete_Click (If Me.NewRecord Then) uses the NewRecord
property to see if the current sales record is new. If the record is new, Me.Undo rolls back changes
to the record and the procedure ends (Exit Sub). If the current record is not new, the procedure
displays a message box to confirm that the user really wants to delete the record. If the user clicks
the Yes button, the procedure deletes the records from the tables.