How to lock specific rows in a subform when a form

Question

I have a subform containing records with a Yes/No field displayed as a checkbox on the subform. When the main form is loaded, I need to lock all checked fields so that the user cannot undo what a previous user did: they should only be able to check/uncheck the boxes that were unchecked at the start of thier session.

Is it possible to iterate through all the records in a subform and lock the records selectively based on the field value when a form is loaded?

I know I can put something like

If (Me.chkItemReceived.Value = -1) Then
        Me.chkItemReceived.Locked = True
Else
        Me.chkItemReceived.Locked = False
End If

in the subform OnCurrent event but this prevents the user from unchecking a box they may have checked accidently. I can iterate through the records on the subform but can't figure out how to lock the records at row-level.

My other solution is to split the form into two subforms of course - items checked vs. items not but would rather keep it to the one form.

Thanks for your help!

Answer 1

Well, you basically gave yourself the solution to your problem - if I am reading your question right. You can set it so when you iterate to the next record, certain controls are locked from editing based on the value of those or other controls. You are not actually locking the rows in the tables, of course, but you are preventing the user from changing the value of the locked controls.

If you mean to prevent other users from editing the record you just edited - then this would not solve your issue.

Answer 2

No, there is no way to have specific rows (or records) locked in the GUI in MS Access. If you change the properties of a control on one record, it changes it on all records in the subform.

I don't think you could even get away with doing row-level locking directly on the underlying table. Typically, locking is done to prevent others from editing your records. But in this case, you are trying to prevent yourself from editing them.

The two methods you describe (OnCurrent and separate SubForms) are probably your best options. For cleanliness of the code, I'd choose the latter.