Update field

[myxmaster]

Hello,
I posteed this question a few weeks afo and Allen was kind enough to
reply. However due to my limited knowledge I am still stumped. Any
further help is most appreciated

On my form I have a field called amount and a field called Check
status. The amount signifies a check amount and status signifies
pending, returned or cleared. Is it possible that when cleared is
selected a calculation will automatically reflect the
change .Example:

Available Balance $5000


Check Amount Status
$3000 Pending


Balance reflects $2000 available


Check Amount Status
$3000 Cleared


Balance reflects $5000

 

[John W. Vinson]

Hello,
I posteed this question a few weeks afo and Allen was kind enough to
reply. However due to my limited knowledge I am still stumped. Any
further help is most appreciated

On my form I have a field called amount and a field called Check
status. The amount signifies a check amount and status signifies
pending, returned or cleared. Is it possible that when cleared is
selected a calculation will automatically reflect the
change .Example:

Available Balance $5000


Check Amount Status
$3000 Pending


Balance reflects $2000 available


Check Amount Status
$3000 Cleared


Balance reflects $5000

You're assuming knowledge that we lack.

Where is [Balance] stored? This table? Some other table?

Please explain the structure of your tables and indicate what's where. An
Update query launched from the form where you change the Status will be the
way to do this, I'm guessing, but without knowledge of *what* needs changing
it's impossible to suggest how that might be written.

Another possibility is to *not store the balance AT ALL* - instead, to
calculate it on demand using a Sum of all cleared transactions.

John W. Vinson [MVP]

 

[myxmaster]

Hello,
I posteed this question a few weeks afo and Allen was kind enough to
reply. However due to my limited knowledge I am still stumped. Any
further help is most appreciated

On my form I have a field called amount and a field called Check
status. The amount signifies a check amount and status signifies
pending, returned or cleared. Is it possible that when cleared is
selected a calculation will automatically reflect the
change .Example:

Available Balance $5000

Check Amount Status
$3000 Pending

Balance reflects $2000 available

Check Amount Status
$3000 Cleared

Balance reflects $5000

You're assuming knowledge that we lack.

Where is [Balance] stored? This table? Some other table?

Please explain the structure of your tables and indicate what's where. An
Update query launched from the form where you change the Status will be the
way to do this, I'm guessing, but without knowledge of *what* needs changing
it's impossible to suggest how that might be written.

Another possibility is to *not store the balance AT ALL* - instead, to
calculate it on demand using a Sum of all cleared transactions.

John W. Vinson [MVP]- Hide quoted text -

- Show quoted text -

My apologies John,
Of course I am looking at what you cannot see. Hopefully this will
provide more insight

Structure as follows:
TransactionTbl

TransactionID
Account Number
Transaction Date
Transaction Type - This is a lookup based on transaction type table
( Deposit/ Withdraawal)
Transaction Description - This is a lookup based on transaction
description table ( Check/ Credit/ ) similar to visa, mastercard,
discover etc
Check Number
Check Status - This is a lookup based on check statustbl ( Cleared/
Pending/ Redeposited/ returned)

I have another table called Limit
LimitTbl
Transaction Type
Limit

I have a form called Transactions and Availability
This has the fields from the transactionstbl and the limittbl.

I have a calculation in the frms footer that sums the total of the
check amounts.

I have an unbound field on the frm that shows the available balance.
This is derived by subtracting the sum total in the footer from the
Limit field.

I hope this will help you help me.
TIA

 

[Ken Sheridan]

What your cheques represent are credits to the account. I assume the Amount
column (field) is also used to record debits (otherwise, in the absence of a
separate debits column, you can't compute the balance unless nothing is ever
withdrawn!), so to compute the current balances you'd need another column in
the table, TransactionType say, with possible values "Debit" or "Credit".
The overall current balance is the sum of all credits less the sum of all
debits; the overall current available balance is sum of all credits where
Status = "Cleared" less the sum of all debits.

You can compute these either in the form's underlying query, or in unbound
computed controls on a form. The expressions are much the same in each case,
so using unbound computed controls on a form the ControlSource of a text box
to show the total current balance would be:

=DSum("Amount", "YourTable", "TransactionType = ""Credit""") -
DSum("Amount", "YourTable", "TransactionType = ""Debit""")

For the current available balance it would be:

=DSum("Amount", "YourTable", "TransactionType = ""Credit"" And Status =
""Cleared""") - DSum("Amount", "YourTable", "TransactionType = ""Debit""")

Note that the balances won't be updated until the current record is saved.
If you want them to be shown immediately on selecting the Status then in the
AfterUpdate of the Status control on the form save the current record and
call the Recalc method to update the computed control(s):

RunCommand acCmdSaveRecord
Me.Recalc

What you should not do is store the balances in a separate column in the
table. As they can be computed from the values in the other columns that
would introduce redundancy and leave the table at risk to update anomalies,
i.e. there would be nothing to stop a value in one or more of the Amount,
Status or TransactionType columns being changed without the value in the
balance column being changed accordingly, this leaving the data in an
inconsistent state. Computing the balances at runtime, however, guarantees
that the values returned are always correct.

I've assumed above that you want the current balance rather than a running
balance per transaction. The latter requires a slightly more complex
expression.

Ken Sheridan
Stafford, England