M
MAR
I'm new to access 2003, and need to know how can I trigger the form to get
locked after checking a box? HELP!!!!!
locked after checking a box? HELP!!!!!
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
K
Ken Sheridan
Put the following code in the check box's AfterUpdate event procedure:
On Error GoTo Err_Handler
Dim ctrl As Control
For Each ctrl In Me.Controls
If ctrl.Name <> Me.ActiveControl.Name Then
' following lines lock and disable all controls
' apart from check box if check box is checked,
' unlocks/enables then if its unchecked
On Error Resume Next
ctrl.Locked = Me.ActiveControl
ctrl.Enabled = Not Me.ActiveControl
On Error GoTo Err_Handler
' include following line to also
' prevent deletions
Me.AllowDeletions = Not Me.ActiveControl
' include following line to also prevent
' new records being added
Me.AllowAdditions = Not Me.ActiveControl
End If
Next ctrl
Exit_Here:
Exit Sub
Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here
Ken Sheridan
Stafford, England
On Error GoTo Err_Handler
Dim ctrl As Control
For Each ctrl In Me.Controls
If ctrl.Name <> Me.ActiveControl.Name Then
' following lines lock and disable all controls
' apart from check box if check box is checked,
' unlocks/enables then if its unchecked
On Error Resume Next
ctrl.Locked = Me.ActiveControl
ctrl.Enabled = Not Me.ActiveControl
On Error GoTo Err_Handler
' include following line to also
' prevent deletions
Me.AllowDeletions = Not Me.ActiveControl
' include following line to also prevent
' new records being added
Me.AllowAdditions = Not Me.ActiveControl
End If
Next ctrl
Exit_Here:
Exit Sub
Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here
Ken Sheridan
Stafford, England
M
MAR
Thank you Ken so much!!!!! and is it possible to have only one user to unlock
the form for editing?
the form for editing?
K
Ken Sheridan
Not in the same way, but if you use a 'pessimistic locking' strategy by
setting the form's RecordLocks property to 'Edited Record' (which is the
recommended strategy these days now that Access provides true record locking
rather than page locking) then if one user begins to edit a record it will be
locked and other users won't be able to edit it until the original user
releases the lock by saving the record or backing out of the edit by
'undoing' the record. With pessimistic locking, when a user has locked a
record other users who have the record open as the current record in the form
will see the symbol in the record selector on the form change from the usual
pencil to a 'no entry sign', a circle with a diagonal line across it.
While a user is editing one record another user can still edit another
record. It is possible to lock 'all records', but this is not normally done
when individual records are being updated; its more suitable for batch
updates.
The other locking strategy of 'optimistic locking' (no locks) allows two or
more users to edit the same record simultaneously, alerting users when they
try to save the record if another user has changed it since they began
editing the record. They then have the chance to see the other user's
changes and/or overwrite them with their own. This strategy isn't usually
used now; it was favoured at one time (pre Access 2000 I think) as Access
then locked the entire page, not the individual record, so with pessimistic
locking, when a user was editing a record not only that record but adjacent
ones could also be locked.
Ken Sheridan
Stafford, England
setting the form's RecordLocks property to 'Edited Record' (which is the
recommended strategy these days now that Access provides true record locking
rather than page locking) then if one user begins to edit a record it will be
locked and other users won't be able to edit it until the original user
releases the lock by saving the record or backing out of the edit by
'undoing' the record. With pessimistic locking, when a user has locked a
record other users who have the record open as the current record in the form
will see the symbol in the record selector on the form change from the usual
pencil to a 'no entry sign', a circle with a diagonal line across it.
While a user is editing one record another user can still edit another
record. It is possible to lock 'all records', but this is not normally done
when individual records are being updated; its more suitable for batch
updates.
The other locking strategy of 'optimistic locking' (no locks) allows two or
more users to edit the same record simultaneously, alerting users when they
try to save the record if another user has changed it since they began
editing the record. They then have the chance to see the other user's
changes and/or overwrite them with their own. This strategy isn't usually
used now; it was favoured at one time (pre Access 2000 I think) as Access
then locked the entire page, not the individual record, so with pessimistic
locking, when a user was editing a record not only that record but adjacent
ones could also be locked.
Ken Sheridan
Stafford, England
M
MAR
Ken,
I’ll keep it for one user for the moment, but when I exit out from the form
or from a case number (using the code provided) and log back in when the
check box has been activated I can still be able to edit even though the
boxed is checked, but if I do the uncheck and check the box back again to
lock (or unlock) the form, I notice that all the cases get to be locked or
unlock up too. Any suggestions on how can I save each approved case and keep
from editing all cases while I add or edit one case only at a time?
I’ll keep it for one user for the moment, but when I exit out from the form
or from a case number (using the code provided) and log back in when the
check box has been activated I can still be able to edit even though the
boxed is checked, but if I do the uncheck and check the box back again to
lock (or unlock) the form, I notice that all the cases get to be locked or
unlock up too. Any suggestions on how can I save each approved case and keep
from editing all cases while I add or edit one case only at a time?
K
Ken Sheridan
It sounds like the check box is bound to a column in the form's underlying
table or query; I'd assumed it was unbound. With a bound control call the
check box's AfterUpdate event procedure in the form's Current event
procedure. If the control is called chkLockControls say, then the code to
call the procedure would be:
Me.chkLockControls.SetFocus
chkLockControls_AfterUpdate
This first moves the focus to the check box, and then executes the code in
its AfterUpdate event procedure, so the other controls will be accessible or
not depending on the value of the check box in the current record.
One other thing you should do is cater for the user moving the form to a new
record. Until the user begins to insert data into the new record the check
box will be Null, but to unlock/enable the controls you need a value of
False. So, in the check box's AfterUpdate event procedure change each
occurrence of:
Me.ActiveControl
to:
Nz(Me.ActiveControl,False)
the Nz function will return a value of False if the control is Null,
otherwise it returns the value of the control, which might be True or False.
You would not have to do this if the AfterUpdate event procedure was only
being executed after the control is updated as in the case of a bound control
it can only be updated to True or False, these being the only legitimate
values of a Boolean (Yes/No) data type column, not to Null, but as its now
being called by the form's Current event procedure Null needs to be catered
for.
Ken Sheridan
Stafford, England
table or query; I'd assumed it was unbound. With a bound control call the
check box's AfterUpdate event procedure in the form's Current event
procedure. If the control is called chkLockControls say, then the code to
call the procedure would be:
Me.chkLockControls.SetFocus
chkLockControls_AfterUpdate
This first moves the focus to the check box, and then executes the code in
its AfterUpdate event procedure, so the other controls will be accessible or
not depending on the value of the check box in the current record.
One other thing you should do is cater for the user moving the form to a new
record. Until the user begins to insert data into the new record the check
box will be Null, but to unlock/enable the controls you need a value of
False. So, in the check box's AfterUpdate event procedure change each
occurrence of:
Me.ActiveControl
to:
Nz(Me.ActiveControl,False)
the Nz function will return a value of False if the control is Null,
otherwise it returns the value of the control, which might be True or False.
You would not have to do this if the AfterUpdate event procedure was only
being executed after the control is updated as in the case of a bound control
it can only be updated to True or False, these being the only legitimate
values of a Boolean (Yes/No) data type column, not to Null, but as its now
being called by the form's Current event procedure Null needs to be catered
for.
Ken Sheridan
Stafford, England