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

[jmartmem]

Greetings,

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!

[Stewart Ross]

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

 
Dim LockConditionMet as Boolean
LockConditionMet = (specify the condition to be tested to determine whether to lock the values; condition true = lock, false = allow updates)
me.firstcontrol.enabled = Not LockConditionMet 
me.firstcontrol.locked = LockConditionMet
me.secondcontrol.enabled = Not LockConditionMet
me.secondcontrol.locked = LockConditionMet
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.

-Stewart

[NeoPa]

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.

[Delerna]

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

[mshmyob]

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:

[CODE=vb]
Dim objFrc As FormatCondition
' add a single format condition to a specific control based on its value (ie: does the current value of the control = varX)
Set objFrc = Me![subFormName].FormatConditio ns.Add(acFieldV alue, acEqual, varX)
objFrc.Enabled = False
[/CODE]

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.

cheers,

[NeoPa]

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.

[mshmyob]

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:

[Code=vb]
Dim objFrc As FormatCondition
' add a single format condition to a specific control based on its value (ie: does the current value of the control = varX)
Set objFrc = Me![txtField].FormatConditio ns.Add(acFieldV alue, acEqual, varX)
objFrc.Enabled = False
[/Code]

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' formatcondition s 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.


cheers,