Update Query or Is this a Bigger Issue?

[jpjordan357]

Hey everyone. I've been working in and out of Access for a few years now with varying degrees of knowledge and know-how, but not consistently enough to retain a lot of the more advanced stuff, unfortunately. Anyway; I recently made a database in order to better keep track of employees and their permissions. We are a role-based organization, so I have a table of employees, roles, and systems they have access to, for the purposes of this question. I have a master form which pulls primarily from 'Employee' and child form which will show their system permissions based on what the 'Role' field is on the employee table:

I have a few maintenance forms which allow me to add roles, systems that will populate the 'Role Assignment' combo above.

HR departments being what they are, like to change roles. Just a lazy example would be, for example, changing a 'Call Center Rep' to 'Call Center Specialist'. The problem I'm having is when I change a role's name in my maintenance form (which then updates the combo box), it doesn't update the stored value in the table for that employee, nor the system permissions that go with it. So if I go back to my main employee form, Jane Doe, who is a Call Center Specialist, still shows as being a 'Call Center Rep'. Consequently, if I change this, the child form for the Role Assignment blanks out, because the query that ties permissions with roles still ties those permissions to a 'Call Center Rep'. Now, it's not a huge deal for me to do a find and replace and clean it up, but if it could happen automatically, or semi-automatically, that would be great. I was trying to imagine if this could be done with an update query after an update on the maintenance form?

I feel like i'm close, but am looking for advice on accomplishing this, or if there's an easier way to accomplish this.

After this, i'll be posting another one related to this database, so stay tuned .

 

[Gasman]

You should be storing the ID of the relevant record, not the description? Also referring to the ID field for linkage, criteria.
Then this would not happen.
As soon, as the role is updated, any table record using that as a FK would show the new description.

 

[jpjordan357]

You should be storing the ID of the relevant record, not the description? Also referring to the ID field for linkage, criteria.
Then this would not happen.
As soon, as the role is updated, any table record using that as a FK would show the new description.

Agreed. I thought I was doing this, but just displaying the description. This will need to be changed in the tables, right? How can I start untangling this?

 

[jpjordan357]

This is the problem here, yes? The Short Text being the datatype rather than the number?

 

[Gasman]

Yes. I would expect you to have somethig along the lines of
Employees
Roles
EmployeeRoles
The last table is a junction table that links an employee with a role.

Yes, you display the RoleName, but store the ID (bad name BTW ) SHoule be more line RoleID in that table and RoleIDFK in EmployeeRoles
Same with Employee table, shoule be something Like EmployeeID with EmployeeIDFK in EmployeeRoles.

Otherwise when you use ID, Access complains it is unsure which one you mean, also easy for debugging and amending further down the road.

 

[jpjordan357]

Yes, i've run into that; I may re-engineer that. So I think my brain is starting to recall; I had this problem before, which is why I switched it to short text, not realizing that I broke it in the process. That RoleID field below is a test field I made in order to try it out. Unfortunately, the combobox displays the record number rather than the clean 'Role Name'.

 

[jdraw]

Not sure of your overall requirement, but have seen some role-based-access-control (RBAC) previously.
Here is a link that may be useful.
You can search "RBAC" in the forum(s) for more.

I would not recommend using lookups at the table field level.

 

[jpjordan357]

I'm looking around access world here, and apparently I shouldn't be using lookup for clean text in tables, just forms, i'll putz with that for a bit.

 

[Gasman]

See here how to create combos as you need.
Basically, you hide the first column (My columns always have the ID as the first field) with 0 width and make that the bound field.
You then display the second column, which is the descriptive field.

I would also have a field in the Role table which shows what is Active, then just select from that WHERE RoleActive. You can add the 'IS true' is you wish, to make it clearer to some.

 

[ebs17]

Serious considerations start with the database schema, i.e. tables and relationships. Do it, show what you've got.

Forms and the ping-pong on them are initially extremely uninteresting, even if some large, colorful pictures make people happy.

In short: I would expect a many to many relationship between the person and the role. A new role for a person would be a new record in the junction table with a validity timestamp.

 

[jpjordan357]

See here how to create combos as you need.
Basically, you hide the first column (My columns always have the ID as the first field) with 0 width and make that the bound field.
You then display the second column, which is the descriptive field.

I would also have a field in the Role table which shows what is Active, then just select from that WHERE RoleActive. You can add the 'IS true' is you wish, to make it clearer to some.

I spent the day yesterday redoing all of those short text fields in the various tables, their references in queries, and the associated things on forms and reports, and everything seems to be operating as desired. thank you for pointing me in the right direction.