access设置绑定控件不可以修改内容,非绑定控件可修改

2019-09-21 17:17:00
tmtony8
原创
365

在access数据绑定的窗体的中,由于窗体数据是直接绑定到记录源的,不需要通过按钮或者其他方式即可修改添加数据

这样非常容易意外操作数据,导致数据出错。

窗体的AllowEdits属性设置为FALSE可以防止这种情况出现,但是这样锁定会把所有控件都设置为不可修改了。

下面示例可以限制窗体的控件内容是否可以直接修改。如果是未绑定控件,可以修改。如果是绑定控件即不可以修改


详细代码如下:

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 " & Now()
        End Select
    Next
    
'    Set the visual indicators on the form.
    On Error Resume Next
    frm.cmdLock.Caption = IIf(bLock, "Un&lock", "&Lock")
    frm!rctLock.Visible = bLock
    
    
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


按钮调用代码:

     Dim bLock As Boolean
     bLock = IIf(Me.cmdLock.Caption = "&Lock", True, False)
     Call LockBoundControls(Me, bLock)



当按钮为锁定时,红色框内的绑定控件内容均不能修改,如果未锁定,即均可修改。无论是否锁定,上面的未绑定文本框控件都可以修改查询内容。

    分享