The short story: data integrity
The long story:
I do this in several places in this application, and it is to ensure that
the user does not save irrelevant data or mutually exclusive pairs of data. I
do this by preventing entry of such. I have a choice: either nullify the
control & disable it when it is irrelevant or run all the same code in the
control's BeforeUpdate or AfterUpdate, along with a message box and Undo, to
notify the user that this value is not relevant (or, in fact, may be mutually
exclusive with some other piece of information) and then undo the attempt to
enter the bad data.
This is a commodity trading contract management program, so one other
feature is that almost all controls are disabled once the contract is closed
- to prevent changes to closed contracts. There are, of course, exceptions,
such as notes. So...that scenario requires that everything except the notes
control be disabled when Status = "Closed". However, even for open contracts,
there are different types of contracts - Purchase/Sale and Priced/Unpriced.
It would cause severe reporting headaches to have a sale contract with a
vendor or a purchase contract with a customer.
Etc, etc, etc.
To put it in perspective, I have 49 fields in the table underlying the form,
and all are necessary. There are certain value combinations that are mutually
exclusive, particularly several where one value should be null if another is
not.
It is also much simpler from the user's perspective to have just the
relevant controls enabled or disabled so they can tab through the form
without tabbing to a lot of controls that are irrelevant.
I like the conditional format idea; however, there will be, in some cases,
several different combinations affecting whether a particular control is
enabled.
Is the contract open or closed?
If it is open, then what is the value of ABC?
If it is closed, what is the value of XYZ?
Depending on the value of ABC, what is the value of GHI?
Based on those three answers (and there are sometimes more than three), is
the control enabled or disabled?
Thus, we could have something like this (or more) for one control:
[ABC]=True AND [XYZ]="123" and Status = "Closed" -> DEF. Enabled = True
[ABC]=False AND [XYZ]="123" and Status = "Closed" -> DEF. Enabled = True
[ABC]=True AND [XYZ]="456" and Status = "Closed" -> DEF. Enabled = False
[ABC]=True AND [XYZ]="123" and Status = "Open" -> DEF. Enabled = True
[ABC]=False AND [XYZ]="123" and Status = "Open" -> DEF. Enabled = False
[ABC]=True AND [XYZ]="456" and Status = "Open" -> DEF. Enabled = True
I know that I could compile this into two longer statements with OR
statements, but multiply this by the 49 controls on the form, and it is
virtually impossible.
Besides - I forgot to mention that my code also nullifies the values in some
of the disabled controls as it disables them and populates others that must
be a certain value as it disables them; this ensures that there are no
phantom entries in data irrelevant to the current contract details (or worse,
data that may throw off a report by filtering in or out data that could skew
totals).
Since various controls are affected by the values of more than one other
control, I relied instead on one procedure that checks everything. I just
don't like having it on the timer. It is relatively easy to call the
procedure in the AfterUpdate & Undo of each control & of the form, but he
KeyPress -> ESC gets to be a pain, especially since I have a lot of KeyPress
events for other purposes (for example, any of the four or five date-related
fields increment forward/backward by one day if the keypress on the control
is + or -, respectively.
blah, blah blah, ad infinitum... I think I spend too much time ensuring
there is no bad data in my app, but it sure does keep the users happy!
:
Hi Brian,
why do you enable and disable so many controls?
~~~
you put the conditional format on the control itself ... so, for DEF,
for instance, the expression might be:
[ABC]=True AND [XYZ]="123"
~~~
you can reference a user-defined function in the conditional formatting
expression...
functionname([fieldname1], [fieldname2],[fieldname3]) = True
it is faster, however, to reference the controls directly in the
equation than it is to call a function
~~~
"The AfterUpdate is easy,
but the Undo (and possibly KeyPress -> ESC) is more difficult."
that is why conditional formatting is nice, it is automatic <smile> ...
Warm Regards,
Crystal
Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace
*
have an awesome day
*
Brian wrote:
I gave a greatly over-simplified example for the sake of brevity. I suspect
it is too complex for a conditional format. There are several If...Then loops
and Select Case statements, including some nested ones, that determine the
enabled/disabled status of some of the controls. It goes something like this
(but about 10 times this long):
If ABC then
Select Case XYZ
Case is = "123"
DEF.Enabled = True
GHI.Enabled = False
Case is = "456"
DEF.Enabled = False
GHI.Enabled = False
Case is = "789"
DEF.Enabled = False
GHI.Enabled = True
End Select
Else
Select Case XYZ
Case is = "123"
DEF.Enabled = False
GHI.Enabled = False
Case is = "456"
DEF.Enabled = True
GHI.Enabled = False
Case is = "789"
DEF.Enabled = True
GHI.Enabled = True
End Select
End If
Not quite sure how to get that all into a Conditional Format... I can use
IIfs but that will not handle the Select Case portions. I suppose I would
have to construct separate equations for each of many combinations of factors
affecting each the enabled status of the control. For some of the controls,
this could equate to perhaps six or seven combinations. Multiply that by the
number of affected controls...
What I think I really need is a procedure that can be called automatically
on the update of any control, as well as the undo. The AfterUpdate is easy,
but the Undo (and possibly KeyPress -> ESC) is more difficult.
:
Hi Brian,
use Conditional Formatting ... one of the options is to enable or disable
click on the control
from the menu, choose Format, Conditional Formatting
Expression Is --> <your equation to test>
--> set Foreground Color, background color, Bold, Italic, Underline,
and/or enabled
Warm Regards,
Crystal
Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace
*
have an awesome day
*
Brian wrote:
I have a form on which certain controls (mostly combo boxes) are enabled or
disabled based on the value the user selects in a particular combo box (e.g.
a Vendor box is enabled and Customer box disabled if the ContractType =
"Purchase" and vice versa if ContractType = "Sale").
So...when ContractType changes, I enable/disable those two controls (these
are just two examples of probably a dozen different combinations involving
various controls, however). Rather than put the specific enable/disable with
each control, I wrote one procedure that checks the content of each of the
base controls and adjusts the enable/disable status of the related controls,
then call that procedure as necessary.
The problem is that if the user changes ContractType (thus triggering other
controls to be enabled/disabled), then presses ESC, ContractType changes back
to the original value, but the enable/disable of the related controls does
not change to match.
This means that ContractType_AfterUpdate is not a workable place to put the
enable/disable code. My solution to this point is to put all the
enable/disable code into the Form_Timer and have the Form_Timer fire every
1/2 second. Any longer than that, and the user might move into another box
before it is disabled, thus causing an error when the code attempts to
disable the box. Even so, I then had to write code to move the focus out of
any box before disabling it (moving the focus to a generic conrol that never
gets disabled).
While this works OK, it is not ideal because there are at least a dozen of
these combinations, and the enable/disable code running twice per second
tends to make the form function a little "jerkily".
I would try Form_Undo, but sometimes it is the Undo of the control, not the
form, that changes the value back. The only guaranteed solution I can think
of is to add the procedure call to the After_Update and Undo events of every
affected base control as well as the form's Undo event, and then I think I
might have to add it to the KeyPress of every base control as well, checking
for the ESC key.
Other ideas? This just makes my code seem really bulky and inefficient.