Delete Query with five 1 to 1 tables

[State]

I have five tables that are related by 1 to 1 relationships (I broke down a former very large table into five tables, the fields are unique and I have normalized them as much as possible). I am trying to create/run a delete query (form a command button) which will delete the record from each table given a unique id number (each record in each table has a corresponding id number; the query gets the id number from a textbox on a form). When I attempt to run the query, I get the message "Specify the table containing the records you want to delete". I am not sure what this means and how to best go about deleting the selected record from all five tables. Thank you.

Paul

 

[Jerry Stoner]

There may be an easier or more elegant way but you could run 5 delete queries back to back from wherever you are running your code now.As long as the criteria is identical for each query except the table name it should work.Youll obviousy want to turn off warnings before running.
Jerry

 

[BLeslie88]

Private Sub cmdDelRecords_Click()
Docmd.runsql("Delete * From Table1 Where Table1.RecordID = " & txtYourID.value & ";")
Docmd.runsql("Delete * From Table2 Where Table2.RecordID = " & txtYourID.value & ";")
Docmd.runsql("Delete * From Table3 Where Table3.RecordID = " & txtYourID.value & ";")
Docmd.runsql("Delete * From Table4 Where Table4.RecordID = " & txtYourID.value & ";")
Docmd.runsql("Delete * From Table5 Where Table5.RecordID = " & txtYourID.value & ";")
End Sub

Hope this helps.... Of course the field name for each recordID may change.....

Regards,

 

[Pat Hartman]

One of the tables must be the "parent" table. In the relationships window, add all 5 tables to the window. Draw join lines between the "parent" table and each of the other for tables. The relationship diagram should look like a fan rather than a line. Select, enforce referential integrity and cascade delete. Then when you delete a row from the "parent" table, the related rows in the other four tables will also be deleted.

 

[State]

Thank you Pat and BLeslie88 for your assistance and further educating me in Access. I had referential integrity enforced, but was not aware of cascade delete. Once I selected cascade delete for each join, the delete query worked like a charm.

Have a great weekend.

Paul