office交流網--QQ交流群號

Access培訓群:792054000         Excel免費交流群群:686050929          Outlook交流群:221378704    

Word交流群:218156588             PPT交流群:324131555

access設置綁定控件不可以修改內容,非綁定控件可修改

2019-09-21 17:17:00
tmtony8
原創
4798

在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)



當按鈕爲鎖定時,紅色框內的綁定控件內容均不能修改,如果未鎖定,卽均可修改。無論是否鎖定,上麵的未綁定文本框控件都可以修改查詢內容。

    分享