Is it possible to lock or disable the current row of a datasheet in a split form?

Michael Paravicini
Michael Paravicini used Ask the Experts™
Hello, is it possible to lock or disable the current row of a datasheet in a split form? Once the user has made any changes to the current row I would like to freeze it and not allow any further changes. However, he should be allowed to change any other row of the same datasheet? Not sure this works but thanks for any help. Rg Michael
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
To prevent a user from editing a particular record, you must have some form of flag on the record that you can test during the Form_Current event.  This could be a Y/N field that you set upon completion of editing the current record, or it might be something else, like a value in another field, I use [DateCompleted] in one of my clients applications.

Then, in the Form_Current event you might do something like:

Private Sub Form_Current

    if  me.txtDateCompleted & "" = "" Then
        me.AllowEdits = true
        me.AllowEdits = false
    end if

End sub

Open in new window

Distinguished Expert 2017

Dale's suggestion assumes that in your BeforeUpdate event for the form, you populate the date completed field

Me.txtDateCompleted = Now()
Mark EdwardsChief Technology Officer

As previously discussed, you now have a way to identify when/if a row is changed.  
The next train of thought is "what is the logic for keeping the row locked after moving off the changed record"?  Is it forever, or just during the time that the form is open.  If the user closes the form and reopens it, can they re-edit the same record, or should it be locked?

Usually, a split form uses editing controls in the form-part of the form to edit data for the selected record, and the datasheet part is used to view/select the record that is to be edited in the form-part.  Once a record is determined to need to be locked - by whatever method you want to make that determination - the next thought is how to "lock the controls that display the data so the data can't be edited".

One method is to toggle the "Locked" property of the controls to "True" if the row needs to be locked, and set the "Locked" property to "False" if it can be edited.  This code should run in the form's Current event, which is where you put code that needs to run based on when a row is selected.  You would need to do the same for any controls in the form-part of the split form,  
It should be noted that in a datasheet, the only row that is in play is the selected row - none of the other rows matter until they are selected.

I'm sure there are other ways, but that's how I would do it....


Thank you so much. The suggested solution works perfectly. Cheers Michael

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial