Undo functions

[Brian]

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.

 

[strive4peace]

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]

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

 

[strive4peace]

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]

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
*

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

 

[strive4peace]

Hi Brian,

"49 fields in the table underlying the form"

with all due respect, most tables with 30 fields have too many ... this
is an indicator that your data needs to be normalized. Read the Access
Basics document in my siggy ...


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

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
*

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.

 

[John Spencer]

I don't know, since I have never used this, but a form does have an UNDO Event
(at least in 2003). Could you use that event to run the code or at least set
a flag that can be used by the timer event to decide whether or not to run the
code in the timer event.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Scott Shearer]

How about using the KeyPress event and trapping when the esc it pressed?

 

[Paolo]

Hi Brian,
on the keydown event of your combos you can add this code

If KeyCode = 27 Then
KeyCode = 0
End If

In this way you trap and neutralize the esc key and you avoid the roll back
to the original value of the combo.

HTH Paolo

 

[Brian]

Hi, Crystal.

I would respectfully disagree concerning normalization. I think I am
extremely meticulous concerning data structures. Commodity trading is a very
complex business, and it required 42 tables to correctly separate the data
elements; even so, this table (the primary Contract table) has 49 fields and
the next less complex one has 25.

For one contract, there are indeed 49 discrete pieces of information that
uniquely describe that contract. Nothing is stored redundantly in the
database, and no combination of fields can be used to determine the value of
any other field(s). For example, I store the VendorID (vendor company) and
VendorContactID (individual within the vendor company) with the contract;
while one might infer that I could just store the VendorContactID and pull
the vendor company information by its relation to the contact within the
company, the problem is that the vendor is required on the contract while the
individual contact information is optional, and the RowSource of the
VendorContactID control is filtered to the VendorID, making it imperative to
allow storage of both, for a number of reasons.

I do not see how I can reduce the number of fields without creating multiple
forms to handle contracts that are identical but for one or two fields. That
is, there are fields that apply to one contract type but do not apply to
another; however, the amount of work involved in designing separate forms for
different contract types (including propagating/synchronizing those vast
portions of code that apply to the common elements all contract types of both
versions of the form) far outweighs the advantage of having separate tables
for different contract types (to say nothing of the difficulties involved in
reporting from multiple similar tables and having the users see different
form layouts for different contract types). Furthermore, there are different
types of contract types: both sales & purchase contracts can be priced or
unpriced. That is at least four combinations that have mostly the same
fields, but, for example, Priced contracts do not require a PricingBoardID,
so I disable the control unless the contract is Priced

I know I could tab the form, but that is purely about screen real estate and
aesthetics - it does nothing to reduce the number of fields needed to
represent a single contract.

Except for quantity & date fields, all are drawn via combo boxes from other
"masterfile" tables, so what is actually stored in this table is the PK of
the foreign table in the relationship, not the text of the fields. All the
table relationships have referential integrity enforced. Here are a few
examples,

ContractID (PK of this table)
Status
Vendor company ID
Vendor contact ID
Vendor contract #
Customer company ID
Customer contact ID
Customer contact #
Contract date
Contract shipment begin date
Contract shipment end date
Quantity
UnitPrice
UnitID
ShippingUnitID
QuantityShippingUnits
CommodityID
Contract Type
CloseCode
CloseDate
PricingStatus
PricingBoardID
MasterContractID
Notes (appear on the printed contract)
Remarks (private, do not print with contract)
....etc. (that's the first half of them)

Now, if I were, for example, to make a ContractNotes table containing the
ContractID & associated Notes memo fields, I would lose the ability to have
it be a bound control and create yet another headache for myself by having to
both load the content of the Notes field to an unbound text field when the
user navigates to the contract as well as saving the contents when form is
updated by any of the many methods (navigating to a new record, closing the
form, etc). Besides, even if I had many unbound fields loaded from other
tables, none of that would help reduce the need to enable or disable those
controls to prevent irrelevant or conflicting data from being stored

Thanks.

Hi Brian,

"49 fields in the table underlying the form"

with all due respect, most tables with 30 fields have too many ... this
is an indicator that your data needs to be normalized. Read the Access
Basics document in my siggy ...


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

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.

 

[Brian]

I know I can use the KeyPress. The problem was that I use the form's KeyPress
event so rarely that I forgot that I have to set the form's KeyPreview to
True to get the form's KeyPress event to fire so I don't have to propagate
the procedure call to the KeyPress event of each control! I just remembered
when I looked at these posts again.

Thanks.

 

[strive4peace]

Hi Brian,

for this scenario:

[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 would make a user-defined function something like this:

'~~~~~~~~~~~~~~~~~~~~~~
Function EnableDEF(pABC as boolean, pXYZ as string, pStatus as string)
as boolean
select case True
case pABC=True AND pXYZ="123" and pStatus = "Closed"
EnableDEF = True
case pABC=False AND pXYZ="123" and pStatus = "Closed"
EnableDEF = True

' more case statements

case else
EnableDEF = True 'or whatever is the default value
end select
End Function
'~~~~~~~~~~~~~~~~~~~~~~

then, in the Conditional Formatting expression for DEF:

EnableDEF([ABC],[XYZ],[Status]) = False
format --> not enabled

"ESC gets to be a pain"

using conditional formatting will take that into account <smile>

-- even if you DID test for ESC on a keypress event, you would need to
test it for EACH control because a previous change will be undone if the
user presses ESC in a control that was not changed

~~~~~~~~~~~~~~`

I would also use a separate table for the Statuses with an autonumber
StatusID and text Status. Store a Long Integer StatusID in related
tables. While this does not cut down on the number of fields, it is
generally better to do it this way.

~~~~~~~~~~~
"I just don't like having it on the timer."

good idea! I find that form timer events can lead to crashing
(especially if the form is open and has no activity for long periods of
time) ... I avoid them whenever possible.

~~~~~~~~~~~

"Now, if I were, for example, to make a ContractNotes table containing
the ContractID & associated Notes memo fields, I would lose the ability
to have it be a bound control"

Not true; read the Mainform/subform section of Access Basics ...

~~
"It would cause severe reporting headaches to have a sale contract with
a vendor or a purchase contract with a customer. "

if you used subforms, you could make them visible or not -- and use code
to clear data if a section had been showing that you will now hide when
the field that determines this is changed

~~~~~~~~~~~~~

"I have 49 fields in the table underlying the form, and all are necessary"

not saying they are not ... just suggesting that perhaps this table
should be separated into multiple tables.

While I can appreciate your view -- most people do not want to change
structure anyway -- especially if they have already set up forms and
reports (but what if you already built a house and there is a crack in
the foundation...? of course you have to fix it or the house will
eventually fall)

I see multiple tables in what you have shown. You have closing and
pricing information in the same table as Contract information. Also, is
not some of the information here also in the Master Contract? Why would
Commodity be in this information? Would that not be in the detail lines
so that you could have a contract that covered multiple commodities?

Unless I studied your database and knew your business better, I could
not, of course, know for sure -- I am just guessing based on working
with thousands of databases in my career.

I developed a contract database for an oil and gas company and while
that is not the same as your business, it did give me a lesson in the
diversity of information that can be on a contract.

~~~

why do I care?

I would like to get to the root of the problem -- to the REASON you need
to hide/disable so many controls... if the data was structured
differently, much of this would not be necessary -- and when it still
is, the rules might be simpler -- such as when you "Close" a contract.

"prevent changes to closed contracts"

In that case, you could put "Status" in the Tag property of all the
controls that need to be disabled if a contract is closed and, on the
form Current event, and the AfterUpdate event of Status:

=EnableUnEnableDetailControlsTag([Form],
iif(nz([Status],"")="Closed",false,true), "Status", [controlname])

WHERE
controlname is a control you would NOT disable so that focus can be
switched

here is code in a standard module:

'~~~~~~~~~~~~~~~~~~~~~~~~~~~
Function EnableUnEnableDetailControlsTag(pForm As Form _
, pBoo As Boolean _
, pTag As String _
, Optional pControlFocus As Control _
) As Byte

Dim ctl As Control

'move focus if we are disabling
if not pBoo then
If Not pControlFocus Is Nothing Then pControlFocus.SetFocus
end if

On Error GoTo Proc_Err

' because Detail is used,
'this is limited to controls in the detail section

For Each ctl In pForm.Detail.Controls

If InStr(ctl.Tag, pTag) > 0 Then
ctl.Enabled = pBoo
End If

Next ctl

Proc_Exit:

Set ctl = Nothing
Exit Function

Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " EnableUnEnableDetailControlsTag"

Resume Proc_Exit

'if you want to single-step code to find error, CTRL-Break at MsgBox
'then set this to be the next statement
Resume

End Function
'~~~~~~~~~~~~~~~~~~~~~~~~~~~

If you want to send me your database, I will run my analyzer on it and
send you back the reports.

(e-mail address removed)


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Hi, Crystal.

I would respectfully disagree concerning normalization. I think I am
extremely meticulous concerning data structures. Commodity trading is a very
complex business, and it required 42 tables to correctly separate the data
elements; even so, this table (the primary Contract table) has 49 fields and
the next less complex one has 25.

For one contract, there are indeed 49 discrete pieces of information that
uniquely describe that contract. Nothing is stored redundantly in the
database, and no combination of fields can be used to determine the value of
any other field(s). For example, I store the VendorID (vendor company) and
VendorContactID (individual within the vendor company) with the contract;
while one might infer that I could just store the VendorContactID and pull
the vendor company information by its relation to the contact within the
company, the problem is that the vendor is required on the contract while the
individual contact information is optional, and the RowSource of the
VendorContactID control is filtered to the VendorID, making it imperative to
allow storage of both, for a number of reasons.

I do not see how I can reduce the number of fields without creating multiple
forms to handle contracts that are identical but for one or two fields. That
is, there are fields that apply to one contract type but do not apply to
another; however, the amount of work involved in designing separate forms for
different contract types (including propagating/synchronizing those vast
portions of code that apply to the common elements all contract types of both
versions of the form) far outweighs the advantage of having separate tables
for different contract types (to say nothing of the difficulties involved in
reporting from multiple similar tables and having the users see different
form layouts for different contract types). Furthermore, there are different
types of contract types: both sales & purchase contracts can be priced or
unpriced. That is at least four combinations that have mostly the same
fields, but, for example, Priced contracts do not require a PricingBoardID,
so I disable the control unless the contract is Priced

I know I could tab the form, but that is purely about screen real estate and
aesthetics - it does nothing to reduce the number of fields needed to
represent a single contract.

Except for quantity & date fields, all are drawn via combo boxes from other
"masterfile" tables, so what is actually stored in this table is the PK of
the foreign table in the relationship, not the text of the fields. All the
table relationships have referential integrity enforced. Here are a few
examples,

ContractID (PK of this table)
Status
Vendor company ID
Vendor contact ID
Vendor contract #
Customer company ID
Customer contact ID
Customer contact #
Contract date
Contract shipment begin date
Contract shipment end date
Quantity
UnitPrice
UnitID
ShippingUnitID
QuantityShippingUnits
CommodityID
Contract Type
CloseCode
CloseDate
PricingStatus
PricingBoardID
MasterContractID
Notes (appear on the printed contract)
Remarks (private, do not print with contract)
...etc. (that's the first half of them)

Now, if I were, for example, to make a ContractNotes table containing the
ContractID & associated Notes memo fields, I would lose the ability to have
it be a bound control and create yet another headache for myself by having to
both load the content of the Notes field to an unbound text field when the
user navigates to the contract as well as saving the contents when form is
updated by any of the many methods (navigating to a new record, closing the
form, etc). Besides, even if I had many unbound fields loaded from other
tables, none of that would help reduce the need to enable or disable those
controls to prevent irrelevant or conflicting data from being stored

Thanks.

Hi Brian,

"49 fields in the table underlying the form"

with all due respect, most tables with 30 fields have too many ... this
is an indicator that your data needs to be normalized. Read the Access
Basics document in my siggy ...


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

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.