Lock everything on form except for one combobox?

[jonathanchye]

Hi,

I have implemented a lock button which works good in my form by locking all the controls in the form to prevent editing. However, this also locked my Search combobox.

This combobox basically searches records by unique ID. Is there anyway to lock everything except this combobox? I use the AllowEdits = false method to lock form and tried adding Me.cboSearch.Locked = false but that didn't work...

 

[vapid2323]

Shot in the dark but did you also do?

Me.cboSearch.Enabled = True

 

[boblarson]

Go to the control in design view and on the OTHER tab in the properties dialog put something like this in the TAG property:

DoNotLock

and then you can use this code to lock in the form's On Current event:

Dim ctl As Control
 
For Each ctl In Me.Controls
     Select Case ctl.ControlType
     Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox
           If ctl.TAG <> "DoNotLock" Then
              ctl.Locked = True
           Else
              ctl.Locked = False
           End If
      End Select
Next

And you can also check if it is a new record or not before locking in case you want to leave new records alone:

If Not Me.NewRecord Then
...put the rest here
End If

 

[jonathanchye]

Shot in the dark but did you also do?

Me.cboSearch.Enabled = True

Yes tried that but didn't work. I guess AllowEdits overrides all these?

 

[boblarson]

And you can't set the form's AllowEdits to false because that locks EVERY control regardless of whether you try to unlock an individual one.

 

[jonathanchye]

Go to the control in design view and on the OTHER tab in the properties dialog put something like this in the TAG property:

DoNotLock

and then you can use this code to lock in the form's On Current event:

Dim ctl As Control
 
For Each ctl In Me.Controls
     Select Case ctl.ControlType
     Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox
           If ctl.TAG <> "DoNotLock" Then
              ctl.Locked = True
           Else
              ctl.Locked = False
           End If
      End Select
Next

And you can also check if it is a new record or not before locking in case you want to leave new records alone:

If Not Me.NewRecord Then
...put the rest here
End If

Thanks for this suggestion. I am just using Me.Allowedits = No Me.AllowEdits method currently to lock though. I will consider using your method instead. Just want something quick and easy.

Had to fiddle around with my current method earlier as well just the locks are gone after changing records but now I fixed it. Only grudge is trying to exclude one combobox from being locked.

 

[jonathanchye]

And you can't set the form's AllowEdits to false because that locks EVERY control regardless of whether you try to unlock an individual one.

Ah, I see... I was intending to lock everycontrol but then realised I need to leave just one unlocked. Will try your method to see if that works. Do I put the code the same place? I am using the button's onclick method to apply the code.

Or should I use the Form On Current method?

 

[boblarson]

If you want the form locked for editing you would want it on the form's On Current event. Then to unlock the controls you basically just use the same code but change this part in red:

           If ctl.TAG <> "DoNotLock" Then
              ctl.Locked = [COLOR=red]False[/COLOR]
           Else
              ctl.Locked = False
           End If

So you could create a generic function to call like this:

Function LockUnlock(frm As Form, blnLock As Boolean)
Dim ctl As Control
 
For Each ctl In frm.Controls
     Select Case ctl.ControlType
     Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox
           If ctl.TAG <> "DoNotLock" Then
              ctl.Locked = blnLock
           Else
              ctl.Locked = False
           End If
      End Select
Next
End Function

And You can put that in a standard module so you could call it from anywhere.

So you would call it like this:

In the On Current Event to lock:

If Not Me.NewRecord Then
   LockUnlock Me, True
End If

And to unlock from a button:

   LockUnlock Me, False

 

[jonathanchye]

Thanks a lot for that mate I will have a look into it. However, one problem I see is with labelling my buttons. This is how I do it at the moment :

Me.btnLock.Caption = IIf(Me.AllowEdits, "Click to Lock", "Click to Unlock")]

 

[boblarson]

Don't label that way. You can use

Me.btnLock.Caption = Switch(Me.ControlName.Locked, "Click to Unlock",  Me.ControlName.Locked = False, "Click to Lock")

So you choose one of the controls which will be locked and unlocked by the code and that's what you use in the spot where I put ControlName.

 

[jonathanchye]

Don't label that way. You can use

Me.btnLock.Caption = Switch(Me.ControlName.Locked, "Click to Unlock",  Me.ControlName.Locked = False, "Click to Lock")

So you choose one of the controls which will be locked and unlocked by the code and that's what you use in the spot where I put ControlName.

Hmm good idea! Thanks again. I have a Yes/No field in my form represented by a checkbox labelled "Locked" (I know it's a bit confusing ) Basically this is a flag to show if the record is locked or not. I hide the lock/unlock button to certain users so I want something graphical for the to see if record is locked henced the tick box.

Here is the code I use to update the checkbox :

Me.Locked = Not Me.AllowEdits

How should I change this?

edit: Going through your code again I guess I just simple replace the ControlName with "locked" ?

 

[boblarson]

Hmm good idea! Thanks again. I have a Yes/No field in my form represented by a checkbox labelled "Locked" (I know it's a bit confusing ) Basically this is a flag to show if the record is locked or not. I hide the lock/unlock button to certain users so I want something graphical for the to see if record is locked henced the tick box.

Here is the code I use to update the checkbox :

Me.Locked = Not Me.AllowEdits

How should I change this?

edit: Going through your code again I guess I just simple replace the ControlName with "locked" ?

I would not use Locked as the name of the object. You can display the CAPTION of a label for the checkbox as "Locked" but you should not name the checkbox locked as that can have bad consequences with using that. I would use chkLocked as the name.

To replace Me.Locked = Not Me.AllowEdits you should be able to use

Me.chkLocked = Me.chkLocked.Locked

because it is on that form it will be getting locked like the other controls.

 

[jonathanchye]

Hmm, I've created a new module and pasted the function code in it. However, I get this error while trying to run :

"Expected variable or procedure, not module"

 

[boblarson]

Did you name the module something OTHER than the function name? If not, it should be something different from the function name. For example basLocks for the module name.

 

[jonathanchye]

Did you name the module something OTHER than the function name? If not, it should be something different from the function name. For example basLocks for the module name.

Ah renaming module seems to have solved that problem but now every form is locked lol and the button caption plus tick box don't update anymore...

 

[boblarson]

every form is locked

When you say "every form" do you mean you have subforms on that form? If so, you would need to go into design view and click on the subform control (control on the parent form which houses the subform, not the subform itself) and under Other > TAG put the same DoNotLock word.

and the button caption plus tick box don't update anymore...

If you have changed any field in the record you would need to issue a save

If Me.Dirty Then Me.Dirty = False

before you can lock again.

 

[jonathanchye]

I only have one subform at the moment but I haven't changed any settings yet. I am going to dump some code perhaps you could see what I am doing wrong?

Here is our Lock/unlock function :

Function LockUnlock(frm As Form, blnLock As Boolean)
Dim ctrl As Control
 
For Each ctrl In frm.Controls
     Select Case ctrl.ControlType
     Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox
           If ctrl.Tag <> "DoNotLock" Then
              ctrl.Locked = True
           Else
              ctrl.Locked = False
           End If
      End Select
Next
End Function

Here is my Form On Load function :

DoCmd.GoToRecord , , acLast

' Only Manager and Admins can lock/unlock records

If strAccessLevel = "Manager" Or strAccessLevel = "Admin" Then
Me.btnLock.Visible = True
Me.btnLock.Enabled = True
Me.btnCloseLive.Visible = True
Me.btnCloseLive.Enabled = True

' Cycle all controls with Tag!!!

For Each ctlCurr In Me.Controls
    If ctlCurr.Tag = "Commercial" Then
        ctlCurr.Locked = False
    End If
    'Lock Sales
    If ctlCurr.Tag = "Sales" Then
        ctlCurr.Locked = True
    End If

Next ctlCurr

End If
If strAccessLevel = "User" Then
Me.btnLock.Visible = False
Me.btnLock.Enabled = False
Me.btnCloseLive.Visible = False
Me.btnCloseLive.Enabled = False

' This is to further lock certain fields according to user levels 
For Each ctlCurr In Me.Controls
    If ctlCurr.Tag = "Commercial" Then
        ctlCurr.Locked = True
    End If

    'Unlock Sales
    If ctlCurr.Tag = "Sales" Then
        ctlCurr.Locked = False
    End If
Next ctlCurr

End If

Dim ctl As Control

If Me.chkboxLocked.Value = True Then
Dim ctrl As Control
For Each ctrl In Me.Controls
     Select Case ctrl.ControlType
     Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox
           If ctrl.Tag <> "DoNotLock" Then
              ctrl.Locked = True
           Else
              ctrl.Locked = False
           End If
      End Select
Next
End If
End Sub

HEre is Form On Current :

Private Sub Form_Current()

If Not Me.NewRecord Then
LockUnlock Me, True
Me.btnLock.Caption = Switch(Me.chkboxLocked.Locked, "Click to Unlock", Me.chkboxLocked.Locked = False, "Click to Lock")
If Me.Live.Value = "Live" Then Me.btnCloseLive.Caption = "Click to Close"
If Me.Live.Value = "Closed" Then Me.btnCloseLive.Caption = "Click to Re-Open"

lngRed = RGB(255, 0, 0)
lngGreen = RGB(10, 255, 10)
If Me.txtLive.Value = "Live" Then
Me.txtLive.BackColor = lngGreen
End If

If Me.txtLive.Value = "Closed" Then
Me.txtLive.BackColor = lngRed
End If

End If

End Sub

And finally here is on button click

Private Sub btnLock_Click()
lngRed = RGB(255, 0, 0)
lngGreen = RGB(10, 255, 10)
LockUnlock Me, False
Me.btnLock.Caption = Switch(Me.chkboxLocked.Locked, "Click to Unlock", Me.chkboxLocked.Locked = False, "Click to Lock")
End Sub

I hope you can help see what's wrong as I really want to use your method as I feel my current method is bit dodgy Thanks.

 

[boblarson]

Well the one thing is you don't need this in the On Load event:

' Cycle all controls with Tag!!!

For Each ctlCurr In Me.Controls
    If ctlCurr.Tag = "Commercial" Then
        ctlCurr.Locked = False
    End If
    'Lock Sales
    If ctlCurr.Tag = "Sales" Then
        ctlCurr.Locked = True
    End If

Next ctlCurr

And pull this out of the On Load event:

If Me.chkboxLocked.Value = True Then
Dim ctrl As Control
For Each ctrl In Me.Controls
     Select Case ctrl.ControlType
     Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox
           If ctrl.Tag <> "DoNotLock" Then
              ctrl.Locked = True
           Else
              ctrl.Locked = False
           End If
      End Select
Next
End If

You just need to call

LockUnlock Me, True

 

[jonathanchye]

The thing is there's one field I want to further lock according to user level so let's say the form is unlocked that field might still be locked to certain users...

I don't quite understand when you said I just need to call LockUnlock Me, True... when do I call it and why? Does that function mean Apply lock if something is true?

Looking back at this function :

Function LockUnlock(frm As Form, blnLock As Boolean)
Dim ctrl As Control

For Each ctrl In frm.Controls
Select Case ctrl.ControlType
Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox
If ctrl.Tag <> "DoNotLock" Then
ctrl.Locked = True
Else
ctrl.Locked = False
End If
End Select
Next
End Function
Are you sure I typed in correctly here? Seems a bit weird to me...

 

[jonathanchye]

Hmm it's not updating at all. I click btnLock but nothing happens. All controls are locked except one I tagged DoNoLock...

What used to happen is chkboxLocked used to update as well...