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.
Doc, it's always good to see other solutions especially solutions that work. Unfortunately, in this thread at least, most people are into theory which is for some of us hard to follow. A little working demo of what people actually propose would be appreciated, so we can compare real solutions. Thx.
Ah, there I have a problem, Jiri. I don't own the code for which I used that solution and was not allowed to make a copy due to NDAs. I could have gotten a copy of the code through a Freedom of Information Act request, but that was too much trouble and I was not expecting to need it anyway. Therefore I can't really post the actual code at the moment.
Here is the overview and a snippet of air code to demonstrate:
1. Assure that for each user, you have a user ID that could be as simple as the PK from an autonumbered user table. This table in my case also contained the domain login name of each user and some other info not relevant to this discussion.
2. Create a table of forms for which you want to exercise this kind of control In the table, have a PK (again, autonumbered is as good as any other) and the form's name. Doesn't matter whether this name is the internal name as it would appear in the title bar or a name that appears in a home-grown label at the top of the form screen. Just be consistent. In my case I used the form's internal name that is returned from Me.Name, but heck, you could call the forms Bob and Ted and Carol and Alice for all it mattered.
3. Build a table consisting of two fields: UserID and FormID. This is a simple junction table designed for sparse usage. A user/form combo is either IN or NOT IN this table. There is no third field. The presence of a given combination ALLOWS access. The absence of such a combination DENIES access.
4. On a maintenance form designed for this purpose, have two combo boxes. One pulls data from the user table; the other from the form table. Using whatever method floats your boat, allow selection of a user and a form. OR multi-select the forms. Have a button that saves or otherwise acts on whatever combination is selected. If you use a multi-select, the code has to save several combinations, but (a) it is a short table and (b) all the info you need is in the two combo boxes.
In my specific example, I had two lists but only displayed one of them depending on whether I was selecting "ADD" or "REMOVE." Depending on that selection, I showed the list of accessible items that either were IN or NOT IN the junction table for the user, and I did not allow user multi-select.
That way I could take someone out of the list or add them to the list. The form knew whether to do an INSERT INTO or a DELETE because I had clicked the ADD/REMOVE toggle in order to get started. In either case I could use the stored User ID to show those junction table entries specific to that user to show what they had or did not have rights to use. The list of forms .Recordsource included either an IN or NOT IN sub-query on the junction table's listed Form Id numbers. That source would get rewritten and requeried based on toggling the ADD/REMOVE button.
5. In each form's Open routine, I already knew the user from when the app loaded because it used a switchboard form that identified the logged in user from the domain login. Since this was a Navy system, the security on that login was enough that I could trust it. But see also the excellent posts by Colin (IslaDog) on security issues. So...
The key decision was a lookup. I had a list of forms. In my case, using the internal name of the form, which is of course Me.Name, I did a DCount.
Code:
Public Function AllowUsage( FName as String ) As Integer
Dim FormNumber as Long, AllowCount as Long
FormNumber = DLookup( "[FormID]", "Formlist", "[FormName] = '" & Me.Name & "'" )
AllowCount = DCount( "[UserID]", "PrivList", "[UserID]=" & CurUserID & " AND [FormID] = " & FormNumber )
If AllowCount = 0 Then
AllowUsage = -1
MsgBox "You are not allowed to use this form.", VBOKOnly
Else
AllowUsage = 0
End If
End Sub
The AllowCount would get a count of either 0 or 1. The function then returned -1 or 0 (respectively), which I passed to the Cancel parameter for the Form_Open routine. That would either block or allow the form to open.
To call this public function kept in a general module, you passed in the argument Me.Name (as an actual argument) and the function knew to test the public variable where my switchboard had stored the username at switchboard form load time.
The ONLY manual operation I required for the junction table was that I had to manually enter my own user ID and the privilege-manager form ID into the junction table - because of course THAT form was protected, too.
If you use a switchboard or dispatcher form that users cannot easily bypass, you can even develop new forms and publish your app with the new forms partly finished. I can't TELL you how much of a nightmare it would have been to be forced to keep incomplete forms in a totally separate copy of the DB because of all of the other "fixes" I had to publish while working on new features, too.
The new forms were not accessible until I added the button for the new forms to the dispatcher form and ran the form access manager. And until you have created the button and granted the access rights, the DCount for the new form always returns 0 and disallows the operation. Since I used template forms that already had lots of my infrastructure built-in, that test was already in place on every new form.
Ah, there I have a problem, Jiri. I don't own the code for which I used that solution and was not allowed to make a copy due to NDAs. I could have gotten a copy of the code through a Freedom of Information Act request, but that was too much trouble and I was not expecting to need it anyway. Therefore I can't really post the actual code at the moment.
You may want to look at post 23 for a file design for the "Per Procedure/Per control" layout I'd discussed. I haven't done this in ACCESS yet, but I've used this approach in other programming languages. Really useful when you also toss in all of the labels so you can change language on the fly to.
