Lock ALL the Controls on a Form With "NOT"

[Uncle Gizmo]

Lock ALL the Controls on a Form - Nifty Access​

In this video I demonstrate how a True/False "If Statement" can be simplified with the word "NOT"... The "NOT Operator" can reverse the logic in your function. If you precede a value which Returns "TRUE" with the "NOT OPERATOR" then it will return "FALSE". You might wonder why on earth you would want to use the "NOT OPERATOR" --- The YouTube video along with the code below, provides an example, which you might find useful...

Private Function fLockAll(blnChkStatus As Boolean) As String

    Dim Ctrl As Control
        For Each Ctrl In Me.Controls
            Select Case Ctrl.ControlType
                Case acComboBox, acListBox, acOptionButton, acTextBox, acSubform ', acToggleButton, acSubform , acLabel , acTabCtl, acOptionGroup, acRectangle      ', acCheckBox
                    Ctrl.Enabled = Not blnChkStatus
                    Ctrl.Locked = blnChkStatus
                Case acCommandButton
                    Ctrl.Enabled = Not blnChkStatus
            End Select
        Next Ctrl

        btnDuplicateRec.Enabled = True
        btnBuildMsgBox.Enabled = True
        btnBuildMod.Enabled = True
        
End Function      'fLockAll

 

[oleronesoftwares]

@Uncle Gizmo , good video. Can you come up with a similar video but showing how all controls can be locked based on user logged in as against having to click a check control.

This can be useful in creating read only forms in a multi user enviroment.

 

[Uncle Gizmo]

all controls can be locked based on user login

I think you are looking for user-level security. That's not a subject for a 5-minute video!

 

[oleronesoftwares]

I think you are looking for user-level security.

Yes, that is what am referring to, but I must admit this is the first time, am seeing such a code in use in VBA, Kudos to you.

 

[Uncle Gizmo]

First step, setup a login system.

If each user has their own desktop PC then you can normally control this just by collecting their PC name.

However if people often share a PC then you may need an extra level to find out who's actually using the PC.

People log in, then leave the machine, if someone else takes the seat then you can have a situation where the person that's using the database is not the actual person who's logged in. This is difficult to control.

The next difficulty is, do you want people to have different levels of user access? In other words, you want user-level control/security? Restrict access to certain forms/data, by job function and/or seniority..

I get a headache just talking about it!!

 

[oleronesoftwares]

I get a headache just talking about it!!

Then we have that in common, i will rather demonstrate than talk.

I have been demonstrating via my youtube channel

https://www.youtube.com/channel/UCZwaTnTGH14R_ozfy_AlyXA/videos

but only one of my teachings is on ms access, others are on general software development, python, scala and Go

 

[Uncle Gizmo]

I have been demonstrating via my youtube channel

I have joined!

 

[oleronesoftwares]

I have joined!

Thank you

 

[Pat Hartman]

Sola,
If you look at

Need to lock all fields except the search box

Hello. I am not an access expert but I have been able to make a nice little access form that shows our products details, prices, sizes etc. I had it set that all the prices and other important data was locked so that no one could accidentally change the data. That worked great but if you wanted...

www.access-programmers.co.uk

The code I posted there shows how to use a user's security level to lock/unlock the controls. It differs from Uncle's solution in that it allows you to use the tag property of a control to permanently set the status of a control to LOCK or NOLOCK. Note that ONLY if you want the control to work independently of the user's security do you need to use the tag property.

For example, if a form has unbound search boxes or combos that you want to remain functional, you would set their tag property to Nolock so that the code will keep them unlocked regardless of whether or not the user's security would prevent him from being able to update the form. And sometimes, you have controls that should be locked regardless of the user's security such as the PK of the record. Once assigned, it can never be changed. PERIOD so you would not want the control to be unlocked.

 

[oleronesoftwares]

Sola,
If you look at

Thanks for sharing @Pat Hartman , i will look at it

 

[SHANEMAC51]

I don't really understand your love for locking/unlocking fields I prefer to have a ribbon form of the main fields with the ability to filter by part of the field value

- yellow fields are not adjustable (fields from the table/query)

- green – adjustable and cleanable (search field) type LIKE "*condition*"

- pink for dates and numeric values (adjustable), with the ability to set the interval

- when clicking on the first field of the line - invoking the correction form, in which the yellow/green rule also applies

 

[Minty]

If you search using Like *SearchTerms* with a wildcard at the beginning of the criteria, Access can't use any stored indexing in the search.
This might not matter with a few 100 records, but will become painfully slow if you have 1000's .

Better to use Like SearchTerms* and tell your end-users to include the * at the start, if they really don't know what the first characters they are searching for are.

 

[SHANEMAC51]

If you search using Like *SearchTerms* with a wildcard at the beginning of the criteria, Access can't use any stored indexing in the search.
This might not matter with a few 100 records, but will become painfully slow if you have 1000's .

it worked fine for me for 80,000 records with an arbitrary combination of conditions from 12 fields

 

[Minty]

With no other filtering? I wouldn't ever bring 80,000 records into a form without some type of pre-filtering.
It would be slow to do anything, especially with a shared backend. On a single-use unsplit database, it's a moot point.

 

[SHANEMAC51]

it worked fine for me for 80,000 records with an arbitrary combination of conditions from 12 fields

these are different materials in stock, the base is local, the choice is by
type / name of the material
- warehouse
- date of arrival
- unit of measurement
- the remainder
- ......

 

[Pat Hartman]

I don't really understand your love for locking/unlocking fields

The point of the code is to lock/unlock form fields to implement security. It solves the problem of the AllowEdits = No locking combo and search boxes which are unbound and so don't need to be secured. The code as presented is an all or nothing proposition. Using the tag property allows you to always lock or always unlock specific controls.

HOWEVER, I don't actually use this code in most apps. I wrote it because a client didn't want to be able to even type in a control and the client is always right. It is overhead that isn't necessary since I can always trap updates using the on dirty event. In the Dirty event, which runs as soon as a person types a single character in any control, I can determine if the person is authorized or not and undo the typing and cancel the update if the person is not authorized and let it continue if he is authorized.

In the world of the PC and Access, code efficiency isn't all that important (unless you are running a loop. Then it may matter). I cut my teeth writing mainframe transactions with thousands of users and a requirement for sub second response time. All code ran on the mainframe so there was serious contention for resources and it was extremely important that code was efficient. So, I learned early on that in many cases it is better to be reactive than to be proactive. Many people are obsessed with locking the form and preventing any data entry at all and they completely loose sight of the fact that you don't have to actually lock a form to protect the data from being updated. You just need a sound understanding of form level events and which event to use for what purpose. The event model isn't random. Each event was designed specifically to handle a given situation and the original designers of Access did a masterful job with the event model. There is only one event I've ever wanted that doesn't exist and that is an event that runs as the last event and with access to the current record. After the Form's AfterUpdate event which only runs when a record has been updates. So it is a form that runs whether or not the record was updated an as the last event before moving to a new record. I sort of understand why such an event doesn't exist - It would have to be read only so maybe that was too difficult to manage.