Lock everything on form except for one combobox? (1 Viewer)

[boblarson]

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...

You can lock that one using your own code.

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?

Look at the function. There is a parameter for form (why we pass ME to it) and then there is a True/Fasle called blnLock. By using

LockUnlock Me, True

that means to run the LockUnlock procedure on the current form and the True tells the function to lock the controls instead of unlocking them.

The red part in the generic function does the locking based on the true or false:

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 = [COLOR=red][B]blnLock[/B][/COLOR]
           Else
              ctl.Locked = False
           End If
      End Select
Next
End Function

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...

You posted the original code I posted instead of the current one which should be in your module. The one I just posted in this post is the one you should have in your standard module.

And what I mean by you can just call the function is that in your Load and On Current events you can just simply use

LockUnlock Me, True

to lock the controls except for the ones with DoNotLock in the Tag. You can then, after that line, unlock any you need based on the user.

So something like this:

Private Sub Form_Current()
Dim strUserPermissions As String
 
LockUnlock Me, True
 
strUserPermissions = Nz(Dlookup("UserPermission", "tblPermissions", "[UserName]=" & Chr(34) & VBA.Environ("username") & Chr(34)),"") 
 
If strUserPemissions <> vbNullString Then
   Select Case strUserPermissions
      Case "EditFields1"
          Me.Control1Name.Locked = False
      Case "EditFields3"
          Me.AnotherControlName.Locked = False
   End Select
End If
 
End Sub

That is just a real simple sample (the permissions stuff is just something I made up to show you what you can do).

And to unlock you just call the

LockUnlock Me, False


I hope that helps.

 

[jonathanchye]

ah I think I understand now. Edit: Nmind figured out how to use it as a toggle but now I get error saying my other controls are closed or doesn't exist...

I can't even go to next record once I lock a record this way...

 

[boblarson]

ah I think I understand now. Edit: Nmind figured out how to use it as a toggle but now I get error saying my other controls are closed or doesn't exist...

I can't even go to next record once I lock a record this way...

Can you post a copy of your database (with bogus data of course)?

 

[jonathanchye]

hmm if you could provide email I would rather send via email? There's not much data but I have done a lot of graphics and design and alot of tables which contains real life names are linked...

 

[boblarson]

hmm if you could provide email I would rather send via email? There's not much data but I have done a lot of graphics and design and alot of tables which contains real life names are linked...

See my PM to you for my email address I use for attachments. Also, be sure to explain in the email what it is and what I'm looking for because I do get a fair amount of email and hit many posts throughout the day and so I may not remember what it is about.

 

[jonathanchye]

I think I may have found the solution after stripping back all code and starting afresh.

I've created a boolean called Dim tempBol as Boolean.

On Form load I assign the value to : tempBol =chkboxLocked.Value

Then I also Did LockUnlock Me, tempBol.

Then on the Button to toggle the lock I did :

tempBol = Not tempBol
LockUnlock Me, tempBol

Seems to work now but need to do more testing

 

[misscrf]

This is such a great, elegant solution! Thanks for posting!

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