If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Deleting Records
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 |
Thread Tools | |
Display Modes | |
|
|