Set record to locked when check box is ticked

Hi all,

I have an After Update event on a yes/no tick box on my form that prompts with a message box, what i want it to do is to set the current record to be uneditable when the check box is ticked.

Private Sub Task_Complete__AfterUpdate()
Dim TaskComplete As String
TaskComplete = MsgBox("Are you sure you wish to complete the task?", vbInformation + vbYesNo, "Complete Task")

If (TaskComplete = vbYes) Then
Me.Form.[Date Completed] = Now() And Me.AllowEdits = False

ElseIf (TaskComplete = vbNo) And Me.Form.[Task Complete?] = False Then

Exit Sub

End If

End Sub

Open in new window


Any ideas?

Cheers

Brendan
eyeisystemsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Two approaches:

1. In the OnCurrent event, set the forms AllowEdits property to false/true based on the field.

2. In the OnCurrent event, loop  through the forms controls and set them to enabled = false, and/or possibly locked = true.

#2 is more of a pain because there are some controls you might not want to touch (like command buttons), but it gives you a lot more control over things that option #1.

Jim.
0
eyeisystemsAuthor Commented:
Hi Jim,

Am i right in thinking that it would set the property for the whole form with either #1 or #2?

Essentially i want to lock just the current record so thats no longer editable rather than the whole form field.

If i'm mistaken, could you give an example code?

Cheers

Brendan
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Brendan,

  No, with option #2, your setting the enabled and/or locked property on each control.   Say you have a filter at the top of your form, which allows filter by date.   If you use open #1, your user can no longer enter a date.

 So it depends on the form.   #1 is far simpler:

  If Me.[Date Completed] = True then
      'Lock the form
      Me.AllowEdits = False
  Else
     ' Unlock form
     Me.AllowEdits = True
  End If

But most use option two.   Here's a procedure you can call to lock your controls:

Public Sub StdLockForm(frm As Form, intState As Integer)
  
  'Lock/unlock all the fields in the detail section of the form that are enabled.

  Dim intL As Integer              'Generic loop counter
  Dim intCount As Integer          'Number of controls on the form
   
  intCount = frm.Count             'Find out how many controls are on the form

  On Error Resume Next
  For intL = 0 To intCount - 1      'Step thru all controls on the form
    If (frm(intL).Section = False) Then 'Don't do any control execpt those in the detail section
        If (frm(intL).Enabled = True) Then frm(intL).Locked = intState   'Set 'Locked' property to proper state
    End If
  Next intL
  On Error GoTo 0

End Sub

Open in new window


 and you would do:

 
 StdLockForm(Me, Me.[Date Completed] )


 This only messes with the locked property and only does controls in the detail section.  So for example, filtering options in the header, or command buttons in the footer would not get touched.

  There's all kinds of scheme's you can use.   Some use a controls tag property, filling it with something like:

 Lock

 and then look for that to know if the control should be touched or not ('lock' in of itself doesn't mean anything, it's just a tag value to look for so you know if you should touch the control or not).

  Let me know what else you might need.

Jim.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

eyeisystemsAuthor Commented:
Hi Jim

i probably should have added that this is a subform datasheet.

So if for instance

Task1 - Task Complete (Yes/No checkbox), other fields...
Task2 - Task Complete (Yes/No checkbox), other fields....

If i set the task complete checkbox on task 1 only to 'Yes' then all the fields of record task1 need to be locked, task2 should still be open.

If i'm reading your response correctly the code provided would lock the property of the control to stop it being edited.

Sorry if i'm misunderstanding this :)

Cheers

Brendan
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<i probably should have added that this is a subform datasheet.>>

 So you'll want the code in the subform, and also change that to a continuous form view.  You have very little control over a data sheet.

<<If i set the task complete checkbox on task 1 only to 'Yes' then all the fields of record task1 need to be locked, task2 should still be open.>>

That will be correct when Task2 receives the focus.  The code will fire in the OnCurrent when you navigate to a new control.

 play around with a bit and you'll see what I mean.

Jim.
0
Helen FeddemaCommented:
Instead of locking the whole record (which would mean that you could never unlock it in Form view), try this code to lock all but a few controls (definitely leave the checkbox unlocked).  Run it from the Current event as well as the checkbox:

Public Sub CycleControls(frm As Access.Form)

   Dim ctl As Access.Control
   
On Error Resume Next

      For Each ctl In frm.Controls
         Debug.Print ctl.Name 
         If ctl.Tag = "Lock" Then
            ctl.Locked = True
         End If
      Next ctl
         
End Sub

Open in new window

0
Helen FeddemaCommented:
You can unlock the controls similarly -- just make another procedure and change the True to False.
0
PatHartmanCommented:
I use this procedure to Lock or Unlock controls in the form object passed to the procedure.  The Tag property of a control is used to override the lock/unlock code.  So, if you want a control to be always unlocked because it is a filter, then the tag would be "NoLock".  If you wanted it to be always locked because it was the PK or FK or something else that could never be changed, then the tag would be "Lock".  Place the sub LockControls() in a standard module so you can use it from any form.
Call LockControls(Me, True)         'lock
...
Call LockControls(Me, False)        'unlock

Open in new window

Public Sub LockControls(frm As Form, bLock As Boolean)
    Dim ctl As Control
    For Each ctl In frm.Controls
    Select Case ctl.ControlType
        Case acTextBox, acComboBox, acListBox, acCheckBox
            Select Case ctl.Tag
                Case "NoLock"
                    ctl.Locked = False
                Case "Lock"
                    ctl.Locked = True
                Case Else
                    ctl.Locked = bLock         'toggle locks
            End Select
        Case acCommandButton
            Select Case ctl.Tag
                Case "NoLock"
                    ctl.Enabled = True
                Case "Lock"
                    ctl.Enabled = False
                Case Else
                    ctl.Enabled = Not bLock         'toggle locks
            End Select
    End Select
Next ctl
Set ctl = Nothing
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.