Lock 1 record (LockBox)

[lmv]

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

 

[Wayne Morgan]

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?

 

[lmv]

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]

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.

 

[lmv]

Thanks again!

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.