Set the AllowEdits and AllowDeletions property of the form.
If you have a yes/no field to indicate whether they are locked or not, do
that in the Current event of the form.
If you need the user to be able to use some unbound controls, or to edit
certain controls only, you will need to toggle the Locked property of each
control instead of setting the form's properties.
Here's an example function you can place into a standard module, and then
call it to toggle the locked property of all bound controls, except those in
the exception list. For example, you could lock all bound controls in the
current form by putting this into the Click event procedure of a command
button:
=LockBoundControls(Me, True)
The funtion loops through the controls on the form. If it finds a subform,
it calls itself recursively, so that all levels of subforms are handled. If
you don't want the subform locked also, include its name in the exception
list. For example to lock everything except Combo1 and Sub2:
=LockBoundControls(Me, True, "Combo1", "Sub2")
Option Compare Database
Option Explicit
Private Const conMod = "ajbLockBound"
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:
Call LogError(Err.Number, Err.Description, conMod & "LockBoundControls")
Resume Exit_Handler
End Function
