More AllowEdits Trouble

Ally

Registered User.
Local time
Today, 18:42
Joined
Sep 18, 2001
Messages
617
I have a module, with a sub procedure preventing users with a LevelID of 2 being able to edit any forms, but those with LevelID 1 have full permissions.

Colin originally had this behind one form, but for my purposes I needed it for all forms, which is why, with Fizzio's help it's now in a module.

But ... it's working for text boxes (after putting Me.Refresh after calling the procedure), but not for anything else, like combo boxes or listboxes. I've amended the code to include the ControlTypes I want but it's not making any difference. Sometimes after a few changes, combo boxes will then start preventing AllowEdits, but it seems a bit haphazard.

The strange thing is, when Colin tried it on his one behind the form, it prevents edits to all ControlTypes and he's only expressed it for acTextBox. This is the code in the module:

Code:
Public Sub fAllowEdits(frm As Form)

Dim ctl As Control, Db As Database, r As Recordset
Dim Lev As Integer, intCanEdit As Integer, Nme As String, frmName As String
    
Set Db = CurrentDb
Set r = Db.OpenRecordset("tblUsers")

msgbox "Current form is " & frm.Name


   Call fOSUserName2
   Nme = fOSUserName2
   
Do While Not r.EOF
   
   If Nme = r.Fields("Username") Then
   Lev = r.Fields("LevelID")
   End If
   r.MoveNext
   Loop
   
   For Each ctl In frm.Controls
       With ctl
           Select Case .ControlType
               Case acComboBox, acTextBox, acListBox
                   If Lev = 2 Then
                      intCanEdit = False
                   Else
                        
                       intCanEdit = True
                   End If
           End Select
       End With
   Next ctl
    
   If intCanEdit = False Then
       With frm
           .AllowAdditions = False
           .AllowDeletions = False
           .AllowEdits = False
        End With
    Else
       With frm
           .AllowAdditions = True
           .AllowDeletions = True
           .AllowEdits = True
        End With
    End If

End Sub

The procedure is called OnOpen of each form:
Code:
    Call fAllowEdits(Form)
    Me.Refresh

Can anyone help please?
 
Ally, on looking at the code again, you seem to be doing something a little strange ;)

You seem to want to allow edits to the form depending on the security level of the user. It should not depend on the control type.

However, if you want to disable edits to those controls only, you need to change your code a bit. I'll assume that you want to disable the edits of a whole form not just certain controls. Let me know if you want to disable only certain controls.

Code:
Public Sub fAllowEdits(frm As Form)

Dim Db As Database, r As Recordset
Dim Lev As Integer, intCanEdit As Integer, Nme As String, frmName As String
    
Set Db = CurrentDb
Set r = Db.OpenRecordset("tblUsers")

msgbox "Current form is " & frm.Name


   Call fOSUserName2
   Nme = fOSUserName2
   
Do While Not r.EOF
   
   If Nme = r.Fields("Username") Then
   Lev = r.Fields("LevelID")
   End If

r.MoveNext
Loop
   
If Lev = 2 Then
   intCanEdit = False
   Else
   intCanEdit = True
End If
    
   If intCanEdit = False Then
       With frm
           .AllowAdditions = False
           .AllowDeletions = False
           .AllowEdits = False
        End With
    Else
       With frm
           .AllowAdditions = True
           .AllowDeletions = True
           .AllowEdits = True
        End With
    End If

End Sub

This will disable all controls if the user level is 2.
 
Sorry - this is better as I can't amend listboxes now, but combo boxes still do the same thing.

Also, there is going to be the occasional list box that you double click to open another record that I am going to want enabled for users with ID 2s.
 
In that case, you want to individually disable each control, and control the edits.

Try this.
Code:
Public Sub fAllowEdits(frm As Form)

Dim ctl as control, Db As Database, r As Recordset
Dim Lev As Integer, intCanEdit As Integer, Nme As String, frmName As String
    
Set Db = CurrentDb
Set r = Db.OpenRecordset("tblUsers")

msgbox "Current form is " & frm.Name


   Call fOSUserName2
   Nme = fOSUserName2
   
Do While Not r.EOF
   
   If Nme = r.Fields("Username") Then
   Lev = r.Fields("LevelID")
   End If

r.MoveNext
Loop
   
If Lev = 2 Then
   intCanEdit = False
   Else
   intCanEdit = True
End If
    
   If intCanEdit = False Then
       With frm
           .AllowAdditions = False
           .AllowDeletions = False
           .AllowEdits = False
        End With

  For Each ctl In frm.Controls
           Select Case ctl.ControlType
               Case acComboBox, acTextBox, acListBox
               ctl.enabled = false
               ctl.locked = true
             End Select
   Next ctl
    
    Else
       With frm
           .AllowAdditions = True
           .AllowDeletions = True
           .AllowEdits = True
        End With
  For Each ctl In frm.Controls
           Select Case ctl.ControlType
               Case acComboBox, acTextBox, acListBox
               ctl.enabled = true
               ctl.locked = false
             End Select
   Next ctl
    End If

End Sub

This will disable the controls so thay cannot be meddled with but also stop edits etc on the form.
 
I can get past one form and go from that to another. Then I get an error message:

Run-time error 2164: You can't disable a control while it has the focus. :( and the red bit below highlighted.

Code:
For Each ctl In frm.Controls
           Select Case ctl.ControlType
               Case acComboBox, acTextBox, acListBox
               [color=red]ctl.Enabled = False[/color]
               ctl.Locked = True
             End Select
   Next ctl
 
This is because you are trying to disable a control that has the focus. Set the focus to another control in the form - best method is to set the tab order to a control that will not be locked.

or

If you want, you can remove the ctl.enabled in both cases and it will simply just lock the control, not disable it. The main difference with this is that when it is disabled, you cannot select it but when it is locked, you cannot edit it.
 
That's great - have cut out the enabled bit and it's fine - thank you muchly.

One more thing, if pos, is: I don't want to disable all the command buttons obviously, as they are the door to other forms, but when the command buttons are AddNew and Delete I do want to disable them when the users ID is 2. Is there any way of referring to these in the module. I have tried to, but I get "variable not defined".

What I've done is (please don't laugh - you know my coding skills) :) :

Dim cmdDel as Control

cmdDel = cmdDelete

Alternatively I know I can do what I need behind each form.
 
This modification (in blue) will disable command buttond called cmdDelete and cmdAddNew if the level is 2

Code:
   If intCanEdit = False Then
       With frm
           .AllowAdditions = False
           .AllowDeletions = False
           .AllowEdits = False
        End With

  For Each ctl In frm.Controls
           Select Case ctl.ControlType
               Case acComboBox, acTextBox, acListBox
               ctl.locked = true
               [color=blue]case acCommandButton           
               if ctl.name = "cmdDelete" or ctl.name="cmdAddnew" then
              ctl.enabled = false
              end if[/color]
             End Select
   Next ctl
    
    Else
       With frm
           .AllowAdditions = True
           .AllowDeletions = True
           .AllowEdits = True
        End With
  For Each ctl In frm.Controls
           Select Case ctl.ControlType
               Case acComboBox, acTextBox, acListBox
               ctl.locked = false
               [color=blue]case acCommandButton           
               ctl.enabled = true[/color]
            End Select
   Next ctl
   End If
 
Last edited:
That's brilliant. Thank you so much.
 

Users who are viewing this thread

Back
Top Bottom