Code to delete record from multiple tables?

[kdirvin]

Hello,

I’m using Access database to track projects and checks. Projects and checks have a many-to-many relationship joined through a juncture table.

Tbl_Projects (PK ProjectID)
Tbl_Checks (PK CheckID)
Tbl_ProjectChecks (PK ProjectCheckID)

I have a main form/subform combination in which the user selects a project number on the main form and populates check data in the subform. The main form is based on tbl_Projects, and the subform is based on a query of tbl_Checks and tbl_ProjectChecks.

My subform is in Continous view. I need the user to be able to highlight a Checks record from the subform, press a command button, and delete BOTH the record in tbl_Checks and the corresponding join record in tbl_ProjectChecks.

I’m not sure what coding would be required for this action or if this can be done. Thank you in advance for any assistance!

 

[boblarson]

Re: Code to delete record from multiple tablss?

You need to run a delete query for each table. It can't be done all at once.

 

[spikepl]

Re: Code to delete record from multiple tablss?

Er, I am curious - if Cascade Delete is set, then surely deleting the one-side will cause the corresponding records on the many-side to go away from the junction table?

 

[kdirvin]

Re: Code to delete record from multiple tablss?

Thanks to both of you for your responses.

spikepl - that's exactly what I'm thinking. Cascade Deletes are set for the table and the juncture. Therefore, if I put a delete query for the table representing the one side, the record from the juncture should also delete.

How would I write such a delete query? (one that deletes the record selected in the subform from the one side table) Keeping in mind that the subform is based on a query of both the juncture table and the one-side table.

Thanks so much!

 

[spikepl]

Re: Code to delete record from multiple tablss?

Why query? Use the wizard creating the button - one of the recipes on offer is deleting the current record. You may also need to disable the warning that will pop up concerning the related record(s). In a macro click on the icon showing all available options, and then there is some Set Warning thing or other. Remember to set it back to the original after deletion. In vba code you can easily select what confirmations/warnings you want - I am not too familiar with macros.

 

[vbaInet]

Re: Code to delete record from multiple tablss?

Have a read about this:

http://office.microsoft.com/en-us/a...or-delete-a-relationship-HA010072597.aspx#BM9

 

[kdirvin]

At present, I do have a button on my subform to delete the record, which I created through Access' built-in wizard. Unfortunately, it deletes the record from the juncture table and not the record from the table representing the one side. If I could get it to delete the one-side table record, Cascade Deletes would accomplish the delete from the juncture table. I'm just not sure how to write the code so that the button deletes the one-side table record. That is where I thought a delete query might come in...?

But I honestly don't know.

Thanks again!

 

[vbaInet]

It doesn't make sense to want to delete the parent record when a child record is deleted. What would happen if you have 10 child records and you delete one of them? Does this mean that the parent record should be deleted which will cause a cascade delete to the other 9 child records?

If you're looking to do this sort of thing then I suppose you shouldn't have a one-to-many relationship in the first place. You should have a one-to-one relationship.

The natural way of things is a cascade delete should delete child records when the parent record is deleted, not the other way round.

 

[spikepl]

#7 You have goodies in a bag, and then you assign the goodies by means of a junction table. The assignment is the one that links the goodies with the assignees.

Now, if you wish to remove a goodie from the goodie-bag, then you can do so by editing the bag itself. The Cascade Delete will then assure that the goodie disappears from all assignments. But the inverse isn't true by default - removing one single assignment should not remove the goodie from the goodie-bag, because that wipes all the other assignments too. It should be done specifically.