Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Lock 1 record (LockBox)

Discussion in 'Information Technology' started by lmv, Jul 28, 2006.

  1. lmv

    lmv
    Expand Collapse
    Guest

    I have been trying to use the attached code. The ckbox is not working right,
    sometimes it locks but then it will not unlock unless I close and then
    reopen. If anyone has successfully used this code can you tell me if the
    events I have the code in look right. Also, does my call to autofill a new
    record belong in a different order. my check box ctrl name is lockbox, and
    the field on my form is called "locked" I don't know if I need to rename my
    field or my ctrl I am completely stumped. (I had already tried some easier
    solutions I had read about but because of some of the code for calendars etc
    they didn't work) I will try any suggestions Thanks!
    -----------
    Private Sub lockbox_AfterUpdate()
    Call LockBoundControls(Me, Me.lockbox.Value, "lockbox")
    End Sub
    -----------------
    Private Sub Form_Current()
    Call LockBoundControls(Me, True)
    'Opens new records with fields filled from last entry
    Call AutoFillNewRecord(Forms!OrdersWDetails)
    Call lockbox_AfterUpdate

    End Sub
    -----------------------
    Private Sub lockbox_Click()
    Call LockBoundControls(Me, True)

    'if you want to lock the controls, or:
    'Call LockBoundControls(Me, False)
    'if you want to unlock them

    'If there are controls you *don't* want to lock, add their names as
    extraarguments, e.g.:
    'Call LockBoundControls(Me, True, "Surname", "City")
    End Sub
    ---------------------------
    MODULE:
    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
     
  2. Loading...

    Similar Threads Forum Date
    Debate With Sarbjeet Singh Dhunda At Turlock (California) Gurdwara, April 2015 - YouTube Gurmat Vichaar Apr 23, 2016
    Uk clocks go back Blogs Oct 17, 2015
    Kesh: Dreadlocks Questions and Answers Nov 20, 2013
    Obamacare Faces Near-Solid Block In The South Health & Nutrition Mar 31, 2013
    Malaysia Have you forsaken your flock, Archbishop? Malaysia News Update Breaking News Jan 30, 2013

  3. Wayne Morgan

    Wayne Morgan
    Expand Collapse
    Guest

    The first problem I see is that you are using both the AfterUpdate and Click
    event of the checkbox to do basically the same thing. The difference is that
    the AfterUpdate takes into account the value of the checkbox, the Click
    event doesn't. The Click event will always try to lock the record. Both will
    fire when you click the checkbox. I would recommend getting rid of the Click
    event and staying with the AfterUpdate event.

    The next possible problem is that the form's Current event is set to always
    lock the record initially, then it appears that you open another form, then
    set the lock according to the checkbox value by calling the checkbox's
    AfterUpdate event. Is all of this necessary?

    --
    Wayne Morgan
    MS Access MVP


    "lmv" <lmv@discussions.microsoft.com> wrote in message
    news:7C67D49F-12D3-4D19-84C3-1D11FEC03931@microsoft.com...
    >I have been trying to use the attached code. The ckbox is not working
    >right,
    > sometimes it locks but then it will not unlock unless I close and then
    > reopen. If anyone has successfully used this code can you tell me if the
    > events I have the code in look right. Also, does my call to autofill a new
    > record belong in a different order. my check box ctrl name is lockbox, and
    > the field on my form is called "locked" I don't know if I need to rename
    > my
    > field or my ctrl I am completely stumped. (I had already tried some easier
    > solutions I had read about but because of some of the code for calendars
    > etc
    > they didn't work) I will try any suggestions Thanks!
    > -----------
    > Private Sub lockbox_AfterUpdate()
    > Call LockBoundControls(Me, Me.lockbox.Value, "lockbox")
    > End Sub
    > -----------------
    > Private Sub Form_Current()
    > Call LockBoundControls(Me, True)
    > 'Opens new records with fields filled from last entry
    > Call AutoFillNewRecord(Forms!OrdersWDetails)
    > Call lockbox_AfterUpdate
    >
    > End Sub
    > -----------------------
    > Private Sub lockbox_Click()
    > Call LockBoundControls(Me, True)
    >
    > 'if you want to lock the controls, or:
    > 'Call LockBoundControls(Me, False)
    > 'if you want to unlock them
    >
    > 'If there are controls you *don't* want to lock, add their names as
    > extraarguments, e.g.:
    > 'Call LockBoundControls(Me, True, "Surname", "City")
    > End Sub
    > ---------------------------
    > MODULE:
    > 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
    >
    >
     
  4. lmv

    lmv
    Expand Collapse
    Guest

    YOU are a genius! (At least in MHO) The first part was the problem... I
    didn't understand they were doing the same thing!

    As far as the last part...

    Private Sub Form_Current()
    'Call LockBoundControls(Me, True)
    'Opens new records with fields filled from last entry
    Call AutoFillNewRecord(Forms!OrdersWDetails)
    'Call lockbox_AfterUpdate

    I don't know if it is necessary I was doing what the instructions said to do.

    When I just tried it getting rid of the click event it worked and I had both
    lines in the Current commented out, and it STILL WORKED. Then I tried it with
    the Call lockbox_afterUpdate and it still worked. So, I don't know why I need
    either of those lines at all...?? If you see any reason why I need them
    please let me know. Would you mind telling me how to have a msg box pop up if
    someone tries to change something when the record is locked and how to change
    the background of the ckbox label when it is locked to red. (Conditional
    formatting is grayed out)

    Thank you so much!


    "Wayne Morgan" wrote:

    > The first problem I see is that you are using both the AfterUpdate and Click
    > event of the checkbox to do basically the same thing. The difference is that
    > the AfterUpdate takes into account the value of the checkbox, the Click
    > event doesn't. The Click event will always try to lock the record. Both will
    > fire when you click the checkbox. I would recommend getting rid of the Click
    > event and staying with the AfterUpdate event.
    >
    > The next possible problem is that the form's Current event is set to always
    > lock the record initially, then it appears that you open another form, then
    > set the lock according to the checkbox value by calling the checkbox's
    > AfterUpdate event. Is all of this necessary?
    >
    > --
    > Wayne Morgan
    > MS Access MVP
    >
    >
    > "lmv" <lmv@discussions.microsoft.com> wrote in message
    > news:7C67D49F-12D3-4D19-84C3-1D11FEC03931@microsoft.com...
    > >I have been trying to use the attached code. The ckbox is not working
    > >right,
    > > sometimes it locks but then it will not unlock unless I close and then
    > > reopen. If anyone has successfully used this code can you tell me if the
    > > events I have the code in look right. Also, does my call to autofill a new
    > > record belong in a different order. my check box ctrl name is lockbox, and
    > > the field on my form is called "locked" I don't know if I need to rename
    > > my
    > > field or my ctrl I am completely stumped. (I had already tried some easier
    > > solutions I had read about but because of some of the code for calendars
    > > etc
    > > they didn't work) I will try any suggestions Thanks!
    > > -----------
    > > Private Sub lockbox_AfterUpdate()
    > > Call LockBoundControls(Me, Me.lockbox.Value, "lockbox")
    > > End Sub
    > > -----------------
    > > Private Sub Form_Current()
    > > Call LockBoundControls(Me, True)
    > > 'Opens new records with fields filled from last entry
    > > Call AutoFillNewRecord(Forms!OrdersWDetails)
    > > Call lockbox_AfterUpdate
    > >
    > > End Sub
    > > -----------------------
    > > Private Sub lockbox_Click()
    > > Call LockBoundControls(Me, True)
    > >
    > > 'if you want to lock the controls, or:
    > > 'Call LockBoundControls(Me, False)
    > > 'if you want to unlock them
    > >
    > > 'If there are controls you *don't* want to lock, add their names as
    > > extraarguments, e.g.:
    > > 'Call LockBoundControls(Me, True, "Surname", "City")
    > > End Sub
    > > ---------------------------
    > > MODULE:
    > > 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
    > >
    > >

    >
    >
    >
     
  5. Wayne Morgan

    Wayne Morgan
    Expand Collapse
    Guest

    You probably need the "Call lockbox_AfterUpdate" line. The form's Current
    event runs whenever you move to a different record. So, the line will check
    the value of the checkbox and set everything properly for that record. The
    checkbox's AfterUpdate event will set everything if you change the value of
    the checkbox.

    On changing the background of the checkbox to red, the checkbox doesn't have
    a BackColor property. You can change the BorderColor if you want to. To do
    so, in the checkbox's AfterUpdate event add lines to change the border
    color.

    Private Sub lockbox_AfterUpdate()
    Call LockBoundControls(Me, Me.lockbox.Value, "lockbox")
    If Me.Lockbox Then
    Me.Lockbox.BorderColor = vbRed
    Else
    Me.Lockbox.BorderColor = vbBlack
    End If
    End Sub

    However, I don't show that this actually does anything unless you change the
    checkbox's SpecialEffect property to Shadowed. I even tried increasing the
    width of the border to make sure it was wide enough to see. That didn't
    appear to do anything either.

    If you want to, you could place a box control around the textbox to create
    your own border and change the color of that. The code would be as above,
    except the BorderColor lines would use the name of the box instead of the
    name of the checkbox.

    --
    Wayne Morgan
    MS Access MVP


    "lmv" <lmv@discussions.microsoft.com> wrote in message
    news:67A81086-A6EB-40AA-8CCB-2645B1C57CA8@microsoft.com...
    > YOU are a genius! (At least in MHO) The first part was the problem... I
    > didn't understand they were doing the same thing!
    >
    > As far as the last part...
    >
    > Private Sub Form_Current()
    > 'Call LockBoundControls(Me, True)
    > 'Opens new records with fields filled from last entry
    > Call AutoFillNewRecord(Forms!OrdersWDetails)
    > 'Call lockbox_AfterUpdate
    >
    > I don't know if it is necessary I was doing what the instructions said to
    > do.
    >
    > When I just tried it getting rid of the click event it worked and I had
    > both
    > lines in the Current commented out, and it STILL WORKED. Then I tried it
    > with
    > the Call lockbox_afterUpdate and it still worked. So, I don't know why I
    > need
    > either of those lines at all...?? If you see any reason why I need them
    > please let me know. Would you mind telling me how to have a msg box pop up
    > if
    > someone tries to change something when the record is locked and how to
    > change
    > the background of the ckbox label when it is locked to red. (Conditional
    > formatting is grayed out)
    >
    > Thank you so much!
    >
    >
    > "Wayne Morgan" wrote:
    >
    >> The first problem I see is that you are using both the AfterUpdate and
    >> Click
    >> event of the checkbox to do basically the same thing. The difference is
    >> that
    >> the AfterUpdate takes into account the value of the checkbox, the Click
    >> event doesn't. The Click event will always try to lock the record. Both
    >> will
    >> fire when you click the checkbox. I would recommend getting rid of the
    >> Click
    >> event and staying with the AfterUpdate event.
    >>
    >> The next possible problem is that the form's Current event is set to
    >> always
    >> lock the record initially, then it appears that you open another form,
    >> then
    >> set the lock according to the checkbox value by calling the checkbox's
    >> AfterUpdate event. Is all of this necessary?
    >>
    >> --
    >> Wayne Morgan
    >> MS Access MVP
    >>
    >>
    >> "lmv" <lmv@discussions.microsoft.com> wrote in message
    >> news:7C67D49F-12D3-4D19-84C3-1D11FEC03931@microsoft.com...
    >> >I have been trying to use the attached code. The ckbox is not working
    >> >right,
    >> > sometimes it locks but then it will not unlock unless I close and then
    >> > reopen. If anyone has successfully used this code can you tell me if
    >> > the
    >> > events I have the code in look right. Also, does my call to autofill a
    >> > new
    >> > record belong in a different order. my check box ctrl name is lockbox,
    >> > and
    >> > the field on my form is called "locked" I don't know if I need to
    >> > rename
    >> > my
    >> > field or my ctrl I am completely stumped. (I had already tried some
    >> > easier
    >> > solutions I had read about but because of some of the code for
    >> > calendars
    >> > etc
    >> > they didn't work) I will try any suggestions Thanks!
    >> > -----------
    >> > Private Sub lockbox_AfterUpdate()
    >> > Call LockBoundControls(Me, Me.lockbox.Value, "lockbox")
    >> > End Sub
    >> > -----------------
    >> > Private Sub Form_Current()
    >> > Call LockBoundControls(Me, True)
    >> > 'Opens new records with fields filled from last entry
    >> > Call AutoFillNewRecord(Forms!OrdersWDetails)
    >> > Call lockbox_AfterUpdate
    >> >
    >> > End Sub
    >> > -----------------------
    >> > Private Sub lockbox_Click()
    >> > Call LockBoundControls(Me, True)
    >> >
    >> > 'if you want to lock the controls, or:
    >> > 'Call LockBoundControls(Me, False)
    >> > 'if you want to unlock them
    >> >
    >> > 'If there are controls you *don't* want to lock, add their names as
    >> > extraarguments, e.g.:
    >> > 'Call LockBoundControls(Me, True, "Surname", "City")
    >> > End Sub
    >> > ---------------------------
    >> > MODULE:
    >> > 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
    >> >
    >> >

    >>
    >>
    >>
     
  6. lmv

    lmv
    Expand Collapse
    Guest

    Thanks again!

    "Wayne Morgan" wrote:

    > You probably need the "Call lockbox_AfterUpdate" line. The form's Current
    > event runs whenever you move to a different record. So, the line will check
    > the value of the checkbox and set everything properly for that record. The
    > checkbox's AfterUpdate event will set everything if you change the value of
    > the checkbox.
    >
    > On changing the background of the checkbox to red, the checkbox doesn't have
    > a BackColor property. You can change the BorderColor if you want to. To do
    > so, in the checkbox's AfterUpdate event add lines to change the border
    > color.
    >
    > Private Sub lockbox_AfterUpdate()
    > Call LockBoundControls(Me, Me.lockbox.Value, "lockbox")
    > If Me.Lockbox Then
    > Me.Lockbox.BorderColor = vbRed
    > Else
    > Me.Lockbox.BorderColor = vbBlack
    > End If
    > End Sub
    >
    > However, I don't show that this actually does anything unless you change the
    > checkbox's SpecialEffect property to Shadowed. I even tried increasing the
    > width of the border to make sure it was wide enough to see. That didn't
    > appear to do anything either.
    >
    > If you want to, you could place a box control around the textbox to create
    > your own border and change the color of that. The code would be as above,
    > except the BorderColor lines would use the name of the box instead of the
    > name of the checkbox.
    >
    > --
    > Wayne Morgan
    > MS Access MVP
    >
    >
    > "lmv" <lmv@discussions.microsoft.com> wrote in message
    > news:67A81086-A6EB-40AA-8CCB-2645B1C57CA8@microsoft.com...
    > > YOU are a genius! (At least in MHO) The first part was the problem... I
    > > didn't understand they were doing the same thing!
    > >
    > > As far as the last part...
    > >
    > > Private Sub Form_Current()
    > > 'Call LockBoundControls(Me, True)
    > > 'Opens new records with fields filled from last entry
    > > Call AutoFillNewRecord(Forms!OrdersWDetails)
    > > 'Call lockbox_AfterUpdate
    > >
    > > I don't know if it is necessary I was doing what the instructions said to
    > > do.
    > >
    > > When I just tried it getting rid of the click event it worked and I had
    > > both
    > > lines in the Current commented out, and it STILL WORKED. Then I tried it
    > > with
    > > the Call lockbox_afterUpdate and it still worked. So, I don't know why I
    > > need
    > > either of those lines at all...?? If you see any reason why I need them
    > > please let me know. Would you mind telling me how to have a msg box pop up
    > > if
    > > someone tries to change something when the record is locked and how to
    > > change
    > > the background of the ckbox label when it is locked to red. (Conditional
    > > formatting is grayed out)
    > >
    > > Thank you so much!
    > >
    > >
    > > "Wayne Morgan" wrote:
    > >
    > >> The first problem I see is that you are using both the AfterUpdate and
    > >> Click
    > >> event of the checkbox to do basically the same thing. The difference is
    > >> that
    > >> the AfterUpdate takes into account the value of the checkbox, the Click
    > >> event doesn't. The Click event will always try to lock the record. Both
    > >> will
    > >> fire when you click the checkbox. I would recommend getting rid of the
    > >> Click
    > >> event and staying with the AfterUpdate event.
    > >>
    > >> The next possible problem is that the form's Current event is set to
    > >> always
    > >> lock the record initially, then it appears that you open another form,
    > >> then
    > >> set the lock according to the checkbox value by calling the checkbox's
    > >> AfterUpdate event. Is all of this necessary?
    > >>
    > >> --
    > >> Wayne Morgan
    > >> MS Access MVP
    > >>
    > >>
    > >> "lmv" <lmv@discussions.microsoft.com> wrote in message
    > >> news:7C67D49F-12D3-4D19-84C3-1D11FEC03931@microsoft.com...
    > >> >I have been trying to use the attached code. The ckbox is not working
    > >> >right,
    > >> > sometimes it locks but then it will not unlock unless I close and then
    > >> > reopen. If anyone has successfully used this code can you tell me if
    > >> > the
    > >> > events I have the code in look right. Also, does my call to autofill a
    > >> > new
    > >> > record belong in a different order. my check box ctrl name is lockbox,
    > >> > and
    > >> > the field on my form is called "locked" I don't know if I need to
    > >> > rename
    > >> > my
    > >> > field or my ctrl I am completely stumped. (I had already tried some
    > >> > easier
    > >> > solutions I had read about but because of some of the code for
    > >> > calendars
    > >> > etc
    > >> > they didn't work) I will try any suggestions Thanks!
    > >> > -----------
    > >> > Private Sub lockbox_AfterUpdate()
    > >> > Call LockBoundControls(Me, Me.lockbox.Value, "lockbox")
    > >> > End Sub
    > >> > -----------------
    > >> > Private Sub Form_Current()
    > >> > Call LockBoundControls(Me, True)
    > >> > 'Opens new records with fields filled from last entry
    > >> > Call AutoFillNewRecord(Forms!OrdersWDetails)
    > >> > Call lockbox_AfterUpdate
    > >> >
    > >> > End Sub
    > >> > -----------------------
    > >> > Private Sub lockbox_Click()
    > >> > Call LockBoundControls(Me, True)
    > >> >
    > >> > 'if you want to lock the controls, or:
    > >> > 'Call LockBoundControls(Me, False)
    > >> > 'if you want to unlock them
    > >> >
    > >> > 'If there are controls you *don't* want to lock, add their names as
    > >> > extraarguments, e.g.:
    > >> > 'Call LockBoundControls(Me, True, "Surname", "City")
    > >> > End Sub
    > >> > ---------------------------
    > >> > MODULE:
    > >> > 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
    > >> >
    > >> >
    > >>
    > >>
    > >>

    >
    >
    >
     

Share This Page