Lock, Unlock Controls

Lock, Unlock Controls

A problem that crops up from time to time is the need to handle a Group of Controls differently from the rest of the Controls on a Form.
Usually this is the need to Lock the Controls so that Data cannot be changed, and conversely Unlock them. However it may be the need to change the Back Colour, or some other Attribute of the Controls.

In the following Videos, I demonstrate a Basic Approach right up to a Sophisticated, Object Oriented Approach.

Sample dB :- Download Sample File To Accompany the Videos :- This blog is accompanied by two essential zip files. The first, LockUnlockControls_2.zip, includes all the code featured across our tutorial videos, providing a comprehensive guide to locking and unlocking controls in MS Access. Additionally, we’ve introduced an innovative method known as the “Nifty Container” for enhanced control management. The corresponding zip file for this technique is available as well, named NiftyContainer_2a.zip. Both files together offer a robust toolkit for efficiently managing your MS Access projects.

Function to Lock/Unlock Controls

Lock/Unlock Controls

Video 1 (03:24)

Demonstration of a simple function that Locks or Unlocks controls on an MS Access Form, depending on whether you pass it a true or false boolean value.

Video 1 (03:24)

Nifty Access YouTube Thumb Nail
Play Video

Lock/Unlock Controls - Bullet Points

00:12 Code in “b”
00:22 the command button
00:34 calls this function “fLockUnlock”
01:35 explanation of a function, its components and how a function works
01:58 it returns the variable name. In other words it identifies the parameter by the name you give it in the function
02:23 right click to open the “Quick Info” for the function

… …

Identify Control with a Character

Group with Character

Video 2 (01:39)

Demonstrates the method of naming the controls with a particular set of characters to identify them as belonging to a particular group.

Video 2 (01:39)

Nifty Access YouTube Thumb Nail
Play Video

Group with Character - Bullet Points

00:05 you might want to separate your controls into groups
00:30 you can code the names of controls to make them fit particular groups
00:40 custom naming convention adding “G1”
00:55 another group using the same custom naming convention identified with “G2”
01:08 the command buttons also use the same custom naming convention
01:17 look at the code behind the form and you will see it utilizes this custom naming convention

… …

Looping Through the Collection

Group with Character

Video 3 (04:52)

Demonstrates how to set particular controls values, (a group of similar controls) by looping through the collection of controls on an MS Access Form.

Video 3 (04:52)

Nifty Access YouTube Thumb Nail
Play Video

Group with Character - Bullet Points

00:01 in this video we have a look at the form prefixed “c”
00:18 an alternative method of handling a Group of Controls is to look through all of the Controls in the forms Control Collection
00:48 looping through each control and testing it
01:15  think about a collection like this,  imagine you have taken all of the keys off of your PC keyboard…
01:25  now imagine you’ve placed all the keys in a bag or even a bucket
01:30 you can’t put your hand in and pull out a “W” for instance
01:40 you pick up a key, you inspect it, what is it? “W” “A” “z”?
01:55 this Code stub is called by the command button onclick event
02:02 the button click event called the function “fLockUnlock”
02:04 we pass in to the function the Group Code “G1” as a parameter
02:20  this is the function “fLockUnlock”
02:24 this parameter “strGroup” is where the group code “G1” is pass through
02:26  the parameter “blnLockIfTrue” is set to True or False depending  on whether we want to set the controls to locked or unlocked
02:30 quick look at the for each loop which examines each control in the form
02:46 don’t confuse Control with Controls one refers to the individual controls the other refers to the collection of controls
03:00 you use the “Me” keyword to refer to the form you are within.
03:02 if the code was in an external module you would have to change “Me” to the name of the form you were referring to
03:25 the select case statement examines each controls control type
03:33 only runs the code if the control is a textbox or a combobox
03:42 controls have different attributes and if you try and modify a control which doesn’t have a particular attribute it will throw an error
03:56 a label has a caption property where has text boxes and combo boxes do not
04:05 without making this check you could find that your code tried to change a property a control didn’t have
04:20 this line calls the function “fGetGroup”
04:23 The Control name is passed in as a parameter
04:28 the group identifier is passed in as a string parameter
04:34 !!!Mistake!!! I meant to say control name, not “Form Name”

… …

Replace the Two Command Buttons with a Single Command Button

ONE for TWO!

Video 4 (01:47)

Demonstrates how to replace two command buttons which switch between a lock and unlock state as in this case, and replace the two command buttons with a single command button and some code.

Video 4 (01:47)

Nifty Access YouTube Thumb Nail
Play Video

ONE for TWO!

00:03 looking at form “d”
00:14 how to replace two buttons with one
00:22 the code checks the caption of the control to see if it contains “Lock”
00:30 when the form is freshly loaded the buttons caption is it’s default so to speak which is “Lock”
00:35 the command buttons caption is inspected and if it is “lock”
00:39 if the caption is lock, it is changed to unlock
00:48 then the lock function is called and the controls are “Locked”
01:12 if it’s “Unlocked” in other words it doesn’t find the caption “Lock” then the else statement runs and performs the reverse of above
01:38 if you click the button you will see it will alternate between lock and unlock

… …

Lock/Unlock Controls 5

Lock/Unlock Controls 5

Video 5 (03:59)

Demonstrates how to setup command buttons to call a class module, the class module can then execute identical code for every button that calls it from your form.

Video 5 (03:59)

Nifty Access YouTube Thumb Nail
Play Video

Lock/Unlock Controls 5

00:03 Form “e”
00:10 lock and unlock button working
00:44 looking at the buttons properties
00:55 look in the event procedure
01:00 the event procedure doesn’t contain [Event Procedure] or [Embedded Macro]
02:14  the major difference is the equals sign “=”  which instruct the code to load the function named “fActiveCmdButton([BtnG1])”
02:33  I say it puts it in square brackets because it’s a control on the Form, but I would like to check that!
02:53 You need to copy and paste that function into every command buttons onclick event and then also change the reference to the button
03:00 in Group 2 it’s the same function but with a different name for the command button “fActiveCmdButton([BtnG2])”
03:12 it’s relatively simple to set this up but you still need to edit each function to reflect the different button it refers to…
03:46 this was one of the first videos I did I think it was about 2006 12 or 13 years ago! So the quality is very low, and I haven’t honed my skills yet

… …

Class module Executes Identical Code for "Every" Button

Class Module - Identical Code

Video 6 (03:29)

Demonstrates how to have your Command Buttons access the code in a Class Module through a function in the code behind the form.

Video 6 (03:29)

Nifty Access YouTube Thumb Nail
Play Video

Class Module - Identical Code - Bullet Points

00:03 Form “e”
00:12 this refers to the class module “clsCmdButtonPressed”
00:22 set the copy of the class module to the class module
00:34 next we call a “Public” function which is within the class model “fInitiate”
00:44 and pass in the command button with a control variable
01:44 you can pass in any of the buttons with this method
01:46 it passes the button in as a command button “Object”
01:54 then it passes the command button object into the class through the function “fInitiate”

… …

Class Module- Walkthrough

Class Module- Walkthrough

Video 7 (03:55)

Overview of a Class that is used with an MS Access Form. This Class enables multiple command buttons to perform basically the same action.

Video 7 (03:55)

Nifty Access YouTube Thumb Nail
Play Video

Class Module- Walkthrough - Bullet Points

00:03 walk through of the class module
00:05 works with form “e”
00:20 this is where the function “fInitiate” I demonstrated in the last video is called from the form onclick property? Event?
00:32 set member command button “mCommandButton” to the past in command button
00:40 then call the click event of the member command button “mCommandButton”
00:47 it’s basically the same code from previous examples modified to handle “mCommandButton”
01:50  I’ve changed the “Me.” reference to the actual form name here
01:58  I mentioned this in the previous video, the fact that you could replace the “Me.” Key word with the actual Form name and move the code out into a stand-alone module or in this case a class module
03:24  I mention “WithEvents” here and also the fact I don’t understand them! I still don’t understand them! What 15 years later!

… …

Nifty Container Method

Class Module- Walkthrough

Video 7 (03:55)

The “Nifty Container” offers you a graphical way of managing the controls on your form. All you do is surround a group of controls with a box, choose one of the coding examples, or write your own, and there you go, you have a simple and easy-to-use method, which avoids using your Control(s) Tag Property, (the usual method of doing this) which you might well want to use for something else. There are many ways to identify controls on your form for particular attention. This “Nifty Container” idea I have developed is unique, I haven’t come across it anywhere else. All you do is draw a box around the group of controls you want to handle. You might want to make sure that the controls have text entries, you might want to make sure that at least one of the controls has an entry, anything you want to do to a group of controls can be done with the “Nifty Container” system, and at the same time avoids using the controls Tag property…

Video 7 (03:55)

Nifty Access YouTube Thumb Nail
Play Video

Class Module- Walkthrough - Bullet Points

0:08 - This is the container or rectangle.
0:38 - In this rectangle I've got combo boxes.
1:07 - Let's have a look in design view.
1:13 - Press the rectangle button in the toolbar.
1:26 - It doesn't work if the rectangle does not surround the controls.
1:40 - Demonstration of the same container around a different set of controls
2:03 - You can easily create a new container
2:32 - This is the code behind the button
2:40 - For EACH control in ME controls
2:50 - I call the nifty container function - fNiftyContainer()
3:35 - This is the clear button which resets all the controls
4:00 - Why use the nifty container? It frees up your Tag property for other uses

… …

More Useful Stuff HERE:-

This website uses third-party software - WordPress Add-Ins to be exact. I don't know what any individual add-in does, but I'm sure that many of them collect information about you. So be aware, if you continue using this site, then you are likely to be sharing your information. I don't know how to disable this sharing for any, or all of the plugins for individual users. So I can't stop the sharing of information. If this worries you then please do not use this site... If you continue to use this site I will assume that you are happy with it.

Do you need a hand in the right direction?

You are in the right place.