Delete Record link to a Jonction Table

[Renel]

I tried with a search on the site to find a solution to my problem, but without success.

So I submit in a more precise way this problem:

In my DB there are three forms, say: Movies, Actors and Actresses. I want to clear a record from the Movies form that has Actors or Actresses names associated with it and their role via control tables (subform). This link is created in a JTActors (or JTActresses) junction table containing 3 fields, it looks like this (each field is referenced with the tables: TFilms, TActors, TRoles):

jIDFilm   jIDActor   jIDRole
1            4             1
1            99           2
1            23           2

When I try to delete a record from the Movie form via the Delete Record button in the LibreOffice navigation bar, I get the deletion warning, but no deletion of the record occurs. The same thing happens if I use a button with the delete record command except for a SYS_FK_84 Constraint Violation error.

I understand that all actor and actress associations made with the movie ID of the record must first be removed from the join table before the movie record can be deleted. So, I have to alter the junction table.

I believe the following command might do the job:

ALTER TABLE “JTActors” ADD CONSTRAINT SYS_FK_84 FOREIGN KEY(“jIDFilm”) REFERENCES “TFilms”(“FilmID”) ON UPDATE CASCADE or ON DELETE CASCADE (if you have a better suggestion…)

But is it possible that this command can be executed when clicking the Delete Record button on the LibreOffice navigation bar or a button that would be added to the form? If yes, how to run this command?

Please pay attention to it.

[RobertG]

Open Tools → Relationships.
There you have created the relation between the tables.
Right mouse click on the connecting line between the two tables → Edit
There you could change the constraint between the two tables.
Choose Update cascade and Delete cascade.
Press ‘OK’ and save the relationship.
Save the database.

[Renel]

Thanks RobertG, it was the good think to do.