Access Relationships

[Venus]

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.

 

[John Vinson]

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.

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]

 

[Venus]

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;

 

[John Vinson]

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;

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]

 

[Venus]

Thank you. I used the button instead of recreating the form.