User Permissions - For Each loop doesn't appear to work

[Futures_Bright]

[Solved] Collection - For Each loop doesn't appear to work

Hi all,

Another day, another problem with my database design it seems. I decided to test the User Permissions module in my database today and unfortunately it didn't work as I expected. The code is below with the problem area highlighted.

Dim ctl As Control
Dim frmCurrentForm As Form
Dim frmName As String
Dim colUser As Collection
Dim colReviewVi As Collection
Dim colReviewEd As Collection
Dim colAdmin As Collection
On Error GoTo err_Permissions
Set frmCurrentForm = Screen.ActiveForm
frmName = frmCurrentForm.Name
 
For Each ctl In Forms(frmName).Controls
   'Make all controls invisible and uneditable
        ctl.Visible = False
        'if label, Enabled doesn't exist and so goes to error
        ctl.Enabled = False
        Next ctl
       Set ctl = Nothing
 
'Group all controls
For Each ctl In Forms(frmName).Controls
    Select Case ctl.Tag
        Case "User"
            colUser.Add ctl, ctl.Name
        Case "ReviewVi"
            colReviewVi.Add ctl, ctl.Name
        Case "ReviewEd"
            colReviewEd.Add ctl, ctl.Name
        Case "Admin"
            colAdmin.Add ctl, ctl.Name
        Case Else
            On Error Resume Next
            colUser.Add ctl, ctl.Name
    End Select
 
    Next ctl
'    Set ctl = Nothing
 
 
'enable and make visible relevant controls
[COLOR=red]Select Case UserAccess[/COLOR]
    Case "Admin"
        For Each ctl In Forms(frmName).Controls
            ctl.Visible = True
            ctl.Enabled = True
            Next ctl
'            Set ctl = Nothing
[COLOR=red]Case "Reviewer"[/COLOR]
[COLOR=red]   For Each ctl In colUser[/COLOR]
[COLOR=red]       ctl.Visible = True[/COLOR]
[COLOR=red]   Next ctl[/COLOR]
[COLOR=red]'        Set ctl = Nothing[/COLOR]
 
[COLOR=red]   For Each ctl In colReviewVi[/COLOR]
[COLOR=red]       ctl.Visible = True[/COLOR]
[COLOR=red]   Next ctl[/COLOR]
[COLOR=red]'        Set ctl = Nothing[/COLOR]
[COLOR=red]   For Each ctl In colReviewEd[/COLOR]
[COLOR=red]       ctl.Visible = True[/COLOR]
[COLOR=red]       ctl.Enabled = True[/COLOR]
[COLOR=red]   Next ctl[/COLOR]
[COLOR=red]'        Set ctl = Nothing[/COLOR]
 
    Case "User"
        For Each ctl In colUser
            ctl.Visible = True
        Next ctl
'        Set ctl = Nothing
    Case Else
        MsgBox "You have not been assigned permissions for this library. Please contact the administrators immediately", vbCritical, "Error - User Permissions"
    End Select

When UserAccess = "Admin", the code works fine. The reason that Set ctl = Nothing is set as comments is because that appeared to be the problem at this point (ctl = nothing), ctl.Name is "<Object variable or With Block not set>". I based this code on the one found through this link, except I'm adding controls to multiple groups and then setting the permission based upon the user's Access level.

When stepping through the process, ctl doesn't change from Nothing once it is judging the users access level and so does not loop each of the For statements (presumably because there 'is no next').

Can anyone tell me what I've done wrong here please? have i referenced the collections incorrectly? Are there problems when having 'For Each' loops in series?

 

[Rx_]

You set up a collection and assigned controls (probably names) into it.
Then you are asking for each control from the collection.

I just feel there is something missing at that point.

What I have done is to put a Tag in a control with a string that can be hashed.
For example: for permission for levels of Federal, State, County
Use a tag of Fed (fed only) FedSt (Fed and State) St (State only), FEDSTCO (all 3)
If the Permission was FED
For each control where the TAG contains "FED" in the string - make it visible.

I don't have time to look it up at the moment. But, it would seem to me that you need a collection "of controls".

 

[Futures_Bright]

Thank you for your comment Rx, however I'd rather use the system I'm setting up as there's a clear hierarchy (i.e. Admin>Reviewer>User) so it is easier for others who might maintain the database in future to decide on minimum level required for visibility/editing and assigning that tag.

I've been reading through error handling (an area I have no experience of prior to this database) and am confident the problem is arising from this. See below for my current code (comments etc. will be cleaned up once I have it working)

Option Compare Database
Option Explicit
'Set Visible/Enabled dependent on User group
Public Sub Permissions()
Dim ctl As Control
Dim frmCurrentForm As Form
Dim frmName As String
Dim colUser As Collection
Dim colReviewVi As Collection
Dim colReviewEd As Collection
Dim colAdmin As Collection
On Error GoTo err_Permissions
Set frmCurrentForm = Screen.ActiveForm
frmName = frmCurrentForm.Name
Err.Clear
For Each ctl In Forms(frmName).Controls
   'Make all controls invisible and uneditable
        ctl.Visible = False
        'if label, Enabled doesn't exist and so goes to error
        ctl.Enabled = False
'Group all controls
        Select Case ctl.Tag
            Case "User"
                colUser.Add ctl, ctl.Name
            Case "ReviewVi"
                colReviewVi.Add ctl, ctl.Name
            Case "ReviewEd"
                colReviewEd.Add ctl, ctl.Name
            Case "Admin"
                colAdmin.Add ctl, ctl.Name
            Case Else
                colUser.Add ctl, ctl.Name
        End Select
        
        Next ctl
        Set ctl = Nothing
        

'    Set ctl = Nothing
               
               
'enable and make visible relevant controls
Select Case UserAccess
    Case "Admin"
        For Each ctl In Forms(frmName).Controls
            ctl.Visible = True
            ctl.Enabled = True
            Next ctl
'            Set ctl = Nothing
    Case "Reviewer"
        For Each ctl In colUser
            ctl.Visible = True
        Next ctl
'        Set ctl = Nothing
        
        For Each ctl In colReviewVi
            ctl.Visible = True
        Next ctl
'        Set ctl = Nothing
        For Each ctl In colReviewEd
            ctl.Visible = True
            ctl.Enabled = True
        Next ctl
'        Set ctl = Nothing
        
    Case "User"
        For Each ctl In colUser
            ctl.Visible = True
        Next ctl
'        Set ctl = Nothing
    Case Else
        MsgBox "You have not been assigned permissions for this library. Please contact the administrators immediately", vbCritical, "Error - User Permissions"
    End Select
    

exit_Permissions:
Exit Sub
err_Permissions:
If Err.Number = 438 Then
    Err.Clear
    Resume Next
ElseIf Err.Number = 0 Then
    Err.Clear
    Resume Next
Else
    If gcfHandleErrors Then
        Call PROC_ERR
        
        GoTo exit_Permissions
    End If
    MsgBox "Error " & Err.Number & " just occured."
    
End If
    Resume exit_Permissions
    
End Sub

As before, everything works fine when an admin logs in, however stepping through the code suggests to me that a zero error is arising every time a ctl is added to a collection (strangely I no longer get the 438 error when ctl.Enabled doesn't exist!). I thought Err.Clear would remove this but it clearly hasn't (as you can tell from my code, I probably don't know how/when to use it!).

It still gets the same problem as before - when it gets to the stage where (in theory at least) all the controls are grouped and the user access level is known, the ctl is not picked up in the for loop at all and this leads to cycling through the code once (going to zero error and resuming next at every stage of the red highlighted text from the original post) and then finishing without actually doing anything.


Can anyone shed any light onto why I might be getting these zero errors or what to do with them?


Kind regards,

Michael

 

[Rx_]

An error number of 0 means no error, everytihng is fine.
An err.clear resets the Err.Number back to 0 (zero)
Take a look at the debug.print statement - this can help you determine what errors are taking place.
The STOP statement just halts the code so you can inspect what is going on.

The for each control - sometimes you might have a control that doesn't have a property like the others - like a Label doesn't have an "enable" property for example.

A couple of ways to handle that - one is trap the error by number, reset the error (Err.clear or Err.number = 0) and resume next.
- or in front of the for each - just put On Error Resume Next - then right after the For Each structure - go back to your orginal error trap.

Looks like you are making progress. Keep learing the debugger, it will drastically improve your ability to code.

Sub TestErrorTrap()
Dim x, y, z As Integer
On Error GoTo MyTrap
x = 1
y = 2
z = x / 0
z = 3
Exit Sub
MyTrap:
Debug.Print "Err is " & Err.Number & " " & Err.Description
Stop
z = 3
Resume Next
End Sub

Once the code has stopped - take a close look at the collections in the debug window. You should be able to inspect the elements in that collection.

 

[Futures_Bright]

Oh right, so are the Zero errors appearing just because I'm stepping through the process? (i.e. if I just ran it they wouldn't appear?)

How do I see the elements of the collection? It might help solve the problems I'm having since the errors are occuring when I try to do For each ctl in Collection.

Edit: Oops, looks like I've spent the last 2-3 days racking my brain after missing a fairly fundamental mistake - I've not Set each collection = New Collection (i.e. not allocated the memory for it)!

Code working now