Access Relationships

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

Guest

I have a 1 to many relationship from Employees to TimeSheets. But when I
delete a record from TimeSheets it also deletes the corresponding record from
Employees.
I also removed Enforce referential integrity from the relationship but still
the same thing happens from the form.
 
I have a 1 to many relationship from Employees to TimeSheets. But when I
delete a record from TimeSheets it also deletes the corresponding record from
Employees.
I also removed Enforce referential integrity from the relationship but still
the same thing happens from the form.
Click to expand...

It sounds like you're deleting in the wrong place, perhaps deleting a
record from a Query joining TimeSheets to Employees. Deleting a child
record should NOT delete that record's parent.

How are you doing the deletion? With a Query? (Post the SQL of the
query). On a Form? (Post the recordsource of the form and describe how
you're doing the deletion).

John W. Vinson[MVP]
 
I'm deleting thru the form. The record source of the form is a Query though.
Here's the record source query:

SELECT Employees.EmployeeID, Employees.FirstName, Employees.LastName,
TimeSheets.HoursWorked, TimeSheets.EATSTallyflag,
TimeSheets.InvoiceTallyflag, TimeSheets.WeekEnding, TimeSheets.EmployeeID,
TimeSheets.ProjectID
FROM Employees INNER JOIN TimeSheets ON Employees.EmployeeID =
TimeSheets.EmployeeID
ORDER BY Employees.FirstName, Employees.LastName, TimeSheets.WeekEnding;
 
I'm deleting thru the form. The record source of the form is a Query though.
Here's the record source query:

SELECT Employees.EmployeeID, Employees.FirstName, Employees.LastName,
TimeSheets.HoursWorked, TimeSheets.EATSTallyflag,
TimeSheets.InvoiceTallyflag, TimeSheets.WeekEnding, TimeSheets.EmployeeID,
TimeSheets.ProjectID
FROM Employees INNER JOIN TimeSheets ON Employees.EmployeeID =
TimeSheets.EmployeeID
ORDER BY Employees.FirstName, Employees.LastName, TimeSheets.WeekEnding;
Click to expand...

That's why it's deleting both. The query contains both tables so when
you delete a record from the query, you're deleting the Employees
record (and, by implication, the related timesheets record).

Could you consider instead using a Form based on Employees, with a
Subform based on TimeSheets (showing multiple weeks if you wiah)? This
would allow you delete a timesheet record from the subform without
affecting the Employees table at all.

The alternative is to put a custom Delete button on the form to
specifically delete a record from the Timesheets table, using a Delete
query:

DoCmd.RunSQL "DELETE * FROM TimeSheets WHERE TimeSheets.EmployeeID = "
& Me!EmployeeID & " AND TimeSheets.WeekEnding = #" & Me!WeekEnding &
"#"


John W. Vinson[MVP]
 
Back
Top