A better way than a bunch of If-Then DLookUp's (3 Viewers)

[isladogs]

I'm answering on my tablet as my computer is packed ready for moving house tomorrow.

I really don't understand why you are responding in such an attacking manner.
I'm not 'admitting' that my example was flawed. It was an example that shows how the tag property can manage the state of groups of controls. I never claimed it was based on user levels but it is perfectly suitable for that purpose and I have used it for that on many occasions.
I'm not running away from you or anyone else but I don't take kindly to being challenged in the aggressive tone you are using.

However I've already answered the same point in my reply to Tera in post #14
Neither of us know whether the OP's requirements are anything like your example. But let's assume they are.

Tag the buttons as 1 2 3 …. 17
Set all buttons hidden by default
Then use select case for each user e.g.

Select Case User
Case 1
ShowControls True, "1", "3", "7", "10", "12", "16"

Case 2
….

As I've already stated, my code is intended for situations where you have a group of controls that would be all made visible or hidden at once. However, it works for your example and is as concise as your code.

Hopefully that will be enough to explain the approach...as it may be several days before I am able to post any further replies
I am perfectly happy whichever solution the OP chooses.
In the meantime, do please get rid of the hostile attitude that you seem to be showing. It doesn't help anyone.

 

[chuckcoleman]

Mark, first, I apologize, I've been wrapped up on another project, but still need to solve this performance issue. To answer your question is number 2: Each person has individually designated permissions for each control. You cannot see the control unless you have permissions to see it.

More to follow later.

 

[Mark_]

Were I to be doing this, I would have a child table under your user table.
AccessControlID - AutoNumber
UserID - Same as the ID in your user table
FormName - String - Holds the name of the form the control is on
ControlName - String - Holds the name of the control
ControlVisible - Boolean - Can this user see this control.

When you open the form you will have all controls defaulted to .Visible = False
I am guessing you already have a way of knowing who is logged in, so you would add a function that sets the controls for you. It gets passed the UserID and the Form name.

In the function, you open a recordset. This would effectively be
"Select * from ControlTable Where FormName = '" & PassedFormName &"' AND UserID = " & PassedUserID
You would then loop through the records and set each control based on the tables values.

Reason you default all to .Visible = False is to make sure if you accidentally forget to update a user record for a control they won't see it. You'll have to add a way to update each user. I'd probably have a second table that has just the form/control so you can copy from there for each new user.

If this approach will work for you, great! If you need more help in getting it to work let us know.

 

[NauticalGent]

ChuckColeman,

Just in case the waters were not muddy enough...I have a system I use that may interest you.

I have a few forms that I want all my users to see but there are some controls I do not want some to be able to change and some I do not want others to see at all.

The concept I use is simple. I have a table that holds all the user's responsibilities. For example:

PK Title
0 DBA
1 Operations
2 Supervisor
3 Technician
4 Guest

In another table I assign each user a responsibility that corresponds with the responsibility table (1-4)

On the forms in question I use the tag property to assign a number value 1-4. When the open opens I loop through all the controls and if the user is a guest then every field is not enabled.

Any fields that I do not want the Techs to see I will assign a tag value of 2, for Operations 1 and so on. In other words, I assign a numeric value that is LESS then their responsibility value.

When the form loads, any control's tag value that is less then the user's responsibility value is either not enabled or not visible.

I was concerned at first about the amount of overhead it would cost to do this every time the form loads but there has been no noticeable delay.

I am not sure if this is what you are looking for but if you are interested I can up-load a sample DB that you can adapt to your needs.

Standing by...

 

[Micron]

Not muddy AFAIC. I said in post 2 "or get the user level from a table" or something similar. Too difficult to exactly quote using my phone but that's the gist of it.

 

[Mark_]

NG,

Looks like he needs per user restrictions. I figured I'd ask him which way he needs to go first, that way we don't give a dozen approaches that don't meet his given needs.

 

[Solo712]

I'm answering on my tablet as my computer is packed ready for moving house tomorrow.

I really don't understand why you are responding in such an attacking manner.
I'm not 'admitting' that my example was flawed. It was an example that shows how the tag property can manage the state of groups of controls. I never claimed it was based on user levels but it is perfectly suitable for that purpose and I have used it for that on many occasions.
I'm not running away from you or anyone else but I don't take kindly to being challenged in the aggressive tone you are using.

However I've already answered the same point in my reply to Tera in post #14
Neither of us know whether the OP's requirements are anything like your example. But let's assume they are.

Tag the buttons as 1 2 3 …. 17
Set all buttons hidden by default
Then use select case for each user e.g.

Select Case User
Case 1
ShowControls True, "1", "3", "7", "10", "12", "16"

Case 2
….

As I've already stated, my code is intended for situations where you have a group of controls that would be all made visible or hidden at once. However, it works for your example and is as concise as your code.

Hopefully that will be enough to explain the approach...as it may be several days before I am able to post any further replies
I am perfectly happy whichever solution the OP chooses.
In the meantime, do please get rid of the hostile attitude that you seem to be showing. It doesn't help anyone.

Colin,
the elementary problem with your approach is that you would have to hard-code the permissions in the form where the buttons are to be accessed. This means you would have to change the code every time a new user is introduced, or his permissions have been altered to add or remove a button.

In my scenario OTOH you would add or change the access control flag in the user profile form and there is a way to do this interactively, of course. The code in the form module with the buttons would handle any combination of permissions. It does not need to change ever because it is controlled by an external user attribute.

As for my aggressive tone, Colin, I use it when someone is asking for like you did in post #10.

Isn't using the tag property significantly easier?
If you have two or more conditions affecting different groups of controls then set some controls with tag = A & others tag = B (or whatever values you like).......

Using tags here is not only not significantly easier it is a silly overkill. You can't control the buttons efficiently by tags where you have a large number of possible (and changing) combinations in permissions and an unknown number of users. Frankly, I am surprised that you don't see it and I am put off by your insisting on it.

Best,
Jiri

 

[isladogs]

Chuck/ NG
Just to say that, in case it wasn’t clear before now, the approach Nautical Gent described in post 24(?) was what I had in mind as well.

If your users fit into specific user groups it is a widely used and easily applied approach. However there may be other more suitable methods if every user is different.

 

[The_Doc_Man]

The only danger in having each user have a specific profile is that if you have 17 forms, each user needs one flag for each form. But on the day you have 18 forms, you have to add a new flag for each user. This is a many-to-many relationship.

Mechanically, I might suggest a table with two columns: Something to represent the user and something to represent the form. When you click the button to open the form, you have to go find something about the form - such as its name - and something about the user - again, perhaps the name. Then do a DCOUNT underneath that button on the permissions table where you are searching for the user identifier and the form identifier. If you make this a "sparse" table (i.e. only have an entry if the user is permitted to use the form) then you either get 0 or 1 as a count.

Then remember that the name of the form doesn't have to be long and involved. Also remember that you could do a lookup table with the long form name if you had to find the form's code number. But the idea is then on some kind of maintenance form, you have two combo boxes - the users and the names of the forms. Then pick one from each combo and click some button to actively save the pair in your "can access" table. And that is how you would populate the list of forms allowed to a user.

With a little bit of code, you could even do a multi-select list and just use VBA to iterate through the form choices to see which ones were highlighted (Selected = True).

 

[Privateer]

Chuck, I am going to chime in because I think you are focused on reports when you are really asking an application wide security question. If you have confidential reports, then you have forms with confidential data to which only a few people should have access. So, I am assuming you must limit access to forms with sensitive data as well as reports, which implies your database requires users to login. This login form should be based on a table with the user's login name, password, status ID (“Enabled” (1) or “Disabled” (2)) and an access level ID. Another table I call access level has something like "Admin" (1), "Manager" (2), or "User" (3). Once you confirm a person's password and that the account is enabled, you also get their access level, which I use to limit which forms they have access to and what objects are enabled on the form. Assuming you click on a button to open a form, a simple IF statement checks their access level and either denies or allows access to the form. When the form opens, I use a case statement, again, based on the access level, to enable or disable objects on the form, which allows everyone to see the fields, and only some to change them. This looks better than turning the visibility on or off.
However, you asked about reports and I do that differently. Everyone has access to the report form, but their access level determines which reports are available to them, which I think is the gist of your question. I use a report table with fields like report name, description, and access level ID, that 1,2,3 again. Rather than seventeen buttons, I use a combo box to select the reports listed in this report table, only they are filtered by their access level. When a manager opens the report form, the combo box displays only the reports where the access level ID is greater than or equal to 2. You might run into issues with this strategy, like where a manager should not have access to a user level object, and in those rare situations, I have created departments, like Accounting, Human Resources and Senior Management, and used that as a further filtering device. One last thing, in the after-update event of the report combo box, I populate a text box on the form with the description of the report. This can be a few words or sentences, but it really helps the users select the right report. Hope this helps.

 

[chuckcoleman]

NG, thank you, (and everyone else). I'm swamped fighting another problem and haven't been able to devote time to this problem that I still want to solve. NG, can you upload your sample db and let me take a look at it?

For everyone, I have an employee table that has a sub-table with the name of a control in a field and a Y/N field that is used to identify if that employee has access Y/N to each control. I believe the reason the Report Form is so slow to open is because there are 17 control buttons on that form and when the form opens up, it runs 17 DLookUp's to see if the user can see/is visible for each of the 17 control buttons. Hope this helps.

 

[Micron]

Most of all of this is personal preference - including passwords and logins. IMHO, there is only one basic reason to bother with a login process, and that's when the pc is shared and users cannot be relied on to log themselves out. Otherwise, why not just get the Windows login ID and if it's not in the db, anyone attempting to enter is denied? Less forms, less hassle over forgotten passwords, etc.

I can't recall anything from the OP that states a group policy approach can't be taken, but then this one has been all over the place and maybe I've forgotten that this was covered. Otherwise, I know I would group users according to some sort of scheme that works (e.g. Admin, Supervisor, Manager, User, whatever) where those values are sorted by level number, and IF form and/or control access needs to be restricted, those forms/controls can be "allowed" based on the numerical user level. Using #'s means that when ordered, you can either provide access on an = or < or > basis, and doesn't require use of control tags - unless you want level numbers to be in the tag property. My method of enabling/disabling controls would either be like
If dbUserLevel > 1 Then
With Me
...

if there were only a few. Otherwise I would loop the control tags for = , <, > some number.

I also try to weigh the complexity of a method in consideration of those who may come after me and will have to work with it, which is why I'd favor one of these over some of the other suggestions.

Most importantly, there hasn't been a lot of feedback from the one for whom this should really matter, and it got a bit ugly at times, so I think I will unsubscribe. Good luck to the OP with the project.
Ciao!
EDIT
Last OP response wasn't there when I started but will leave things as is.

 

[Solo712]

NG, thank you, (and everyone else). I'm swamped fighting another problem and haven't been able to devote time to this problem that I still want to solve. NG, can you upload your sample db and let me take a look at it?

For everyone, I have an employee table that has a sub-table with the name of a control in a field and a Y/N field that is used to identify if that employee has access Y/N to each control. I believe the reason the Report Form is so slow to open is because there are 17 control buttons on that form and when the form opens up, it runs 17 DLookUp's to see if the user can see/is visible for each of the 17 control buttons. Hope this helps.

Hi,
the sub-table is a good idea but there should be a single variable per user (long integer if you are not planning to go over 32 reports, or a string) that manages the individual controls for him/her. The one-on-one method that you have contributes to the sluggishness of your code - you are making 256 duplicate tests. At any rate, I have whipped up a little demo for you to consider. It uses a single variable to manage the access to the controls (you can change the "enable" property to "visible"). Let me know if it helps.

Good luck,
Jiri

 

[The_Doc_Man]

Privateer, you are right about the idea of using "roles" per user, but Chuck has made it clear that he has nearly as many roles as he has users. In the cases I had to use with the Navy, I could get by with three roles plus the "role" of being a disallowed user. But in effect, Chuck potentially has 17 roles, or claims to.

 

[Solo712]

Privateer, you are right about the idea of using "roles" per user, but Chuck has made it clear that he has nearly as many roles as he has users. In the cases I had to use with the Navy, I could get by with three roles plus the "role" of being a disallowed user. But in effect, Chuck potentially has 17 roles, or claims to.

Not quite true, Doc. Chuck has 17 users, each assigned a role. There are 131,072 possible roles that he can assign.

Best,
Jiri

 

[Mark_]

Chuck,

One thing you can do, since you already have the table that holds each control and a yes/no for it, is to make ONE query that returns the child records for a given user.

Once the form opens, open the query for your current user.
Loop through the records in the query.
For each control, set its .visible based on what you have in your query.

This should be very quick.

 

[The_Doc_Man]

True enough, Jiri - 2^17 is 131K - but the point was that it was more than 3 or 4 roles.

 

[gemma-the-husky]

There are two ways to do this.


If you have a bunch of yes/no fields in the employees record, then you need a select query to read that record, and then you can enable/disable the controls by checking the relevant field on the record. So one dlookup only (or rather a select query, but it;s the same thing).

My earlier idea was to compress the 17 flags into a single field. In practice, there isn't going to be much difference in clock ticks between a single dlookup to get the compressed field, and a single query to get quick access to all 17 flags. Having a 4-byte record does make it somewhat easier to add extra flags, as you automatically have 32 flags with a 4-byte record. If you have individual flags, you have to keep redesigning tables to include the new flags.

If you have an employee record, and then a set of 17 yes/no options linked to the employee in a subtable, (which makes it easier for you to add new options if you ever need to), then you need 17 reads. Even if you maintain a sparse matrix including only the records set to true (and therefore a missing record is by definition false), you still need 17 reads.

One other point. Generally it's easier to manage permissions by assigning users to groups. If you have already established the permissions for Group "Normal Users", then instead of having to set all the permisisons for a new employee, you can just allocate him to Group "Normal Users" - but this doesn't solve the issue regarding the time taken for 17 reads.
(although I note the suggestion above to read all the flags with a single query, and then iterate the recordset, which again should be quicker than doing the same thing 17 times.)

Anyway, I hope this gives you some ideas.

 

[Solo712]

There are two ways to do this.


If you have a bunch of yes/no fields in the employees record, then you need a select query to read that record, and then you can enable/disable the controls by checking the relevant field on the record. So one dlookup only (or rather a select query, but it;s the same thing).

My earlier idea was to compress the 17 flags into a single field. In practice, there isn't going to be much difference in clock ticks between a single dlookup to get the compressed field, and a single query to get quick access to all 17 flags. Having a 4-byte record does make it somewhat easier to add extra flags, as you automatically have 32 flags with a 4-byte record. If you have individual flags, you have to keep redesigning tables to include the new flags.

If you have an employee record, and then a set of 17 yes/no options linked to the employee in a subtable, (which makes it easier for you to add new options if you ever need to), then you need 17 reads. Even if you maintain a sparse matrix including only the records set to true (and therefore a missing record is by definition false), you still need 17 reads.

One other point. Generally it's easier to manage permissions by assigning users to groups. If you have already established the permissions for Group "Normal Users", then instead of having to set all the permisisons for a new employee, you can just allocate him to Group "Normal Users" - but this doesn't solve the issue regarding the time taken for 17 reads.
(although I note the suggestion above to read all the flags with a single query, and then iterate the recordset, which again should be quicker than doing the same thing 17 times.)

Anyway, I hope this gives you some ideas.

Dave,
I entered the discussion here supporting your idea of using a bit-encoded flag. Of all the solutions offered here, I consider your original suggestion the most natural, effective and surely the fastest way to manage access to the buttons. I have provided a demo in post #33 showing how to implement the bit-switching battery. This is a simple, interactive way to set or clear the switches for all the present and future users. It is also quite flexible, leaving the admin to choose any combination of reports for any user, and changing it without any restrictions. You may superimpose a group policy on the button selections but that is outside of what the OP asks for.

P.S. A single long integer can manage 32 controls. That should be more than enough for the given scenario leaving room for 15 spare controls in the future.

Best,
Jiri

 

[The_Doc_Man]

I am not against bit-twiddling when compaction is needed. However, for those who are not comfortable with bitwise operators, you can still run into issues with awkward syntax.

The reason I mentioned a list based on a junction-table between person and form and using a DCount to see if that combination is allowed? Because you only need to add the code for a new form or the code for a new person to the respective single-subject tables where you would do the lookup. If you have to add a new user or a new form, all of your previous data remains valid, and other than data updates to two tables, you do not require changes. Your logic doesn't change at all. Only your data changes.

In a language and an environment where space is NOT a premium but programming complexity IS an issue, you want to make the minimum changes when you make changes at all. And that was the purpose of my suggestion. It SHOULD NOT be taken as deprecating any other offered solution... but I had method in my madness. AND what I offered works whether you need just a couple of roles are whether you need a list of ALL things the person could open individually enumerated.