Command Button to DELETE from 2 tables

Hi,

I am sure this is super easy to most people, so be patient with me.

I have a form that references 2 tables (form within a form). The main form references the Contact Info of an employee (tblContact_Info). The subform references which team or teams this employee is on (tblLookup_UserTeam). All the records are tied together by the UserID. tblContact_Info will only have 1 entry with the UserID. tblLookup_UserTeam could have one or multiple records with the UserID.

I would like to put a command button on the main form to "Delete Record". So if an employee is no longer needed in the database, I could click this button and Delete the Employee's Contact Info from tblContact_Info, as well as any and all records associated with that employee's UserID in tblLookup_UserTeam.

What code would I need to associate with this command button?

THANK YOU!!!!

Assuming you are using bound forms

Add a command button, to you main form, using a wizard (delete operation criteria)

Set up a relationship between these two tables that includes cascade delete.

With this done, when you delete the main table record, all related records will automatically be deleted as well.

S-

For some reason my database will not allow this. Is there code I can associate with the command button on the main form to delete from the second table?

What message do you get when you try to establish the relationship?

S-

It says the data will not allow for this relationship to cascade deletions.

But honestly, I was hoping I could get your help with SQL or VBA code to put behind the button as you did in the other posting. That seems more reliable in the long run.

Thank you.

Truthfully, letting the database do the work is always more reliable.

However, with that said. I rarely allow cascade delete, I rarely allow the users to delete the parent records and automatically delete the child records. Most users don't understand the far reaching effects of the cascade delete (coded or otherwise).

For example Sales order system, you have orders where people buy your inventory. After a year, you find that you no longer sale a inventory item and you delete it from your system. If you allowed cascade delete (through relationships or code), it would also delete all the sales records for that item ever being sold. Hence you would never want to delete the inventory item.

This doesn't appears to be a problem in your case.

It would be easier for me to provide you will a good suggestion on the best way for you to do this if you would post a sample of your DB

I have also included a sample DB that shows you how to delete a record useing code. (I would still establish that relationship becuase it is more effective to let the DB do the work)

S-