Auditing desired Change Requests to Tables WITHOUT updating the ta

I am developing some logging for a multi-user DB my colleague built. I am a
light VBA coder (self-taught).

Our Access MDB will be used for our User community to request changes to
the key system-level data of a hosted application that is not connected to
the MDB. Thus we are mimicking the Data Tables of the host system in
Access. In fact, once this becomes operational, the source of truth for
these tables will be the Access Db, not the host system.

Our team will be reviewing the Change Requests, on a field by field basis,
and either approve them or reject them. Once changes get approved, we will
apply the changes to the official records in the Db representing what the
host system should be. In other words, we do NOT want the official Access
tables changed by the users when they submit them. We just want to create
request data.


My first attempt was, when someone wanted to change an existing row's data
via a form, saving the changed record right in the main table that the
official data is in, but I would change the Primary Key to the next number in
the sequence, thus creating a brand new record with a status of 'request.' I
could not find a way to change the AUTONUMBER primary key field this way, so
I set this aside.

Then I discovered the 'Creating an Audit Log' by Allen Browne
http://allenbrowne.com/AppAudit.html
and this seemed promising. I was successful at getting the auditing to work
on a sample table I am working with. But I can not figure out a way to tweak
it to let me NOT update the underlying table and just record in the Audit
table. The details of my attempts are below.

Any ideas on how to log desired change requests without changing the
underlying data rows until the requests are approved would be appreciated,

Thanks in advance!
Allan L.


For all of following scenarios of Allen Brown's Audit Log, I commented out
the line of VBA code in the SAVE button on the form which saves the data in
the table.

I left the Audit Log code in the BEFORE & AFTER update events of the Form.
Changed rows via the form and clicked Save button, which saved the changed
data in the regular table (WHICH I DO NOT WANT), but did record the audit
data correctly.

Then With both sets of Audit Code for a Change in the BEFORE UPDATE event (I
moved the AFTER UPDATE code into the BEFORE UPDATE code) and changed a row in
the form, this still changed the official Table, WHICH I DO NOT WANT. It
created Audit records correctly.

With Both Sets of Audit Code in SAVE BUTTON and nothing in the form's
Before/After Update events, I changed a row 7 This again changed the
official Table, WHICH I DO NOT WANT. It created Audit records but the change
are NOTreflected in the 'EditTo row.

And by not using the real SAVE command to create a new row in the primary
table when the user wants to request a new row, I can not use this audit
feature as the audit rows refer to the real table's primary key.