469,344 Members | 6,163 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,344 developers. It's quick & easy.

How to lock a control in a specific record or subset of records


I have an Access 2002 continuous form called "Project Update Form" in which users can update project values presented in a series of combo boxes and text boxes.

Three of the combo boxes ("FY", "Qtr" and "Mth") contain the values for a project's deadline: fiscal year, quarter and month, respectively, and are enclosed in an option group such that when the values for any of these three combo boxes are changed, an automatic change occurs to the value in a fourth combo box (named "cboStatus"), changing its value to a pre-determined setting (in this case, the word "Yellow").

Here's my challenge...

I want to lock the value in cboStatus following the AfterUpdate procedure to the option group. This is because I don't want someone to go back and change the status to its original setting after a date change. Furthermore, I want the value locked only for the specific record where the date changed. In other words, if I have 10 records in my table but only 2 whose date changed, I want the cboStatus to be locked for the two records only.

Everything I've tried thus far locks cboStatus for ALL records. Can I lock a control on a specifc record or subset of records?

Whatever advice anyone can offer would be greatly appreciated!
Feb 22 '08 #1
6 3386
Stewart Ross
2,545 Expert Mod 2GB
...I want to lock the value in cboStatus following the AfterUpdate procedure to the option group. This is because I don't want someone to go back and change the status to its original setting after a date change..
Hi. You could use the On Current event of your Project Update form to toggle the controls you don't wish to have updated to enabled=false and locked=true based on a condition testing the value of your status field (and any others you choose). Skeleton code for this in your On Current event is just

Expand|Select|Wrap|Line Numbers
  2. Dim LockConditionMet as Boolean
  3. LockConditionMet = (specify the condition to be tested to determine whether to lock the values; condition true = lock, false = allow updates)
  4. me.firstcontrol.enabled = Not LockConditionMet 
  5. me.firstcontrol.locked = LockConditionMet
  6. me.secondcontrol.enabled = Not LockConditionMet
  7. me.secondcontrol.locked = LockConditionMet
  8. etc
On Current is a record-level event that is fired each time you move from one record to another.

You would also call the On Current event in your After Update event, so that after a user updates values on your form the condition is tested to toggle controls on or off.

This will not prevent changes to individual values at table level if users have access to the underlying tables, however.

Feb 22 '08 #2
32,183 Expert Mod 16PB
This sort of question seems to come up a lot.
If I were to say that changing a setting on an individual instance of a control would be akin to redesigning a field in a table for a single record then hopefully you'll understand why it makes no sense at all.
Essentially the properties that you have access to (to change) are object properties and not instance properties. Instance meaning a single example of an object. The object being the design.
I hope this makes things a little clearer.
Feb 25 '08 #3
1,134 Expert 1GB
or in other words
on a continuous form in design mode each column has 1 and only 1 control for displaying the data. When you run the form each column has multiple copies of that same single instance of the control. Changing the properties of that single instance means each column now still only contains mutiple copies of that single instance with the changed property.

Oh and good analogy NeoPa
Feb 26 '08 #4
904 Expert 512MB
You could DISABLE an individul control in a subform (whether it is continuous or not).

Keep in mind Access allows a MAXIMUM of 4 individul control changes only.

It is better known as the FORMATCONDITION collection.

To disable an individule control you would do something like this:

Expand|Select|Wrap|Line Numbers
  1. Dim objFrc As FormatCondition
  2. ' add a single format condition to a specific control based on its value (ie: does the current value of the control = varX)
  3. Set objFrc = Me![subFormName].FormatConditions.Add(acFieldValue, acEqual, varX)
  4. objFrc.Enabled = False
Important to remember you can only do it for a maximum of 4 seperate 'controls'. So you would have to delete them or modify them to stop it from crashing.

Feb 26 '08 #5
32,183 Expert Mod 16PB
Conditional formatting (as mshmyob describes) is possible to use. If you can find a detectable condition that matches your requirements. This is still applied across the object as a whole, however it's clever enough to determine the value of each one and apply formatting conditionally.
Feb 26 '08 #6
904 Expert 512MB
Oops I made a slight mistake in my last post code.

To apply your FormatCondition to 1 and only 1 control in your subform you would change the previous code to:

Expand|Select|Wrap|Line Numbers
  1. Dim objFrc As FormatCondition
  2. ' add a single format condition to a specific control based on its value (ie: does the current value of the control = varX)
  3. Set objFrc = Me![txtField].FormatConditions.Add(acFieldValue, acEqual, varX)
  4. objFrc.Enabled = False
I changed 'subFormName' to 'txtField'. TxtField being the name of the text or combo control you want to disable.

Everything else I mentioned about only being able to apply 'different' formatconditions to only 4 individual controls still aplly.

I have done exactly what you are trying before where based on the selection in the combo box on each row of a continuous form I change the background color of a specific text box on that 1 row. It does not propogate down the whole column. I use it to just hilite a specific value based on a specific condition being met.

I will admit it took me a while to figure it out and stop it from changing my whole column.

Feb 26 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

14 posts views Thread by Gary Nelson | last post: by
8 posts views Thread by Komandur Kannan | last post: by
5 posts views Thread by payffl | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.