Can a Check Box Prevent Other Fields from Being Used?

[thulotjr]

Hello, I am using Access 2013.

I have a simple flat-file database. My question deals with
"freezing" or "locking" two fields in a form, so that they cannot be
selected under certain circumstances. Please allow me to elaborate.

The first field is named Eligibility, and is a drop-down menu. The
second field is Union, and is a Short Text field. I'll be using these
two fields for about 75% of the records.

But I also have a field named Exempt, which is a check box. Here's
what I want to do:

If this check box is marked, I want to prevent the Eligibility and
Union fields from being used. This is to prevent anyone entering data
from accidentally using these two fields for that particular record.

It's happened before and makes for a less organized reports and
queries. Roughly 25% of the records will have this Exempt check box
marked.

Is it possible to set up the check box to freeze/lock those two fields
if it is checked? If it is possible, then what's the easiest way to
do it?

Thank you! T. Hulot, Jr.

 

[John W. Vinson]

Hello, I am using Access 2013.

I have a simple flat-file database. My question deals with
"freezing" or "locking" two fields in a form, so that they cannot be
selected under certain circumstances. Please allow me to elaborate.

The first field is named Eligibility, and is a drop-down menu. The
second field is Union, and is a Short Text field. I'll be using these
two fields for about 75% of the records.

But I also have a field named Exempt, which is a check box. Here's
what I want to do:

If this check box is marked, I want to prevent the Eligibility and
Union fields from being used. This is to prevent anyone entering data
from accidentally using these two fields for that particular record.

It's happened before and makes for a less organized reports and
queries. Roughly 25% of the records will have this Exempt check box
marked.

Is it possible to set up the check box to freeze/lock those two fields
if it is checked? If it is possible, then what's the easiest way to
do it?

Thank you! T. Hulot, Jr.

You can't do it in a Table - tables have no usable events - but it's not hard
in a Form. You will need just a couple of bits of VBA or Macros. One would be
in the AfterUpdate event of the checkbox:

Private Sub Exempt_AfterUpdate()
Me!Eligibility.Enabled = Not Me.Exempt
Me!Union.Enabled = Not Me.Exempt
End Sub

This will set the ENABLED properties of these two controls to No if Exempt is
checked, and to Yes if it's not.

You would also need similar code in the form's Current event to handle
existing records:

Private Sub Form_Current()
Me!Eligibility.Enabled = Not Me.Exempt OR Me.NewRecord
Me!Union.Enabled = Not Me.Exempt OR Me.NewRecord
End Sub

This will enable the controls if Exempt is unchecked or if you're on the new
record.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com