Hi There,

I wonder if I could get help with something.

I'm working on a DB for employees. Each employee has a position within the company, so, for example, "Staff Manager 1" and "Staff Manager 2" etc.

I've made up a table called tblRole that holds the PK and the Position. A relationship is established between tblRole and tblEmployees, the FK being the autonumber PK in tblRole.

Here's my dilemma. The Roles are static which means regardless of who is assigned to a role, there will always be that role. If a person leaves or is off sick, say the "Staff Manager 1", someone else has to be assigned to that role.

I've put unique constraint (index, no duplicates) on the FK (RoleID) for tblEmployees so that only one person can be assigned to each unique role like a one to one relationship. Two people can’t be “Staff Manager 1”.

The problem I have is that if I want to change the roles of employees, perhaps a swap between employees, Access wont let this because I’d be initially creating a duplicate record when assigning the first employee to someone else’s role.

What I’m thinking about doing is using an unbound form. Changing the roles of employees with unbound dropdown lists. Then, using ADO code to remove the index from the backend table, and submitting the new values to the table and then re-Indexing the field with the ADO code.

I’m not 100% percent sure how to do this (Yet). But before I attempt this, I’d like to know if there’s an easier way to get round this. There might be and I’m just over complicating things.

10 Years
Discussion Span
Last Post by bhicks11

Why would it create a duplicate record if you are changing the person in that position? You would be editing the record rather than creating a new one. What is it about your table structure that requires adding a new record rather than changing it?

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.