how to prevent delete the record in form while exist linked record in subform (1 Viewer)

eng.trk1

New member
Local time
Today, 11:48
Joined
Dec 31, 2019
Messages
3
Hi ,
I apologize for my English language , (Arabic is my mother tongue)

Now.. I am new in programming with MS access..
I designed from added records in many tables with multiple sub-forms, all of them is linked with master ID in main table..

The problem is :
When the user deletes the master record, the other related records remain stuck in their tables and thus the presence of incomplete data ..

The question is :
How upgrade delete button to check if there are records in the relevant subforms before allowing deletion of the master record.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 14:48
Joined
May 21, 2018
Messages
8,463
I would make a relationship and enforce referential integrity. Set cascade deletes to true. No code is required, it will automatically delete.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 14:48
Joined
May 21, 2018
Messages
8,463
From the Microsoft Website
https://support.office.com/en-us/ar...tionship-dfa453a7-0b6d-4c34-a128-fdebc7e686af

Turn referential integrity on or off
On the Database Tools tab, in the Relationships group, click Relationships.

On the Design tab, in the Relationships group, click All Relationships.

All tables with relationships are displayed, showing relationship lines. Note that hidden tables (tables for which the Hidden check box in the table's Properties dialog box is selected) and their relationships will not be shown unless Show Hidden Objects is selected in the Navigation Options dialog box.

Click the relationship line for the relationship that you want to change. The relationship line appears thicker when it is selected.

Double-click the relationship line. The Edit Relationships dialog box appears.

Select or clear Enforce Referential Integrity.

Make any additional changes to the relationship, and then click OK.

If you enforce referential integrity, the following rules apply:

You cannot enter a value in the foreign key field of a related table if that value doesn't exist in the primary key field of the primary table — doing so creates orphan records.

You cannot delete a record from a primary table if matching records exist in a related table. For example, you cannot delete an employee record from the Employees table if there are orders assigned to that employee in the Orders table. You can, however, choose to delete a primary record and all related records in one operation by selecting the Cascade Delete Related Records check box.

You cannot change a primary key value in the primary table if doing so would create orphan records. For example, you cannot change an order number in the Orders table if there are line items assigned to that order in the Order Details table. You can, however, choose to update a primary record and all related records in one operation by selecting the Cascade Update Related Fields check box.

Notes: If you have difficulty enabling referential integrity, note that the following conditions are required to enforce referential integrity:

The common field from the primary table must be a primary key or have a unique index.

The common fields must have the same data type. The one exception is that an AutoNumber field can be related to a Number field that has a FieldSize property setting of Long Integer.

Both tables exist in the same Access database. Referential integrity cannot be enforced on linked tables. However, if the source tables are in Access format, you can open the database in which they are stored and enable referential integrity in that database.

Set the cascade options
You might encounter a situation where you have a valid need to change the value on the "one" side of a relationship. In such a case, you need Access to automatically update all of the affected rows as part of a single operation. That way the update is completed in full so that your database is not left in an inconsistent state — with some rows updated and some not. Access helps you avoid this problem by supporting the Cascade Update Related Fields option. When you enforce referential integrity and choose the Cascade Update Related Fields option, and you then update a primary key, Access automatically updates all fields that reference the primary key.

You might also need to delete a row and all related records — for instance, a Shipper record and all related orders for that shipper. For this reason, Access supports the Cascade Delete Related Records option. When you enforce referential integrity and select the Cascade Delete Related Records check box, Access automatically deletes all records that reference the primary key when you delete the record that contains the primary key.

Turn cascade update and/or cascade delete on or off
On the Database Tools tab, in the Relationships group, click Relationships.

On the Design tab, in the Relationships group, click All Relationships.

All tables with relationships are displayed, showing relationship lines. Note that hidden tables (tables for which the Hidden check box in the table's Properties dialog box is selected) and their relationships will not be shown unless Show Hidden Objects is selected in the Navigation Options dialog box.

Click the relationship line for the relationship that you want to change. The relationship line appears thicker when it is selected.

Double-click the relationship line.

The Edit Relationships dialog box appears.

Select the Enforce Referential Integrity check box.

Select either the Cascade Update Related Fields or the Cascade Delete Related Records check box, or select both.

Make any additional changes to the relationship, and then click OK
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:48
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to AWF!


As already mentioned, enforcing referential integrity with cascade delete will prevent incomplete data to exist, because they will be automatically deleted. But if your intention is to not let users delete the main data without first deleting the related data themselves, then please let us know. Cheers!
 

eng.trk1

New member
Local time
Today, 11:48
Joined
Dec 31, 2019
Messages
3
I would make a relationship and enforce referential integrity. Set cascade deletes to true. No code is required, it will automatically delete.

I tried to set a cascade in relationships but only one relation done..
the other relationships (no relation appear or error message appear as file attached )

I checked the records and all records are linked
 

Attachments

  • Screenshot_4.png
    Screenshot_4.png
    83.5 KB · Views: 273

eng.trk1

New member
Local time
Today, 11:48
Joined
Dec 31, 2019
Messages
3
thank you all for helping me ..
To explain what I did was I emptied all the records from the tables related to the main table
and when I delete the master record, All related records is deleted ..

thanks again
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:48
Joined
Oct 29, 2018
Messages
21,358
thank you all for helping me ..
To explain what I did was I emptied all the records from the tables related to the main table
and when I delete the master record, All related records is deleted ..

thanks again
Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.
 

Users who are viewing this thread

Top Bottom