Saving Original Record when Revising

[Brian Carlson]

I have created an invoice table (t_PCO) with numerous related tables
(t_PCOEquipment), (t_PCOLaborHours), (t_PCOMaterial). Often these invoices
are submitted to customers only to be returned with revisions. I am trying
to make it possible that one can choose to revise these invoices rather than
overwrite them and in addition I would like to be able to track the changes
made. Currently, I am using the invoice number as the primary key for the
main table and I believe that I could add a RevisionNum field which would be
used with the invoice number as the primary field, thus allowing me to group
these later. I am wondering if there is a way which I can: (1) make a copy
of all of the records from the various tables that is related to a particular
invoice and then (2) have all of these copies of the original records have
their RevisionNum field adjusted by 1. I believe that this approach would
work, but suggestions are very welcome. Please be forewarned; I am an
extreme novice. Thank you in advance.
Brian

 

[Allen Browne]

Access does not provide an easy way to do this, Brian.

It is possible to use the Form events to create a copy of the record in a
logging table. Here's how:
Audit Trail - Log changes at the record level
at:
http://allenbrowne.com/AppAudit.html

But it's not as simple as a revision number field. Say you have an invoice
made out to Fred Nerks, with 2 rows (line items) in the related table.
Possibilities include:
a) User adds another line item.
b) User deletes a line item.
c) User edits a line item (including reassigning it to a different invoice?)
d) User edits the header record (including reassigning it to a different
client, which affects all line items.)
e) User deletes header record (which deletes the associated line item(s) as
well.)

I'm not sure which of these constitute a revision number in your thinking.

 

[Brian Carlson]

Allen:

In regards to this log, none of the records associated with one invoice
would ever be reassigned to a different invoice. I may delete, edit or add
records to the tables related to the main invoice table. After reading your
response and the attached link, I began to parse down the actual information
I am interested in tracking across invoices. I really do not need to track
the individual items contained in each related table, but rather only the
totals from each of the subforms. That is, I am not interested in what has
changed but rather only the numerical difference. Would it work to put
fields on the main form that store the totals received from each subform and
then any revisions to an invoice would simply have to be treated as
completely new records with the same invoice number and different revision
number. It seems that I would then be able to compare each invoice and its
revisions? This one may be a bit over my head at this point in time. Thanks
for your help.

Brian

 

[Allen Browne]

I think it would be better to audit both (main table and related table)
rather than store a total.

If you just have a total, you have no way of knowing what that number means,
or how it was made up, or even if it is correct. If you log the related
records as well, you have a verifiable and meaningful log.

 

[Brian Carlson]

Allen:
Thank you for your assistance. I will read your article more
throughly and try to mess around with this on a copy of my database to see if
I can master it. Thanks for the help.

Brian

 

[Fred]

I know only 1% of what Allen does on Access.

But I do run companies, and where I live what you are trying to do is very
unusual (some companies may even consider it improper) and may be making
your life overly complicated. Normally you would consider an invoice to be
a one-time event, "frozen" thereafter. If you send it again, it is a COPY.
And make changes via a second debit/credit "invoice". The net result is
reflected in STATEMENTS of the account which combine the above.

 

[Brian Carlson]

Fred:
I should probably clarify. These are not invoices proper, but rather
billing proposals. They are invoices, given that the work was already
performed, but the owner has the option of how they are billed for the charge
and all billings are negotiable. This is because we have a contract with
these owners to do work (these invoices are always paid as billed), but
sometimes extra work comes up that must be done to continue our contract
work. The owner does not want our contract work to be held up by these
items, so we proceed with them and us documenting what was done. We then
send them these invoices, which they can approve as is, or they ask us to
revise if their is a discrepancy with their records. I do not know if any of
this information will assist in addressing my question, but in either case,
thank you for the input.

Brian

 

[Fred]

Hello Brian,

Your post establishes that your current practice is fine with your
customers.

So, the rules/ practices that I described are not needed in your business
scenerio.

But, in my opinion, they still might be the easiest / best way to
accomplish what you are trying to do, and also provide a solid foundation for
dealing different future issues.

 

[Brian Carlson]

Fred:
I wish it was easy to implement tne changes you suggested, in either case,
thank you for your assistance.

Brian