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.