Error on Delete

Why is it that when I try to delete a record in a recordset where I have a join between two tables it gives me a foreign key constraint error, but delete's the record anyway? How do I get rid of the error?

For example:

This is my sql statement that generates my recordset.

SELECT Injury.*, Members.LastName FROM Injury INNER JOIN Members ON Injury.IDNumber = Members.IDNumber

I can add and edit records just fine, but when I try to delete a record I get this error:

Run-time error '-2147217873(80040e2f)':

DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_Injury_Members'. THe conflict occurred in database 'RS', table 'Injury', column 'IDNumber'.

Any ideas?

It looks like you are trying to delete from two tables at the same time with the Members.lastname field included in your query. You can have foreign key constraint problems if you try and delete from a record whose primary key is also a foreign key in a related table.

I am assuming that you want to delete a record from the Injury table and not from the members table. If you remove the members.lastname field from your delete query and add the memberid as a variable then it might solve your problem.

Try this;

"SELECT * FROM Injury WHERE IDNumber = " & variable & ";"

OR check you join between the tables for cascading deletes. If it not checked then enabling it might also solve your problem and /or create new ones. Beware with cascading deletes they can be dangerous.