User Permissions - hide/disable form controls

[Futures_Bright]

Hi all,

I've hit a problem that, based on reading through several forums, many people have had however I'm not having any luck implementing the solutions for my needs!

Basically I've set up two modules within my database - one called User Permissions (to retrieve the level of access the current user should have - run on startup) and then Control Visibility is designed to contain the Sub to hide/disable all relevant controls. The User Permissions module works cleanly and retrieves the name of the group the User belongs to (currently limited to "User", "Reviewer" and "Admin" however there may be another category and I will add some lines to ensure - should a user somehow appear in a new group - that they are treated as "User" as well) - This result is referred to in the following code as UserAccess.

Just to warn you, this code is basically butchered from several sources and put in an order that makes sense to me, but clearly not VBA (I've self-taught VBA to carry out specific tasks so my coding knowledge is probably just past pseudo-code)!

Public Sub Permissions()
Dim ctl As Control
Dim frmName As Form
frmName = Screen.ActiveForm
For Each ctl In frmName.Controls
   'Make all controls invisible and uneditable
        frmName.ctl.Visible = False
        frmName.ctl.Enabled = False
 
        Next ctl
 
If UserAccess = "Admin" Then
    For Each ctl In frmName.Controls
        frmName.ctl.Visible = True
        frmName.ctl.Enabled = True
     Next ctl
 
Else
    For Each ctl In frmName.Controls
 
            'Make User only controls visible, not editable
            If ctl.Tag = Null Then
                frmName.ctl.Visible = True
                frmName.ctl.Enabled = False
              'exit loop if regular user
                If UserAccess = "User" Then
                    Next ctl
                End If
            'Make Reviewer Controls visible and editable where necessary
            ElseIf ctl.Tag = "ReviewVi" Then
                frmName.ctl.Visible = True
                frmName.ctl.Visible = False
            ElseIf ctl.Tag = "ReviewEd" Then
                frmName.ctl.Visible = True
                frmName.ctl.Enabled = True
                'exit loop if reviewer
                If UserAccess = "Reviewer" Then
                    Next ctl
                Else
                End If
            End If
        Next ctl
        Set ctl = Nothing
End If
End Sub

As this code is located in a module to be called up by each form used, it doesn't allow me to use the Me. expression - hence why I'm using Screen.ActiveForm instead (and will add a line to SetFocus when opening a new form!) - this is what's causing me the latest problems - debug currently points at 'frmName = ' (just before Screen.ActiveForm) stating "Invalid use of property". I'm also not entirely sure that 'Next ctl' is the best way to stop the code for a control and move onto the next one. Finally, I'm aware that "ctl.Tag = Null" might be incorrect and might be "" instead, however my code has hit problems before even arriving at that bit so I've not been able to check yet - if you know for sure do let me know!

Also if you see any other problems with this code, please let me know!

Thanks for any and all help. Kind regards,

Michael

 

[Futures_Bright]

Some Slight Progress (I think). The error is no longer coming up on "frmName =". I've changed frmName (As String) = frmCurrentForm.name (where frmCurrentForm = Screen.ActiveForm and is set in the code for the Form On_Load). All previous references to frmName are now "Forms(frmName)". I can post the new code tomorrow if necessary

The errors now are falling, as I expected, on the 'next ctl' commands where I'm trying to restart the loop on the next control when the user's permission is reached. Can anyone tell me how it is best to do this? Exact error message is "Compile error: Next without For"