Version 1.2         Approx 0.9 MB (zipped)             First Published 11 Sept 2024


This article was prompted by an email from German developer, Marcus Dieterle:
I am facing the challenge of locking certain rows in a Continuous Form so that they can no longer be edited. The lock should be activated when a checkbox is set to 'True'. Currently I am using the BeforeUpdate event, which works, but is not optimal.

One problem with using the BeforeUpdate event to validate changes is that users may think they can make changes, only for the system to reverse those changes on leaving the record.

Altering the properties of individual records in continuous forms isn’t easy as the form uses the same controls repeated for each record. As a result, changes made by ticking a checkbox on one record normally affects all records.

However, the desired result is easy to achieve with very little code

The example app has 4 forms with minor variations in the code used:


To do this, add a boolean field (Lock) and (optionally) a date field LockDate to the form record source and the assocated controls to the form:


Then add the following code:

CODE: (Form 1)

Private Sub LockUnlockControls()

Dim ctl As Control

    For Each ctl In Me.Controls
        If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
            ctl.Locked = Me.Lock
        End If
    Next ctl

End Sub

'===========================================

Private Sub Lock_AfterUpdate()
    Call LockUnlockControls
End Sub

'===========================================

Private Sub Form_Current()
    Call LockUnlockControls
End Sub

The Form_Current event triggers for each selected record and allows each textbox and combo box to be locked / unlocked depending on the value of the checkbox

The Lock_AfterUpdate event ensure the code applies immediately to the current record. You may wish to adapt this if your form has additional checkboxes for other fields. If so, change the If ctl.Type . . . line in LockUnlockControls:

CODE: (Form 2)

Private Sub LockUnlockControls()

Dim ctl As Control

    For Each ctl In Me.Controls
        If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Or ctl.ControlType = acCheckBox Then
            ctl.Locked = Me.Lock
        End If
    Next ctl

End Sub

The above code will now also lock ALL checkboxes when checkbox Lock is ticked. This also means that users will not be able to untick any checked instances of the Lock checkbox. This may be seen as desirable in some cases but it also means that any mistakes cannot be reversed.

A further minor modification will allow you to make an exception of the Lock checkbox:

CODE: (Form 3)

Private Sub LockUnlockControls()

Dim ctl As Control

    For Each ctl In Me.Controls
        If Not ctl.Name = "Lock" Then
            If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Or ctl.ControlType = acCheckBox Then
                ctl.Locked = Me.Lock
            End If
        End If
    Next ctl

End Sub

There may be certain fields that should ALWAYS be locked. In the example forms, the primary key field PupilID and the LockDate field are ALWAYS locked.
This requires 2 extra lines of code:

Private Sub LockUnlockControls()

Dim ctl As Control

    For Each ctl In Me.Controls
        If Not ctl.Name = "Lock" Then
            If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Or ctl.ControlType = acCheckBox Then
                ctl.Locked = Me.Lock
            End If
        End If
    Next ctl

    Me.PupilID.Locked=True
    Me.LockDate.Locked=True

End Sub

As the appearance of records does not change when locked, you could also consider adding a lock image next to each locked record.
For this to work successfully, you will need to overlay the image with a command button and use its transparency property in the Detail_Paint event.

CODE: (Form 4)

Private Sub Detail_Paint()
    'Button cmdLock is transparent only when chkLock is ticked, so showing imgLock below it.
    'Otherwise the button has the same color as the detail section

    cmdLock.Transparent = Me.Lock End Sub

For full details of this approach, see my article: Paint Me Transparent in a Continuous Form



Video

To see this in action, please watch the accompanying video (8:41) on my YouTube channel: Lock Selected Records in a Continuous Form or you can click below:

       

If you liked the video, please subscribe to my Isladogs on Access channel on YouTube. You will then be notified whenever new videos are released.



Click to download:

        Lock Records Continuous Form v1.2    ACCDB file - Approx 0.9 MB     (zipped)



Feedback

Please use the contact form below to let me know whether you found this article interesting/useful or if you have any questions/comments.

Please also consider making a donation towards the costs of maintaining this website. Thank you



Colin Riddington         Mendip Data Systems             Last Updated 11 Sept 2024

Return to Example Databases Page

Return to Top