Missing OnLeaveRecord event driving me mad!

[Atlas]

Here why:

Form (table A) with subform (table B,) 1 to N relationship;

(A) - ID_A - Description - Total

(B) - ID_A- ID_B - Description - Amount


A-> Total must be as the sum of (1 to N) of B->Amount

1) Stuffing checks in the mainform afterupdate doesn't help a lot, cause
users can change amounts in the subform without touching the record in the
mainform

2) Using Allen Brown's tip to hold last ID in main record and check if it
changes in the OnCurrent event, gives unpredictable results.

3) Enforcing recordcount > 1 on (B) doesn't allow user to move to the
subform to enter data.

What a mess!

Help appreciated

P.S. - Really don't understand why designers of Access left out such an
important event........

 

[Dirk Goldgar]

Here why:

Form (table A) with subform (table B,) 1 to N relationship;

(A) - ID_A - Description - Total

(B) - ID_A- ID_B - Description - Amount


A-> Total must be as the sum of (1 to N) of B->Amount

1) Stuffing checks in the mainform afterupdate doesn't help a lot,
cause users can change amounts in the subform without touching the
record in the mainform

2) Using Allen Brown's tip to hold last ID in main record and check
if it changes in the OnCurrent event, gives unpredictable results.

3) Enforcing recordcount > 1 on (B) doesn't allow user to move to the
subform to enter data.

What a mess!

Help appreciated

P.S. - Really don't understand why designers of Access left out such
an important event........

I don't follow why this is an issue; could you explain in more detail?
Can I take it that this is not a case where you are trying to
*calculate* A.Total from the sum of B.Total, but rather are starting
with a value for A.Total and want to require that entries be made in B
to add up to the specified total? That's the only case that makes sense
to me. If you were just trying to calculate A.Total as the sum of
B.Total, it would seem unnecessary and counterproductive, the sum of
B.Total can always be calculated and displayed whenever you want it.

 

[Rick B]

Not sure I follow any of your message, but your subject would indicate that
you are trying to do something as you leave a record (move to the next
record). Would OnCurrent not meet your needs? Of BeforeUpdate?

Rick B


Here why:

Form (table A) with subform (table B,) 1 to N relationship;

(A) - ID_A - Description - Total

(B) - ID_A- ID_B - Description - Amount


A-> Total must be as the sum of (1 to N) of B->Amount

1) Stuffing checks in the mainform afterupdate doesn't help a lot, cause
users can change amounts in the subform without touching the record in the
mainform

2) Using Allen Brown's tip to hold last ID in main record and check if it
changes in the OnCurrent event, gives unpredictable results.

3) Enforcing recordcount > 1 on (B) doesn't allow user to move to the
subform to enter data.

What a mess!

Help appreciated

P.S. - Really don't understand why designers of Access left out such an
important event........

 

[Albert D. Kallal]

First, why are you storing the total in table A anyway?

If you force your design to have to store the total, then EVERY SPOT in you
application that can update, delete, modify or add new records to table B
will requite code? Why do this ? You will wind up spending more time writing
update code then will developing the application. A good developer will
NEVER store such values as a invoice total etc. What you do is build a query
to sum, or return the total for a given customer or whatever, and then use
that in your reports etc.

What is truly fabulous about the above approach is that then NO code is
need. What happens if a user hits ctrl alt del, or if the computer locks up,
or even if a error occurs in your code..and the total/sum is not correct?
You then will wind up writing some code routine to go through all entry's
and "fix" the total for you (in other words...you start writing the code and
queries to return the total value anyway because it becomes some much
trouble to write PERFECT code that always updates the totals correctly).

What is even more incredible here is that then code to add records, or
delete records can be written FREELY through out your whole application, and
you NEVER worry about having to update some total.

Modern designs, and every database book on the planet will tell you NOT to
store those kinds of values when you can calculate them..as then you DON'T
EVEN HAVE to write ANY code to update the totals!

Anyway, lets assume that you need to torture your self, and in fact DO want
the total to be stored in the parent record/table.

Any reason you don't use the after update event of the sub form?

In you "main" parent form, you can make a routine like:

Public Function SetTotal

Dim curTotal As Currency

curTotal = Nz(DSum("PayAmount", "tblInvoiceDetails", "main_id = " &
Me.ID), 0)

Me!InvoiceTotal = curTotal

end function


Now, in the sub-forms after update event, you simply go:

me.Parent.SetTotal

And, you should also call the SetTotal when you delete a record in the
sub-form also.

And, of course, if you every write any other code, or import data, or do
anything else, then you will need to write some code to ALWAYS update that
total. This as mentioned will cost you lots of wasted time..when you can use
ZERO code, and simply display/calculate the values as you need them....

If you take a look at the following complex form, you can see the totals at
the bottom of the form, but I simply used the =sum() command in the forms
footer will automatically calculates the totals for me..but I never actually
store the values as the code would be WAY too complex to always maintain the
correct values..

Scroll down tot he last screen shots here to see what I mean:

http://www.attcanada.net/~kallal.msn/Search/index.html

And, some notes on the above approach where I did NOT store totals can be
found here:

http://www.attcanada.net/~kallal.msn/Articles/PickSql/Pick6.html

 

[Atlas]

I don't follow why this is an issue; could you explain in more detail?
Can I take it that this is not a case where you are trying to
*calculate* A.Total from the sum of B.Total, but rather are starting
with a value for A.Total and want to require that entries be made in B
to add up to the specified total? That's the only case that makes sense
to me. If you were just trying to calculate A.Total as the sum of
B.Total, it would seem unnecessary and counterproductive, the sum of
B.Total can always be calculated and displayed whenever you want it.

Dirk, here's how it works:

Table (A) holds info about financial transactions done either from bank or
cash or both. So just to be more exact,

(A) ID_A, Date, Description , Total, Bank_credit, Bank_debit, Cash_credit,
Cash_debit

Obviously it makes sense to stuff values in table A when data is complete in
table B:

(B) ID_A, ID_B, Description, credit, debit

that holds details about the financial transaction of A (1:N) B.

As data is manipulated in a continuos form (A) that holds in the footer a
continuos subform (B) thats how it should work:

User creates a new record in A, puts the date, the description and then is
automaticaly moved down in the subform (B) where it is forced to create
detail records (at least one).

Once done, it is allowed to go back to A and put the proper values in the
bank or cash account.

Holding a total is useful here because it avoids issueing a CALC query on
the subform continuosly.

There are quite a lot of conditions to look for..........that's why I'm
missing a proper onleaverecord event.

 

[Atlas]

Rick I've posted more details answering to Dirk.....

 

[Atlas]

Albert thanks for answering.
I'm giving more details in an naswer to Dirk.

Anyhow, what you say sounds perfect. Never store calculated values.

But sometimes you need to do so. What if you have very long recordsets on a
datasheeet where every record can have multiple "childs"? Acces is quite
famous not to be fast; so can you end issueing calc queries continuosly?
What will happen when the database grows?

I can imagine that in the end, the time you save avoiding what you call the
perfect code, you spend it in optimizing speed of your code, maybe limiting
rowsets dimensions and managing navigation in the rowset, and maybe still
having performance issues.

I may need some code to scan the entire db and check if the totals are
correct. This can be done easily. But I can also try to speed up data
management with "helpers". But Access event model doesn't seems to be so
helpful on this side.....

 

[Albert D. Kallal]

You are correct, there is some compromise here. Less code, easier to
use..can mean a slow down.

This is kind why I posted a solution that I think will work quite well for
you....

 

[Atlas]

Ok you, got my on the right way.....I'm pulling out the total from the table
and calcultate it @ runtime. I still try to enforce checks to be sure users
don't miss something.
Also now when printing the report, I perform running sums and check if data
in the master table conforms to tals in the details table. It works.

Bye!