Auto change the value/data of control base on another control

[moi]

Hello all,

How will i auto change the status confrol from "Active" to "Fully Paid" if my balance control value is "0"..my balance control is a calculated control in my main table..

Thank you all.

 

[GaP42]

Perhaps using an iif statement in the status control (expression builder) itself:

=IIf([IsPd]=-1,"Fully Paid","Not Fully Paid")

where you use iif txtBalance = 0 ?

 

[The_Doc_Man]

The way you explained that leaves ambiguities. Are you talking about a form with a control that contains a calculation? Or is the calculation due to having a calculated field in a table? You mixed contexts in your question so it might be hard for us to answer without clarification.

Not that it CAN'T be answered, but at least I am not sure which of two or more ways to answer. So tell us more about the situation.

 

[moi]

Hi the-doc-man,

The calculated contorol is based on 2 controls, 1) TCP is from table which the form source, 2) SUM control from a subform within the form.

Now the control i would like to based the auto change is "balance"..

My appologies guys if i can't explained well..

 

[moi]

Perhaps using an iif statement in the status control (expression builder) itself:

=IIf([IsPd]=-1,"Fully Paid","Not Fully Paid")

where you use iif txtBalance = 0 ?

Hi,

How would i use this, i dont want to remove the default value "Active" of the Status control if the balance is not = to "0" zero.

 

[GaP42]

Assume your StatusControl displays either "Active" or "Fully Paid" (a control bound to the data item "Status") and you have a control Balance. Balance is a calculated value and may display values >0, <0 or 0. When the Balance = 0 then the StatusControl should display "Fully Paid".
If this is the situation, then you need to update the value of the item "Status" when Balance = 0. That could be achieved in the BeforeUpdate event of the form where Balance is shown:

if Balance = 0 then
  Status = "Fully Paid"
else
 Status = "Active"
end if

When the record is updated, immediately after the successful completion of the BeforeUpdate event of the form, the record will show the appropriate Status in the StatusContol

If, on the otherhand, your StatusControl is an unbound control, the display can show either "Active" or "Fully Paid" depending on the value of Balance. In the unbound StatusControl, enter the following as the ControlSource:

=IIf([Balance]=0,"Fully Paid","Active")

The value of the StatusControl is not saved to the Status, unless you take further steps similar to the above.

 

[moi]

Assume your StatusControl displays either "Active" or "Fully Paid" (a control bound to the data item "Status") and you have a control Balance. Balance is a calculated value and may display values >0, <0 or 0. When the Balance = 0 then the StatusControl should display "Fully Paid".
If this is the situation, then you need to update the value of the item "Status" when Balance = 0. That could be achieved in the BeforeUpdate event of the form where Balance is shown:

if Balance = 0 then
  Status = "Fully Paid"
else
Status = "Active"
end if

When the record is updated, immediately after the successful completion of the BeforeUpdate event of the form, the record will show the appropriate Status in the StatusContol

If, on the otherhand, your StatusControl is an unbound control, the display can show either "Active" or "Fully Paid" depending on the value of Balance. In the unbound StatusControl, enter the following as the ControlSource:

=IIf([Balance]=0,"Fully Paid","Active")

The value of the StatusControl is not saved to the Status, unless you take further steps similar to the above.

Thank you very much GaP42, well explain.. Maybe your first code is the suitable one, because my Status control is bound..

Again thank you..

 

[Pat Hartman]

It is poor practice to save two data fields that have the same meaning. You can always tell the status by checking the Balance. Keeping the Balance in itself is a violation because the Balance represents the sum of other data so just try to limit your design to the minimum number of poor design choices.

 

[GaP42]

It is poor practice to save two data fields that have the same meaning. You can always tell the status by checking the Balance. Keeping the Balance in itself is a violation because the Balance represents the sum of other data so just try to limit your design to the minimum number of poor design choices.

@moi indicated that Balance is a calculated control - which may not be stored. Not sure in this particular case, but it is sometimes the case that the calculated extended price is stored as it is calculated on perhaps transient values - eg a discount rate that may be subject to later change. The determination of a Balance and "Fully Paid" status are both determined by calculation - retaining either or one or the other is based on assessment of the overhead.

 

[Pat Hartman]

As we all know, the storing of calculated values leaves you open to data anomolies. You need code in a number of places to handle this so that whenever one of the underlying items changes, the total is always updated. Not everyone gets this right. Also, people who take over the application at a later time might not realize that this violation exists and not cater to it.

Put lots of comments everywhere so that no one will miss them.