The code below lets you lock/unlock all the bound controls on a form and its
subforms, except for those named in the exception list.
To call it to lock all controls on your form except Combo1 and TextBox2, set
the On Click property of your command button to:
=LockBoundControls([Form], True, "Combo1", "TextBox2")
To unlock, use False instead of True.
The code loops through all the controls on the form, and ignores:
- anything named in the exception list,
- anything except combo, listbox, option group, check box, option button,
toggle button,
- anything that has no Control Source (e.g. button in option group),
- anything unbound (nothing in Control Source),
- anything bound to an expression (Control Source starts with "=").
For the others, it toggles the Locked property.
If there are any subforms, the code calls itself to handle them also. If you
do not want the subform locked, put its name in the exeption list. The
recursive call means all nested subforms (sub-subforms, sub-sub-forms, etc)
are handled also.
In case anyone is wondering why you would want to do this instead of just
setting the AllowEdits of the form, that approach does not give you any
flexibility to leave certain controls unlocked, not even unbound controls.
----------------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 = Trur 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 " & frm.Name & " 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
Public Function HasProperty(obj As Object, strPropName As String) As Boolean
'Purpose: Return true if the object has the property.
Dim vardummy As Variant
On Error Resume Next
vardummy = obj.Properties(strPropName)
HasProperty = (Err.Number = 0)
End Function
----------------code ends-------------------
Use your own error handler, or copy the one from:
http://members.iinet.net.au/~allenbrowne/ser-23a.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
Stephen said:
I want to create an option button that locks/unlocks all fields on a form
except itself.
I started with :
If Forms![Purchase Orders].[LockRecords] = -1 Then
Me.ProductID.Locked = True
Me.PricePerLB.Locked = True
Me.CtnPrice.Locked = True
Me.UnitsReceived.Locked = True
Me.TransactionDate.Locked = True
Me.CtnsReceived.Locked = True
Else
Me.ProductID.Locked = False
Me.PricePerLB.Locked = False
Me.CtnPrice.Locked = False
Me.UnitsReceived.Locked = False
Me.TransactionDate.Locked = False
Me.CtnsReceived.Locked = False
End If
End Sub
...but as I add/ change fields this has become cumbersome.
Is there a way to Lock all fields except one based on a combo box value?
Thanks in advance.
