So you want to add a yes/no field to the table, and check this box to
prevent changes to the record. The user can still uncheck this yes/no field
to edit the record, but cannot edit anything else while the box is checked.
This involves code in the AfterUpdate event of the the check box, and also
in the Current event of the form. You cannot set the AllowEdits property of
the form (since that would prevent you unchecking the box), so you need to
loop through the controls and set their Locked property. Presumably you want
to prevent the deletion of the record if the record is locked as well.
1. Copy the code below into a standard module, and save.
2. Set the AfterUpdate propery of your lockbox check box to:
[Event Procedure]
Click the Build button, and add this line to the code:
Private Sub lockbox_AfterUpdate
Call LockBoundControls(Me, Me.lockbox.Value, "lockbox")
End Sub
3. Set the On Current property of the form to
[Event Procedure]
Click the Build button, and add this line to the code:
Private Sub Form_Current
Call lockbox_AfterUpdate
End Sub
The interesting aspects of the code are:
- Unbound controls are not locked.
- Add the names of controls you don't want to lock (such as your "lockbox"
check box).
- The code calls itself recursively to lock any subforms as well. If you do
not want to lock a subform, include the name of the subform control in the
exception list.
--------------code starts-------------------------
Public Function LockBoundControls(frm As Form, bLock As Boolean, _
ParamArray avarExceptionList())
On Error GoTo Err_Handler
'Purpose: Lock the bound controls and prevent deletes on the form any
its subforms.
'Arguments frm = the form to be locked
' bLock = True to lock, False to unlock.
' avarExceptionList: Names of the controls NOT to lock
(variant array of strings).
'Usage: Call LockBoundControls(Me. True)
Dim ctl As Control 'Each control on the form
Dim lngI As Long 'Loop controller.
Dim bSkip As Boolean
'Save any edits.
If frm.Dirty Then
frm.Dirty = False
End If
'Block deletions.
frm.AllowDeletions = Not bLock
For Each ctl In frm.Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox,
acOptionButton, acToggleButton
'Lock/unlock these controls if bound to fields.
bSkip = False
For lngI = LBound(avarExceptionList) To
UBound(avarExceptionList)
If avarExceptionList(lngI) = ctl.Name Then
bSkip = True
Exit For
End If
Next
If Not bSkip Then
If HasProperty(ctl, "ControlSource") Then
If Len(ctl.ControlSource) > 0& And Not ctl.ControlSource
Like "=*" Then
If ctl.Locked <> bLock Then
ctl.Locked = bLock
End If
End If
End If
End If
Case acSubform
'Recursive call to handle all subforms.
bSkip = False
For lngI = LBound(avarExceptionList) To
UBound(avarExceptionList)
If avarExceptionList(lngI) = ctl.Name Then
bSkip = True
Exit For
End If
Next
If Not bSkip Then
If Len(Nz(ctl.SourceObject, vbNullString)) > 0& Then
ctl.Form.AllowDeletions = Not bLock
ctl.Form.AllowAdditions = Not bLock
Call LockBoundControls(ctl.Form, bLock)
End If
End If
Case acLabel, acLine, acRectangle, acCommandButton, acTabCtl,
acPage, acPageBreak, acImage, acObjectFrame
'Do nothing
Case Else
'Includes acBoundObjectFrame, acCustomControl
Debug.Print ctl.Name & " not handled on " & conMod & " at " &
Now()
End Select
Next
Exit_Handler:
Set ctl = Nothing
Exit Function
Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description,,
"LockBoundControls"
Resume Exit_Handler
End Function
--------------code ends-------------------------
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
tvh said:
Is there a way to add a checkbox to a form that locks the individual
record?
After an invoice has been submitted for a particular Work Order, there is
no
need for us to alter the data. But, if for some reason we had to go back
and
change a record, it would be nice to be able to uncheck a box, change the
data, then reselect the "lockbox".
Thanks
