Unwanted Deletion

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form for a timesheet application based on the following query:

SELECT Timesheet.StaffID, Timesheet.PeriodStartDate, Staff.UserName,
Staff.FName & " " & [LName] AS FirstLast
FROM Timesheet INNER JOIN Staff ON Timesheet.StaffID = Staff.StaffID;

In testing today, I deleted a record and was surprised that not only the
Timesheet record was deleted but also the corresponding record in the Staff
table. Clearly I'm misunderstanding something basic about queries.

Can anyone explain this behavior and tell me what I need to do change?

Thank you.
Sprinks
 
It looks like you created a relationship between these two tables in the
relationships window and checked both Enforce Referential Integrity and
Cascade Delete Related Records.

Barry
 
Barry,

Thanks for your response. That was my first thought too, but I couldn't
initially find any relationship between the Timesheet table and the Staff
table. After your comment, I looked more carefully, and realized that my
relationship between the Timesheet and TimesheetDetail tables was linked on
the StaffID, so the Cascade Delete spec was unintentionally deleting not only
the detail records but also the Staff record.

I've changed the Timesheet primary key now and its working correctly.

Thanks again.
Sprinks

Barry Gilbert said:
It looks like you created a relationship between these two tables in the
relationships window and checked both Enforce Referential Integrity and
Cascade Delete Related Records.

Barry

Sprinks said:
I have a form for a timesheet application based on the following query:

SELECT Timesheet.StaffID, Timesheet.PeriodStartDate, Staff.UserName,
Staff.FName & " " & [LName] AS FirstLast
FROM Timesheet INNER JOIN Staff ON Timesheet.StaffID = Staff.StaffID;

In testing today, I deleted a record and was surprised that not only the
Timesheet record was deleted but also the corresponding record in the Staff
table. Clearly I'm misunderstanding something basic about queries.

Can anyone explain this behavior and tell me what I need to do change?

Thank you.
Sprinks
Click to expand...
Click to expand...
 
Back
Top