Hi, I have two tables joined by PK. The form has Main and Sub. One Main can have many Subs.

I am trying to create a macro or Query that will delete a record the problem I am having is if i delete a record from table 1 (Main) the data in the table 2 (Sub) stays in the DB.

Can anyone tell me how to delete a record and the sub records at the same time?

Thanks.

Fen How

Do you have the cascade deletes set up in the relationships window?

No, should i do that? How do i do that?
Thanks

Hum... That's kind of MS Access 101. Here's a link to get you started:

http://databases.about.com/od/tutorials/l/aarelationship1.htm

thanks, it will not let me do it as it says it violates integrity rules, the DB is a one to many with the PK in Main table autonumber.

Thanks.

Fen

If you cannot define the relationship, you have orphan records that need to be dealt with first. Either delete them or add their parent records if you can figure out what they are. When the data is clean, you will be able to establish the relationship and then you can set the CascadeDelete option.