To lock the subforms as well, the code needs to call itself recursively when
it finds a control of type acSubform.
The function below does that.
It takes 2 parameters:
- a reference to the to be locked or unlocked;
- True to lock it, or False to unlock it.
Any further parameters you pass are understood to be the names of controls
that are NOT to be locked/unlocked. For example, if you there was one
subform you did not want to lock, you could pass its name and it would not
be changed. (Unbound controls are not locked/unblocked in any case. We tend
to use these for filtering the form, and we want the user to be able to do
that even when the form is "locked".)
-----------------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 in LockBoundControls() 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
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 starts-------------------------------
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"@ndy" <an*******@hotmail.com> wrote in message
news:6c**************************@posting.google.c om...
Hi developers,
i've a problem with my tabcontrol.
Before i open my form with a tabcontrol with 3 tabs
(Frm_Files|Frm_Lines|Frm_Guarantee) all the controls on al the tabs
must be enabled. With the next code only the controls on the active
form are enabled.
For Each ctl In Me.Controls
Select Case ctl.ControlType
' Ignore a few that won't be changed
Case acLabel, acPageBreak, acPage, acCommandButton, acTabCtl,
acOptionGroup
Debug.Print ctl.Name & " ignored"
Case Else
Debug.Print ctl.Name & " processed"
ctl.Enabled = False
End Select
Next
How can i enable the controls on the tabs (subforms)?
