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

 

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