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

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
mpimAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Dev-Soln LLCCommented:
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
    else
        me.AllowEdits = false
    end if

End sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PatHartmanCommented:
Dale's suggestion assumes that in your BeforeUpdate event for the form, you populate the date completed field

Me.txtDateCompleted = Now()
Mark EdwardsChief Technology OfficerCommented:
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....
mpimAuthor Commented:
Thank you so much. The suggested solution works perfectly. Cheers Michael
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.