Need to lock all fields except the search box (1 Viewer)

[Uncle Gizmo]

Thanks @Sodslaw - love the user name BTW

Ha! Hadn't spotted that!

 

[Pat Hartman]

I don't use "Screen.Active" anything since there are situations when the active form or control is not necessarily what you think it is. I prefer to be specific so I pass in a reference to the common code with the form I want it to operate on. Take a look at the documentation

Screen.ActiveForm property (Access) | Microsoft Docs

If a subform has the focus, ActiveForm refers to the main form. If no form or subform has the focus when you use the ActiveForm property, an error occurs.

 

[Uncle Gizmo]

If a subform has the focus, ActiveForm refers to the main form. If no form or subform has the focus when you use the ActiveForm property, an error occurs.

This is a problem I solved using my Call/Called Class Module:-

CallCalled Class Module - Nifty Access

CallCalled Class Module I will be adding information about my "CallCalled" Class Module here, as it becomes available. The most recent example of it is on Access World Forums here:-VBA to reuse data entry popup form Class Module - Overview Class Module - Overview Video 1 (01:09) You can see how...

www.niftyaccess.com

(VBA Code at Bottom of the Web Page) ...

When you are handling controls with "Screen.ActiveControl" it doesn't matter if the control is on the main form or the subform you still maintain the link between your code and the original object. However I wanted to trigger code in the subform, code to simulate the after update event of the "Screen.ActiveControl" and as you have indicated Pat, the subform doesn't actually exist as a separate form, you only get access to the main form.

This is the code that finds the subform:-

Set mfrmCallingForm = fGetParentForm(ctrlActiveControl)     'Store the Form the Control is on, (not always/necessarily the active Form).

 

[Pat Hartman]

Thanks Uncle. But I'm not looking for a solution. I was just explaining why I don't use Screen.ActiveControl. The code I posted passes in a reference to the form that is running the code to the function doing the locking. "Me" works whether the calling form is a main form or a subform. Others might find the info useful though.

Call LockControls(Me, False) 'unlock

Call LockControls(Me, True) 'lock

 

[avincent61]

This is the code I use when I want everything on the same form. Since my apps have security, I check it in the form's current event and then lock or unlock the form based on what authorization the user has. In your case, use the call to lock or unlock from wherever you want to control the locking.

To make this work, I use the tag property of each control. The tag property needs to be set ONLY for exceptions. So, on a form where normally everything is going to be locked but you want the search fields to be unlocked, mark the search fields as "NoLock". In a form where normally everything is going to be unlocked but you want some fields to always be locked, then tag those fields with "Lock".

Private Sub Form_Current()
    If Forms!frmLogin!txtLevel >= 8 Then
        Call LockControls(Me, False)        'unlock
    Else
        Call LockControls(Me, True)         'lock
    End If
End Sub

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 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

This was absolutely perfect for my application! I only used the "'lock" portion of the Private Sub. In the Public Sub, I used Case"" for the fields to remain unlocked so I didn't have to apply tags to all of my controls (there's a lot). Lastly I commented out the command button portion for later use. You rock!

 

[Pat Hartman]

You're welcome. I'm glad you found it useful.

In the Public Sub, I used Case"" for the fields to remain unlocked so I didn't have to apply tags to all of my controls (there's a lot).

You need to review the logic again. The ONLY controls that require a Tag are those that you ALWAYS want to remain locked or unlocked so it is rarely more than a few no matter how many controls you have on the form.

If a control is ALWAYS unlocked (this is the most common usage), set the tag to NoLock. You use this for unbound controls used for searching. This is the gotcha' when you use the AllowEdits = False setting. When you do this, that not only locks the editable controls but also the unbound search controls. Usually, if a control is always locked, such as the PK field, you set the control's locked property to Yes. This code gives you a second option.

If you have a couple of controls that you want to keep mostly locked but only unlock for individuals with specific security clearence, you can make a small modifcaton to the code to add a security level check. This way, you can lock and/or hide fields like salary and SSN but show and/or unlock if the user has special security. So, the code is even more useful with slight tweaks.

Look again at the code. If the tag is not either Lock or NoLock, then the code toggles the lock property. That means if the control is locked, the code unlocks it. If it is unlocked, the code locks it. This part has nothing to do with what the tag value is.

 

[Pat Hartman]

@avincent61 Although likes are always welcome, they are not very useful in identifying whether a post solved a problem since there is no way to distinguish between a "thanks for trying" from a "that clarified the code for me and I'll fix my app to use the code correctly".

 

[avincent61]

@avincent61 Although likes are always welcome, they are not very useful in identifying whether a post solved a problem since there is no way to distinguish between a "thanks for trying" from a "that clarified the code for me and I'll fix my app to use the code correctly".

My apologies...To clarify per your previous response: All of my form fields are unlocked by default. OnCurrent, my code loops through the form fields and locks all of them, then unlocks one specific field by name, then based on higher level user security level, unlocks the fields that have the tag "QCLock" (referenced in the module code), then, based on highest level of user security, allows edits/deletions etc., which unlocks any fields that are still locked. At least that's how I was able to achieve my particular goal. I have multiple user security levels to get through, which is why I start by locking everything, then unlocking based on the user. I do have error handling, but I have more code after the one below, so I didn't include it here.

Dim intnewrec As Integer
 
    intnewrec = Form.NewRecord
        If intnewrec = False And IsNull(Me.CreatedRecord) Then
            Me.CreatedRecord.Value = Forms!frmlogin!txtLogin
'            MsgBox "You're in a new record." This was to test the code
        Else
'            MsgBox "This is NOT a new record" This was to test the code
        End If
        
        
    Dim LoginType As Integer
    Dim User As String
    Dim ctrl As Control
        
    '   set user = a value of the txtLogin textbox on the Navigation Form
    User = Forms![frmlogin]!txtLogin
    '   Assign the UserSecurity from the user login to LoginType
    LoginType = DLookup("UserSecurity", "tblPersonnel", "UserLogin = '" & User & "'")
    
        '   Lock all controls if user login type is less than allowed level
        If LoginType < 5 Or LoginType = 10 Or LoginType = 11 Or LoginType = 8 Then
            '   Loop through all controls on form and lock them
            On Error Resume Next
            For Each ctrl In Me.Controls
                ctrl.Locked = True
            Next ctrl
            On Error GoTo 0
        ElseIf LoginType = 5 Or LoginType = 7 Or LoginType = 9 Then
            '   Lock controls meant only for Administrator
            Call LockControlsQC(Me, True) 'lock
        Else
            Me.AllowDeletions = True
            Me.AllowEdits = True
            Me.AllowAdditions = True
        End If

        '  Unlock one specific control
        Me.SIR_Status.Locked = False

 

[Pat Hartman]

I'm glad you have a solution. When you have to manage sets of controls based on login type, the solution is more complex.

I would rethink the LogInType concept so that each type belongs to a group. That means a table that defines all valid values of LogInType and for each, adds a groupID. That way, instead of hardcoding multiple type values in multiple forms, you can make a single procedure that you pass in a group in addition to the form reference. Over time, this will save you a lot of coding and also minimize future code changes if you end up having to add more types. If you notice, the code I posted is held in a standard module therefore it does NOT exist in every single form as your code needs to. If you have 50 forms, which isn't a lot, you have to repeat the code 50 times. If you have to change it, you have to change it in 50 places. In my app, I have to change the code in one, single module. HUGE difference.

 

[avincent61]

I'm glad you have a solution. When you have to manage sets of controls based on login type, the solution is more complex.

I would rethink the LogInType concept so that each type belongs to a group. That means a table that defines all valid values of LogInType and for each, adds a groupID. That way, instead of hardcoding multiple type values in multiple forms, you can make a single procedure that you pass in a group in addition to the form reference. Over time, this will save you a lot of coding and also minimize future code changes if you end up having to add more types. If you notice, the code I posted is held in a standard module therefore it does NOT exist in every single form as your code needs to. If you have 50 forms, which isn't a lot, you have to repeat the code 50 times. If you have to change it, you have to change it in 50 places. In my app, I have to change the code in one, single module. HUGE difference.

Considering I use public codes in my modules that check users security level, I'm disappointed that I didn't think to go the route you're suggesting for the forms themselves. The user security level isn't the same for all of my forms, but there are groups of forms that could use the same code. Thanks for this.

 

[Pat Hartman]

It isn't too late to change Using the group concept going forward makes maintenance much easier.

I also assign a group to a form. Then the check is always the same for each form. You take the form's group and look it up in the user's groups.

For special cases, you might have to define a group that has only a single form. Users get to belong to multiple groups. A simple dCount() is the only code you need. I also separate view, add, change, delete. So the security check for each happens in a different event procedure. The view check happens in the Open event - the form doesn't open if the groups don't match. For Update, the code goes as the first statement in the form's BeforeUpdate event. If the user isn't allowed to update the form, the code uses me.Undo and Cancel = True to clear any changes and cancel the update. For add, you use the BeforeInsert event. Code is the same as the BeforeUpdate event. And for Deletes, you use the on Delete event and also Cancel = True.

Very little code to handle the whole thing. Handling new forms and new people just means updating the two groups.

 

[avincent61]

It isn't too late to change Using the group concept going forward makes maintenance much easier.

I also assign a group to a form. Then the check is always the same for each form. You take the form's group and look it up in the user's groups.

For special cases, you might have to define a group that has only a single form. Users get to belong to multiple groups. A simple dCount() is the only code you need. I also separate view, add, change, delete. So the security check for each happens in a different event procedure. The view check happens in the Open event - the form doesn't open if the groups don't match. For Update, the code goes as the first statement in the form's BeforeUpdate event. If the user isn't allowed to update the form, the code uses me.Undo and Cancel = True to clear any changes and cancel the update. For add, you use the BeforeInsert event. Code is the same as the BeforeUpdate event. And for Deletes, you use the on Delete event and also Cancel = True.

Very little code to handle the whole thing. Handling new forms and new people just means updating the two groups.

Thank you so much Pat!
This has given me a whole new perspective to work with.
Sorry for the slow reply.

 

[Pat Hartman]

Security checking can get very complex. Once you work to tablize the rules, it gets ever so much easier