Lock ALL the Controls on a Form With "NOT" (1 Viewer)

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 22:45
Joined
Jul 9, 2003
Messages
14,650

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

Code:
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

Passionate Learner
Local time
Today, 14:45
Joined
Sep 22, 2014
Messages
1,140
@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

Nifty Access Guy
Staff member
Local time
Today, 22:45
Joined
Jul 9, 2003
Messages
14,650
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

Passionate Learner
Local time
Today, 14:45
Joined
Sep 22, 2014
Messages
1,140

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:45
Joined
Feb 19, 2002
Messages
36,894
Sola,
If you look at

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.
 

SHANEMAC51

Active member
Local time
Tomorrow, 00:45
Joined
Jan 28, 2022
Messages
294
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
 

Attachments

  • msa_0131.png
    msa_0131.png
    35.1 KB · Views: 130
  • msa_0131a.png
    msa_0131a.png
    19.4 KB · Views: 105

Minty

AWF VIP
Local time
Today, 22:45
Joined
Jul 26, 2013
Messages
9,371
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

Active member
Local time
Tomorrow, 00:45
Joined
Jan 28, 2022
Messages
294
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

AWF VIP
Local time
Today, 22:45
Joined
Jul 26, 2013
Messages
9,371
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

Active member
Local time
Tomorrow, 00:45
Joined
Jan 28, 2022
Messages
294
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

Super Moderator
Staff member
Local time
Today, 17:45
Joined
Feb 19, 2002
Messages
36,894
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.
 

Users who are viewing this thread

Top Bottom