Deleting Records

In a timesheet application, I have the following tables:

Timesheet
----------------
TimesheetID AutoNumber (PK)
StaffID Integer (FK to Staff)
PeriodStartDate Date

TimesheetDetail
---------------------
TimesheetDetailID AutoNumber (PK)
TimesheetID Integer (FK to Timesheet)
DayHours1 Integer
.....
DayHours14 Integer

This is clearly a non-normalized structure, but is done intentionally to
make the input of each two-week period easier. When complete, the user posts
the records via a command button to normalized tables, from which summaries
and reports are done.

When a new staff member is added, the office manager will initilize a new
record for them. I created a form that allows them to select an active staff
member from a combo box, and type in the period start date, with error
checking to ensure the date makes sense.

When an employee leaves the company, Active will be set to No in the Staff
table. Their Timesheet record will no longer be required, and while I could
prevent deletions on the Initialization form and filter for only Active
employees, I was surprised that deleting a record from the Initialization
form ALSO deleted the related Staff member record. I don't know why it
should do this--there is no Cascading Delete R.I. set between Staff and
Timesheet.

The SQL of the form's underlying query is:

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

Can anyone tell me why this is happening?

Thank you.
Sprinks

These may be additional hints to someone:

- If I run the underlying query and delete the record, the corresponding
Staff record is also deleted.
- If I show the Timesheet table in the back-end file, it shows an expanding
'+', which if pressed, shows the related Timesheet Detail records.
- If I show the Timesheet table in the front-end fiel, pressing the '+'
shows the related Staff record.

???

Thank you.
Sprinks

"Sprinks" wrote:

In a timesheet application, I have the following tables:

Timesheet
----------------
TimesheetID AutoNumber (PK)
StaffID Integer (FK to Staff)
PeriodStartDate Date

TimesheetDetail
---------------------
TimesheetDetailID AutoNumber (PK)
TimesheetID Integer (FK to Timesheet)
DayHours1 Integer
....
DayHours14 Integer

This is clearly a non-normalized structure, but is done intentionally to
make the input of each two-week period easier. When complete, the user posts
the records via a command button to normalized tables, from which summaries
and reports are done.

When a new staff member is added, the office manager will initilize a new
record for them. I created a form that allows them to select an active staff
member from a combo box, and type in the period start date, with error
checking to ensure the date makes sense.

When an employee leaves the company, Active will be set to No in the Staff
table. Their Timesheet record will no longer be required, and while I could
prevent deletions on the Initialization form and filter for only Active
employees, I was surprised that deleting a record from the Initialization
form ALSO deleted the related Staff member record. I don't know why it
should do this--there is no Cascading Delete R.I. set between Staff and
Timesheet.

The SQL of the form's underlying query is:

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

Can anyone tell me why this is happening?

Thank you.
Sprinks