I could be wrong, but I don't think this answers your question at all.
Making a field that says the record is locked does nothing to prevent the
user from making changes.
Here are a few previous posts that may offer more help. The key is to check
the field in the "current" property of the form so the system will look at
your field as you scroll from one record to the next. BE aware that if you
do not have the user locked out of the table, they could always open the
tables and make changes....
----------------------------------------------------------
If you use a form to display the data in your table, you have a great many
more options, because you can manipulate the controls which display the
data.
If, for example, you have a "CompleteDate" field, and want to disallow edits
for any record for which there is a value in this field, you could use code
like this:
if isnull(CompleteDate) then
me.allowedits=true
else
me.allowedits=false
endif
The question then becomes where to execute this code. At the very least,
you'll want to run it every time you move to a new record, to check whether
you want to allow edits for that record. Use the form's Current event for
that.
If you want to stop editing of the record as soon as a value is entered,
run this code in the control's AfterUpdate event. (In this case, you'll
want to include the line
me.dirty=false
to save the record as it is, because changes in the allowedits property only
take effect after the record is saved.
HTH
- Turtle
----------------------------------------------------------
So you want to add a yes/no field to the table, and check this box to
prevent changes to the record. The user can still uncheck this yes/no field
to edit the record, but cannot edit anything else while the box is checked.
This involves code in the AfterUpdate event of the the check box, and also
in the Current event of the form. You cannot set the AllowEdits property of
the form (since that would prevent you unchecking the box), so you need to
loop through the controls and set their Locked property. Presumably you want
to prevent the deletion of the record if the record is locked as well.
1. Copy the code below into a standard module, and save.
2. Set the AfterUpdate propery of your lockbox check box to:
[Event Procedure]
Click the Build button, and add this line to the code:
Private Sub lockbox_AfterUpdate
Call LockBoundControls(Me, Me.lockbox.Value, "lockbox")
End Sub
3. Set the On Current property of the form to
[Event Procedure]
Click the Build button, and add this line to the code:
Private Sub Form_Current
Call lockbox_AfterUpdate
End Sub
The interesting aspects of the code are:
- Unbound controls are not locked.
- Add the names of controls you don't want to lock (such as your "lockbox"
check box).
- The code calls itself recursively to lock any subforms as well. If you do
not want to lock a subform, include the name of the subform control in the
exception list.
--------------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 on " & conMod & " at " &
Now()
End Select
Next
Exit_Handler:
Set ctl = Nothing
Exit Function
Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description,,
"LockBoundControls"
Resume Exit_Handler
End Function
--------------code ends-------------------------
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
-----------------------------------------------
You could use the Current event to test if that field has a value, and if it
does, lock the controls for that record:
Private Sub Form_Current()
Dim blnLock As Boolean
blnLock = (Len(Me.DateFieldName.Value & "") <> 0)
Me.ControlName1.Locked = blnLock
Me.ControlName2.Locked = blnLock
Me.ControlName3.Locked = blnLock
' etc.
End Sub
--
Ken Snell
<MS ACCESS MVP>
