Conditionally Lock Data on Current Form

Takunda Mafuta

Registered User.
Local time
Yesterday, 22:53
Joined
Sep 4, 2019
Messages
13
I am a newbie to MS Access VBA. I want to lock fields on a form (PaymentRequest) "Payment Request 1" using a checkbox such that if I go to the next form (PaymentRequest) "Payment Request 2" the fields will be editable until I also lock them with a checkbox on that particular form. I have tried to use the following code but it is locking not just the fields on the current form but also fields on all the subsequent forms instead which is not what I want. The code is as below:

Code:
Private Sub ApprovedHOD_AfterUpdate()

If IsNull(ApprovedHOD.Value) Then
    Me.Department.Locked = False
    Me.Payee.Locked = False
    Me.NatureOfPayment.Locked = False
    Me.Currency.Locked = False
Else:
    Me.Department.Locked = True
    Me.Payee.Locked = True
    Me.NatureOfPayment.Locked = True
    Me.Currency.Locked = True
End If

End Sub

Also take a look at the screenshot and the subject database file I have attached.
 

Attachments

  • PRFaccessworld.accdb
    PRFaccessworld.accdb
    928 KB · Views: 133
  • LockFieldsConditionally.jpg
    LockFieldsConditionally.jpg
    108.2 KB · Views: 151
Another method of doing this uses the Tag property of the various controls.
Assign the same tag value to the 4 controls e.g. X

Add this code to a standard module

Code:
Public Sub LockControls(State As Boolean, Tg1 As String, Optional Tg2 As String, Optional Tg3 As String, _
        Optional Tg4 As String, Optional Tg5 As String, Optional Tg6 As String)

On Error GoTo Err_Handler

    'set controls to locked or not according to the control tag value
     For Each ctrl In Screen.ActiveForm.Controls
        Select Case ctrl.ControlType
        
        Case acLabel, acCommandButton, acTabCtl, acPage, acImage, acLine, acRectangle, acPageBreak
            'no code here - these can't be locked
            
        Case Else
            If ctrl.Tag = Tg1 Or ctrl.Tag = Tg2 Or ctrl.Tag = Tg3 Or ctrl.Tag = Tg4 _
                    Or ctrl.Tag = Tg5 Or ctrl.Tag = Tg6 Then ctrl.Locked = State
                    
        End Select
            
    Next ctrl
  
Exit_Handler:
    Exit Sub

Err_Handler:
    MsgBox "Error " & Err.Number & " in LockControls procedure: " & Err.Description
    Resume Exit_Handler
    
End Sub

Now in your form add this code

Code:
Private Sub ApprovedHOD_AfterUpdate()

  If me.ApprovedHOD =True Then
     LockControls True, "X"
  Else
     LockControls False, "X"
  End if 

End Sub

To see this approach in action, see this example app https://www.access-programmers.co.uk/forums/showthread.php?t=293439

The approach can also be used to disable or hide a group of controls.
In your case, I would disable the controls rather than lock them so you can see a difference.

To do so replace LockControls with EnableControls throughout.
In the module code it would be ctrl.Enabled instead

Hope that makes sense
 

Users who are viewing this thread

Back
Top Bottom