Programatically lock Access 2003 records

[egaffney]

We are getting closer to the answer here. I used the code you posted and am still having the problem that if i open a record that is unlocked, lock it by checking the box, then try to exit the record or move to the next record it will tell me

"Before attempting changes please verify that the verified contents check box is unchecked."

So as a consequence the checkbox is being included in the items to be locked, controlled by itself. The checkbox won't attach to a specific record if i make it unbound. Is there any way that i can tag the checkbox so that it is omitted from the record lock code?

Also, something else that is interesting, when i click on the subform with the record locked i cannot change any of the records, they are frozen and i immediately get the "Before attempting changes please verify that the verified contents check box is unchecked." routine...

but on the main form i can modify the fields and it isn't until i get to leaving the record or clicking on a subform that i get the notification and the Me.Undo takes effect and reverts the entry to its prior entry...

The way the subform behaves is ideal at this point, it is just the main form that needs a good talking to...Thanks again for all of your help!

[Denburt]

Yes I think we are getting close try this and see if you find any issues, it seems to work fine on this end.

Main Form

Option Compare Database
Option Explicit
Dim ChckBool As Boolean

Private Sub Form_BeforeUpdate(Cancel As Integer)
If ChckBool = False And Me!ck = True Then
MsgBox "Before attempting changes please verify that the verified contents check box is unchecked.", vbCritical
Cancel = True
'You can also use:
'Me.Undo
Else
ChckBool = False
End If
End Sub

Private Sub Check54_AfterUpdate()
ChckBool = True
DoCmd.RunCommand acCmdSelectRecord
End Sub

[FishVal]

Yes I think we are getting close try this and see if you find any issues, it seems to work fine on this end.

Main Form

Option Compare Database
Option Explicit
Dim ChckBool As Boolean

Private Sub Form_BeforeUpdate(Cancel As Integer)
If ChckBool = False And Me!ck = True Then
MsgBox "Before attempting changes please verify that the verified contents check box is unchecked.", vbCritical
Cancel = True
'You can also use:
'Me.Undo
Else
ChckBool = False
End If
End Sub

Private Sub Check54_AfterUpdate()
ChckBool = True
DoCmd.RunCommand acCmdSelectRecord
End Sub

Why not so
Set in control properties Tag="Protected" for each control you want to lock/unlock.
Run the following sub on AfterUpdate event in locking CheckBox and Current event in the form if the CheckBox is bound to table field (and this should be so if you want to keep a record locked until it will be unlocked by user)

Private Sub chkLock_AfterUpdate()
    LockUnlock
End Sub

Private Sub Form_Current()
    LockUnlock
End Sub

Private Sub LockUnlock()

    On Error Resume Next
    For Each ctrl In Me.Controls
        If ctrl.Tag = "Protected" Then ctrl.Locked = Me.chkLock
    Next

End Sub

Simple and works. :)

PS. Obviously, if you want to lock controls in subform replace "Me.Control s" to "Me![Subform Name].Form.Controls"

[egaffney]

This combination works like magic:

[CODE=VB]
'Declaration

Option Compare Database
Option Explicit
Dim ChckBool As Boolean
_______________ _______________ ______
'The main form

Private Sub Form_BeforeUpda te(Cancel As Integer)
If ChckBool = False And Me![ContentsVerifie d] = True Then
MsgBox "Before attempting changes please verify that the verified contents check box is unchecked.", vbCritical
Cancel = True
Me.Undo
Else
ChckBool = False
End If
End Sub
_______________ _______________ ______
'The checkbox

Private Sub ContentsVerifie d_AfterUpdate()
ChckBool = True
DoCmd.RunComman d acCmdSelectReco rd
End Sub
_______________ _______________ ________
'Any Subforms
Private Sub Form_BeforeUpda te(Cancel As Integer)
Dim rs As Recordset
If Me.Parent![ContentsVerifie d] = True Then
MsgBox "Before attempting changes please verify that the verified contents check box is unchecked.", vbCritical
Cancel = True
'You can also use:
Me.Undo
Else
'An optional and temporary reminder to everyone to lock the record after editing the subform
MsgBox "Remember to lock the record when you have finished editing it.", vbOKOnly
End If
End Sub
[/CODE]

Thanks ever so much Denburt, this bit of code did the trick. I will test it out over the next day or two and post back any news that I have. Once again thank you for your time, this is one useful feature that many databases could benefit from. I would also like to thank everyone else who posted a response to my question and got me thinking. Talk to you soon!

[egaffney]

Why not so
Set in control properties Tag="Protected" for each control you want to lock/unlock.
Run the following sub on AfterUpdate event in locking CheckBox and Current event in the form if the CheckBox is bound to table field (and this should be so if you want to keep a record locked until it will be unlocked by user)

Private Sub chkLock_AfterUpdate()
    LockUnlock
End Sub

Private Sub Form_Current()
    LockUnlock
End Sub

Private Sub LockUnlock()

    On Error Resume Next
    For Each ctrl In Me.Controls
        If ctrl.Tag = "Protected" Then ctrl.Locked = Me.chkLock
    Next

End Sub

Simple and works. :)

PS. Obviously, if you want to lock controls in subform replace "Me.Control s" to "Me![Subform Name].Form.Controls"

I will try this piece of code out as well. Thanks for the idea, and I'll let you know if any wacky stuff happens.

[Denburt]

This combination works like magic:

'Declaration

Option Compare Database
Option Explicit
Dim ChckBool As Boolean
_______________ _______________ ______
'The main form

Private Sub Form_BeforeUpda te(Cancel As Integer)
If ChckBool = False And Me![ContentsVerifie d] = True Then
MsgBox "Before attempting changes please verify that the verified contents check box is unchecked.", vbCritical
Cancel = True
Me.Undo
Else
ChckBool = False
End If
End Sub
_______________ _______________ ______
'The checkbox

Private Sub ContentsVerifie d_AfterUpdate()
ChckBool = True
DoCmd.RunComman d acCmdSelectReco rd
End Sub
_______________ _______________ ________
'Any Subforms
Private Sub Form_BeforeUpda te(Cancel As Integer)
Dim rs As Recordset
If Me.Parent![ContentsVerifie d] = True Then
MsgBox "Before attempting changes please verify that the verified contents check box is unchecked.", vbCritical
Cancel = True
'You can also use:
Me.Undo
Else
'An optional and temporary reminder to everyone to lock the record after editing the subform
MsgBox "Remember to lock the record when you have finished editing it.", vbOKOnly
End If
End Sub

Thanks ever so much Denburt, this bit of code did the trick. I will test it out over the next day or two and post back any news that I have. Once again thank you for your time, this is one useful feature that many databases could benefit from. I would also like to thank everyone else who posted a response to my question and got me thinking. Talk to you soon!

Great glad its working let us know how it turns out.

Fish your method may be a viable alternative and I am glad you posted it I am sure that it can come in handy.

[egaffney]

I have tested both codes and here is what I found.

Denburt’s code works well and can be used as a blanket record lock where all records in a form or subform have a lock applied to them.

Fish’s code also works well and can be used when you want to lock some, but not all controls in a record.

Two distinct flavours of programmatic record locking, I love it! Neither chunks of code interfere with my On Load or On Unload events either. I will likely find a use for both techniques in my current and future applications. Thanks again to all who helped out.

Evan