Protect data in my form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Similar to a previous post but with a twist (no lemon)...

I want (nearly) all the fields on my form NOT to allow edits, if one of the
fields value is true.

Is there a better way to protect the data in my fields based on the users
selection of Protect data Y/N combo box? The trouble is the Protect Data
field is on the form! So I can't change it back once it has been selected.


My code is:

' ======== Protect Data code starts here =========

With Me
.AllowEdits = Not Nz((!ProtectData = "Yes"), False)
.AllowDeletions = Not Nz((!ProtectData = "Yes"), False)
End With
' ========= Protect Data code ends here ====================

The trouble with the code is that it protects the entire form, including the
field with the value of Yes/No for protecting the data. Such that, once you
select Yes, you can't change it back to No.

I thought wow that was easy ….... HA !

Previously I have had to include each individual field (enabled = False)
code for the on current event of the form, and also the on_lost_focus event
of the field in question.

Is there a better way to protect just about all the data in my fields based
on the users selection of Protect data Y/N combo box? Also, is there a
better option rather than enabled = False?

Many thanks for any advice.
 
If you want to allow some edits and not others, you will need to set the
Enabled (or Locked) property of the individual controls.

The code below loops through all the bound controls on a form, and sets
their Locked property. You can pass in the names of any controls you do NOT
want to lock as optional arguments. For example, if you do not want to lock
Text0 and Combo1, you would code:
Call LockBoundControls(Me, True, "Text0", "Combo1")

If the code finds a subform, it calls itself recursively to handle the
subform as well. If you don't want the subform locked, pass it's name in the
exception list.

Watch the line wrap.

-----------------------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 at " & Now()
End Select
Next

Exit_Handler:
Set ctl = Nothing
Exit Function

Err_Handler:
MsgBox "Error " & Err.Number & " - " &Err.Description
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-------------------------------
 
Many Thanks again Allen.
--
I may not know VBA inside out, but from the outside I am looking in.
Dylan Moran - Melbourne Australia


Allen Browne said:
If you want to allow some edits and not others, you will need to set the
Enabled (or Locked) property of the individual controls.

The code below loops through all the bound controls on a form, and sets
their Locked property. You can pass in the names of any controls you do NOT
want to lock as optional arguments. For example, if you do not want to lock
Text0 and Combo1, you would code:
Call LockBoundControls(Me, True, "Text0", "Combo1")

If the code finds a subform, it calls itself recursively to handle the
subform as well. If you don't want the subform locked, pass it's name in the
exception list.

Watch the line wrap.

-----------------------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 at " & Now()
End Select
Next

Exit_Handler:
Set ctl = Nothing
Exit Function

Err_Handler:
MsgBox "Error " & Err.Number & " - " &Err.Description
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-------------------------------
Click to expand...
 
Back
Top