File not updateing

M

Mike Painter

I have two forms "Charges" and "Payments" that update another table in the
forms BeforeUpdate.
They add the existing value if it is a new record and if not they add the
difference between the old and new values.

They work. I can't cause an error even if I do things I shouldn't without
turning off the computer at just the right time.
Both update two fields in a record, a current value and a "forever" total.
The forever total never seems to be wrong.
The records the form updates show correctly in a subform the form that the
totals show up in.
Originally an older Access program it was just updated to the latest
version

They report that several times a month (400 -500 entries) errors crop up.

I think they are doing something wrong but don't have any clues and have run
out of ideas on ways to cause the error.

Any ideas?

The forever total idea I took from McDonald's. Their in store computers
keep a total on everything they sell. A special piece of equipment is needed
to gain access to these totals.
Technicians have been told that if they leave the device and go have lunch
someplace else a new corvette will be waiting when they get back.
PS McDonalds makes little to negative profit on their burgers. The money is
in the other stuff.

PPS They track employee time to the 1/100 th of an hour.
 
A

Allen Browne

Mike, you gave us no valid reason why you should be storing this total.

What happens if the update fails, e.g. because of a conflict with another
user? Are you handling deletes as well? Can you guarantee that neither you
nor anyone else who will maintain this database in the future will ever
modify the data except through this form (e.g. by running an append or
update query)?

There are cases where a stored total is the only practical solution, but if
you are forced into one of those scenarios, you cannot just rely on the
currently stored value being correct. You need to either recalculate the
value with each change, or else provide some kind of periodic recalculation
so that the errors are ultimately self-correcting instead of getting further
and further out as time goes on. IMHO, it is completely unacceptable to use
an algorithm where you just hope it's right and have no way to trace the
history and correct the number or even demonstrate that it is correct.
 
M

Mike Painter

Allen said:
Mike, you gave us no valid reason why you should be storing this
total.
What happens if the update fails, e.g. because of a conflict with
another user? Are you handling deletes as well? Can you guarantee
that neither you nor anyone else who will maintain this database in
the future will ever modify the data except through this form (e.g.
by running an append or update query)?

There are cases where a stored total is the only practical solution,
but if you are forced into one of those scenarios, you cannot just
rely on the currently stored value being correct. You need to either
recalculate the value with each change, or else provide some kind of
periodic recalculation so that the errors are ultimately
self-correcting instead of getting further and further out as time
goes on. IMHO, it is completely unacceptable to use an algorithm
where you just hope it's right and have no way to trace the history
and correct the number or even demonstrate that it is correct.
Click to expand...

There are no other users and will not be.
There will never be any valid reason for updating the tables without using
the forms. There will never be any way to append or update the tables with a
query.
Deletes are not allowed for audit trail reasons and if the user did delete a
record it would not matter since the current state of the database can be
used to recover from deletions in one table. Deletions in both tables would
require restoring a back up.

As stated in the original, the forms handle correctly the case where a
record is recalled and changes made to it. This is "forbidden" since a new
record should be used for audit trail purposes.

I don't just hope it's right. I've spent many hours trying to find out why
over a ten year period it seems occasionally wrong in one place and never
wrong in another using the same algorithm I've used for more than 20 years
in DOS and Windows and more than one database. Errors only occur in this
application and span two or more computers, versions of Windows and of
Access.
One reason for two tables is that it makes audit trails easier (trace
history)
 
Top