Problems when a row is deleted

[scrub55434]

Screen view of Database Diagram

Screen view of problem view

I am working with a Access Data Project and MSDE database server. In the image you can see the tables that I am working with. I have a table, tblShifts, that has all of the shifts with clock in and out times for all buildings. I am trying to downsize this list to include only buildings that the user is matched with in tblManagerBuildings by using SUSER_SNAME() to get there current username. Seems easy enough.

Only I can't get it to work. It does give you only shifts in the buildings that you are assigned to. But when you go to delete or insert wierd things happen. If I have the query set up as it is in the "Screen view of problem view" link I get an error "The field 'shiftnum' is read-only" when I try to insert or delete. So I tried another way. First I set up a view that gives you all of the buildings that you have access to.

View all of the user's buildings

Then I connect that to tblShifts and get all shifts in the user's buildings:

Shift table connected to user's buildings

If I output the BuildingNum column when you go to delete a record nothing happens. If I output the username column I get the "The field 'shiftnum' is read only" error. If I don't output any of the columns from viewMyBuildings then when I delete a row it Deletes the Building and the User from ALL of the tables. Including all of the shifts associated with the building. AWWW! I know that this is possible. My problem is probably with having a many - to - many relationship, but I have been working on the for a day and a half and I think I took care of the many to many relationship properly. If anyone has any input PLEASE HELP!

 

[scrub55434]

Users are seperate from employees. The username will match the Windows Authentication username. The users will be managers that aren't in the employee database because they don't clock in or out. I could combine them but there are going to be 150 employees and under 5 users.

 

[scrub55434]

Thanks for the input. That makes sense. Can you think of a better way to structure this? I tried connecting tblMangerBuildings.BuildingNum to tblShifts.building thinking that this would connect the shift to the user but it had the same results.

As a workaround I created a trigger that set a flag column tblShifts.deleted to 1 for the row being deleted then ran a sql command that deleted all rows where tblShifts.deleted is equal to 1. The workaround seems to do the job.

Thanks Again

 

[scrub55434]

That is a good point, in most cases that would make sense. But I think it does in ours. There are 4 people involved, building manager, operation manager, general manager and systems administrator. The mass majority of the time the building manager will be the only one making changes to the building. The administrator needs to be able to make changes because they do payroll. The operations manager needs to be able to step in and cover for the building manager. Even though the general manager will only be viewing the reports they are not a security risk as they are the senior employee. So in our case it seems that there will always be at least 3 people that need to be able to make changes. Let me know if this makes sense or if in your opinion it would be better in the long run to create 3 seperate interfaces, or maybe I don't need to and there is a better approach. I am fresh out of college so I don't have a lot of experiance in what works in the long run.