Security function

Looking for a simple function to check current user and permissions status - to run on load of each form.

Option Compare Database
Option Explicit
Private Sub Form_Current()
    Me.CurrentUser = GetUserName()
End Sub

Private Sub Form_Load()
    Dim nUser As String
    nUser = GetUserName()
    If DLookup("UserGroupRecID", "TblUser", "UserName =" & Chr(39) & nUser & Chr(39)) = 1 Then  'Admin
        Me.AllowEdits = True
        Me.AllowAdditions = True
        Me.AllowDeletions = True
        Me.AllowFilters = True
    ElseIf DLookup("UserGroupRecID", "TblUser", "UserName =" & Chr(39) & nUser & Chr(39)) = 2 Then  'UserRW
        Me.AllowEdits = True
        Me.AllowAdditions = True
        Me.AllowDeletions = False
        Me.AllowFilters = True
    ElseIf DLookup("UserGroupRecID", "TblUser", "UserName =" & Chr(39) & nUser & Chr(39)) = 3 Or 4 Then  'UserRO & Guest
        Me.AllowEdits = False
        Me.AllowAdditions = False
        Me.AllowDeletions = False
        Me.AllowFilters = True
    End If
End Sub

Open in new window


I currently am using this on each form and would like to convert it to a Global function - find one that is a global function.  I am already using the GetuserName()
Karen SchaeferBI ANALYSTAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
Move the code from Form_Load to a standard module.  Then in each Form_Load event, call it.

Private Sub Form_Load()
    Call CommonValidate
End Sub
Karen SchaeferBI ANALYSTAuthor Commented:
Move the code from Form_Load to a standard module.
This is what I need help with.

what is CommonValidate?  never heard of it is there a function that is suppose to go with it?
PatHartmanCommented:
The new procedure is named "CommonValidate".  All I did was copy the code that was in the form load event and move it into a new function.  This new function MUST be defined in a standard code module.  In order to be reusable, it should not be in the form itself.  Create a new standard module if you don't have one that is suitable.

Public Function CommonValidate()
    Dim nUser As String
    nUser = GetUserName()
    If DLookup("UserGroupRecID", "TblUser", "UserName =" & Chr(39) & nUser & Chr(39)) = 1 Then  'Admin
        Me.AllowEdits = True
        Me.AllowAdditions = True
        Me.AllowDeletions = True
        Me.AllowFilters = True
    ElseIf DLookup("UserGroupRecID", "TblUser", "UserName =" & Chr(39) & nUser & Chr(39)) = 2 Then  'UserRW
        Me.AllowEdits = True
        Me.AllowAdditions = True
        Me.AllowDeletions = False
        Me.AllowFilters = True
    ElseIf DLookup("UserGroupRecID", "TblUser", "UserName =" & Chr(39) & nUser & Chr(39)) = 3 Or 4 Then  'UserRO & Guest
        Me.AllowEdits = False
        Me.AllowAdditions = False
        Me.AllowDeletions = False
        Me.AllowFilters = True
    End If
End Function

Open in new window

Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Karen SchaeferBI ANALYSTAuthor Commented:
Thanks, however, not sure the code can call a Me. ..., thought it would have to use form name.subformname  etc.?

I get invalid use of Me keyword.

K
Nick67Commented:
No Points
You are right K
Change the declaration
Public Function CommonValidate(f as form)
Replace Me with f

Then call it so

Private Sub Form_Load()
     Call CommonValidate(Me)
 End Sub
Karen SchaeferBI ANALYSTAuthor Commented:
If I call this function on the parent form, do I needed it on the child subforms also?

K
Nick67Commented:
I don't think so.
A subform is a control, and a locked control isn't to going to permit edits of any type

BUT, it is better to test that assumption thoroughly first than have egg-on-face later
Karen SchaeferBI ANALYSTAuthor Commented:
Ok which status should I set for read-only user that still need to be able to select a criteria from drop downs to view the data,  or do I need to get more granular in my code to handle this instance?
Nick67Commented:
At the moment, you are nuclear on the form.
One approach would be to walk through the Controls collection of f and set enabled/disabled based on criteria, rather than nuking the whole form.
f.AllowEdits is the bugger because you can't change unbound controls then, either

Keep the other three and handle what controls are permitted to be locked/unlocked by walking the Controls collection.
With something like this, you walk it looking for

Dim ctrl as control
For Each ctrl In f.Controls
    If ctrl.ControlType = acComboBox And Nz(ctrl.ControlSource,"") = "" Then
        ctrl.Locked = Myboolean
    ElseIf ctrl.ControlType = acTextBox And Nz(ctrl.ControlSource,"") = "" Then
       ctrl.Locked = Myboolean    
    ElseIf ctrl.ControlType = acCheckBox And Nz(ctrl.ControlSource,"") = "" Then
        ctrl.Locked = Myboolean
    End If
Next ctrl

and I set myboolean = true or false based on the conditions.
Then I only need one block of  f.something = myboolean type statements
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You may also need to use a table-based approach if you want to enable certain controls only. This can be tedious, since you need to remember to add any new controls to the table, but essentially:

1. Create a table with a structure like this:

tControls
ID
ParentName
ControlName
Enabled
AllowEdits
AllowAdditions
AllowDeletions
etc etc

Now add rows to that table for each Form and Control you need to control, and set the permissions.

Now when you open a form, use a function like this:

Function SetPermissions(ObjectName as string)

Dim ctl As Control
Dim rst As DAO.Recordset
Set rst = Currentdb.OpenRecordset("SELECT * FROM tControls WHERE ParentName='" & ObjectName & "'"

Do Until rst.EOF
  ctl = Forms(ObjectName).Controls(rst("ControlName"))
  ctl.Enabled = rst("Enabled")
  rst.MoveNext
Loop

End Function

You can also use the Tag property of each control, but a table-based approach allows you greater latitude. For example, if you have ReadOnly users, DataEntry users, Management users, etc, you could enhance the table-based approach to handle the differing needs of those users.
PatHartmanCommented:
Sorry, I forgot to change the me. references to a form object when I put your code into a separate procedure.  

I also rarely lock an entire form since it interferes with buttons and search fields.  Here is code I run in the Current event of each form AND subform.
The first snippet checks their security and on this form I have additional security related to a delete button so even if they have permission to update, they may not be allowed to delete.

Looking at the LockControls code, you can see that the code checks for Lock and UnLock.  These are used to toggle the lock/unlock process because even on a form where you want to allow updates, you may still want to lock some controls and vice versa.  Even when they can't update, you may still want to allow some controls to work such as buttons and combos used for filtering.

I haven't tried Scott's method which is ultimately more flexible, but I think mine might be slightly easier to implement since you won't have to remember to change your lock properties table whenever you add/delete a control.  The method I am suggesting uses the Tag property but ONLY for variations so on most forms all the tag properties are empty.  It would only be the controls that I want to control specifically to say that they will ALWAYS be locked or ALWAYS be unlocked.  So on forms where I show changed by and changed date, those controls would always be locked since the user never gets to change them; they are under program control and on forms that allow filters, the filters would always be unlocked.

If I were going to implement Scott's suggestion, I would add some code that validates that all data entry controls and buttons on all forms are accounted for.

    If Forms!frmLogin!chkOKCLI = True Then
        Call LockControls(Me, False)        'unlock
        If Forms!frmLogin!txtLevel > 8 Then
            Me.cmdDelete.Enabled = True
        Else
            Me.cmdDelete.Enabled = False
        End If
    Else
        Call LockControls(Me, True)         'lock
    End If

Public Sub LockControls(frm As Form, bLock As Boolean)
    Dim ctl As Control
    For Each ctl In frm.Controls
    Select Case ctl.ControlType
        Case acTextBox, acComboBox, acListBox, acCheckBox
            Select Case ctl.Tag
                Case "NoLock"
                    ctl.Locked = False
                Case "Lock"
                    ctl.Locked = True
                Case Else
                    ctl.Locked = bLock         'toggle locks
            End Select
        Case acOptionGroup, acOptionButton, acCheckBox      ''not working
             Select Case ctl.Tag
                Case "NoLock"
                    ctl.Locked = False
                Case "Lock"
                    ctl.Locked = True
                Case Else
                    ctl.Locked = bLock         'toggle locks
            End Select
        Case acCommandButton
            Select Case ctl.Tag
                Case "NoLock"
                    ctl.Enabled = True
                Case "Lock"
                    ctl.Enabled = False
                Case Else
                    ctl.Enabled = Not bLock         'toggle locks
            End Select
    End Select
Next ctl
Set ctl = Nothing
End Sub

Open in new window

Karen SchaeferBI ANALYSTAuthor Commented:
thanks Pat, I will give it a try, the only issue is that my Tag field is already being used for the Audit Log captures.

K
Karen SchaeferBI ANALYSTAuthor Commented:
 If Forms!frmLogin!chkOKCLI = True Then
        Call LockControls(Me, False)        'unlock
        If Forms!frmLogin!txtLevel > 8 Then

is there a way to use a variable or active form instead of hard coding the form name?  Also as of now my user groups consist of Admin, UserRW (Read write, no delete), UserRO & Guest(Read only & filters).  I am not using a login form, I am currently use GetUserName() function.

K
Karen SchaeferBI ANALYSTAuthor Commented:
Forms!frmLogin!txtLevel > 8  Where does this value come from?
PatHartmanCommented:
When the database opens, users are presented with a login form.  I verify their credentials and then hide the login form and open the application menu.  Therefore, whenever I need to check security I refer back to controls on the hidden form.  That is why the form name is hard-coded.  If you have the user enter credentials on every form, you can refer to the current form using Me.somecontrol.

The last bit of code was straight out of my application.  I wasn't suggesting that you copy it exactly.  It was simply an example of working code.  The security in this app is a bit of a mish-mash since it was copied from an earlier application and then expanded upon.  There are lots of methods you can use.  In this case, there are a bunch of checkboxes that signify special authority and then level numbers that are used for more general authority.  I use the level concept in many apps but rather than checkboxes, I normally create a table with a row for each special authorization since it requires no schema change if I have to add one as the app grows.

If your tag field is already being used, you would need to modify your current code to allow for multiple values.  Then rather than checking for = "Lock", you would use Instr(Me.control.Tag, "Lock") to find out if the Tag contains the "Lock" value.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Karen SchaeferBI ANALYSTAuthor Commented:
still working with this please keep opening.

K
Karen SchaeferBI ANALYSTAuthor Commented:
Thanks for the input, however, this project was cancelled I no longer need assistance with this issue.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.