We help IT Professionals succeed at work.

Allow edits in three checkboxes when the form is AllowAdditions = False, AllowDeletions = False, and AllowEdits = False

SteveL13
SteveL13 used Ask the Experts™
on
I have a form that when on open the following VBA code executes:

    If DLookup("[SecurityLevel]", "LOCALtblCurrentUser") > 1 Then
        Me.AllowAdditions = False
        Me.AllowDeletions = False
        Me.AllowEdits = False
    Else
        Me.AllowAdditions = True
        Me.AllowDeletions = True
        Me.AllowEdits = True
    End If

Open in new window


However, if the users security level is >1 I DO want them to be able enter values in three checkboxes.  How can I allow that to happen?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Generally, instead of setting AllowEdits = False, your best bet is to create a procedure (LockControls) which will loop through all of the controls on the form and lock or unlock all of the controls other than the 3 you want to edit.  Then, instead of using AllowEdits = True/False, you would use

LockControls True
or
LockControls False

If I were doing this, I'd include parameters in the procedure for Form, LockOrUnlock, and then include a parameter array which would allow you to pass in a list of controls to ignore during the LockControls process.  If you did this, you would call the procedure like:

LockControls  me, "Locked", "chkBox1Name", "chkBox2Name", "chkBox3Name"

Dale

Author

Commented:
I don't know how to do that.
Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014
Commented:
You could also use the GotFocus and LostFocus events of those checkboxes to allow edits. First, create a form-level variable:

Dim fAllowEdits as boolean

Now in the GotFocus event of your checkboxes:

fAllowEdits = Me.AllowEdits
Me.AllowEdits = True

In the LostFocus event:

Me.AllowEdits = fAllowEdits
Distinguished Expert 2017

Commented:
Here are two blocks of code.  The first you need to customize based on what your lock/unlock criteria is.  The second, you just place in a standard module (NOT a form's class module) and you can use it from any form.  I developed this code because as you have discovered, the AllowEdits is all or nothing.  Sometimes I use it for the same purpose that you want but mostly I use it so that I can enable all my search features on a form but not the bound controls.  The controls are placed into three categories.
1. Always Lock no matter what - Tag property = Lock
2. Always UnLock no matter What - Tag property = UnLock
3. Lock/Unlock depending on toggle switch value - most common and most controls don't need to have this property set.

The code relies on the tag property of a control.
    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

Open in new window

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

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Create a new code module in your application and paste in this code:

Enum LockUnlock
    Locked = -1
    Unlocked = 0
End Enum

Public Sub LockUnlock(frm As Form, LockState As LockUnlock, ParamArray ExcludeControls() As Variant)

    Dim ctrl As Control
    Dim intLoop As Integer, bSetState As Boolean
    
    For Each ctrl In frm.Controls
        
        Select Case ctrl.ControlType
            Case acCheckBox, acListBox, acComboBox, acTextBox, acOptionGroup
                bSetState = True
                For intLoop = LBound(ExcludeControls) To UBound(ExcludeControls)
                    If ctrl.Name = ExcludeControls(intLoop) Then
                        bSetState = False
                        Exit For
                    End If
                Next
                If bSetState Then ctrl.Locked = LockState
            Case Else
                'do nothing
        End Select
        
    Next

End Sub

Open in new window

Then call this with:
LockUnlock me, Locked, "chkBox1Name", "chkBox2Name", "chkBox3Name"

Open in new window

This will not lock controls which are on a subform of the current form.  To do that, you would have to modify the code with another test to determine whether the control type is a form and if so, call the function again from within itself.

Author

Commented:
I ended up using Scott's recommendation.  Seemed the simplest and it worked.