Auditing desired Change Requests to Tables WITHOUT updating the table

[allanBOS]

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 to use the next value in the sequence 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 when user changes the row in the form. 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, either by changing the AUTONUMBER Primary key in the form or by using the Audit Logs, without changing the underlying data rows until the requests are approved, would be appreciated. If I can not change the AUTONUMBER Primary key in the form I know I need a separate table to store the "requests" and the Audit Log table seems like a great way to do this since it would be easy to find which fields changed.

Another possibility is comparing the Textbox's OldValue to Current value and if changed, writing the details of change to a change table, but (Me.TextboxName.OldValue), but that requires a lot of control-by-control coding. Which is why using the Audit Log from Allen Browne was so desirable to use

Thanks in advance!
Allan


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.



When above did not work, I then tried both legs of the audit code in the before update event which then had line

Cancel = True

after to stop the update to the table, but that did not work correctly. It does prevent update to the main table. Unfortunately, the new EditTo row in Audit log does not include the changes the user enters, it is same as EditFrom record for changed fields.


So the pseudo-code I need in the Before Update event of the form -- in order to use the Audit Log from Allen Browne is:

BEFORE UPDATE event

IF a changed row THEN

Run the Allen Browne Audit log in a tweaked way which will work without changing the table in the form

PREVENT THE CHANGE TO THE REGULAR TABLE
'CANCEL = True doe not work so I was trying to get record.Fields.CancelUpdate to work here

ELSE ........

 

[kjv1611]

First, I didn't read your entire post. It's a long read - maybe by necessity, but it doesn't seem so.... If I got your point correctly, it's this:

1. You want a log of all changes, or certain changes, to a particular table.

2. You don't want that log to actually edit the said table, but rather store the changes elswhere.

If that's correct, then I think you may be going about it wrong from the start - of course, I didn't read the whole thing, so correct me where I'm wrong.

This is how I'd do it:

1. You've got your "main" table
2. You need to add a separate "log" table.
3. In that table, you create a LogID, and then also include the ID field from the other main table, and any time there is an update, you create a new entry in your log table, and in that record, you include the maintableID which you can then use to create any reports you want in the future....

So, then you could later say, okay, what was all the activity for maintableID 12345.... Build a query that joins the 2 tables on maintableID, and lists the log details for all records in the log table with maintableID 12345...

Is this anything like what you're looking for? If not, could you perhaps put the short "here's my problem, and I want this result" in another post in this thread?

Thanks,

--

"If to err is human, then I must be some kind of human!" -Me

 

[allanBOS]

Yes, kjv1611, thanks. I am already going down that approach from a data perspective. I got a good hint on another board yesterday as follows which I am successfully implementing for changing existing rows:


"I would have used a form subform setup and record the change requests in the subform. The main form will be "read only" so users can't change the existing information themselves. Once the managers approved a change request, apply it to the main table and mark the record in the subform as completed. Your audit log will also have the information of who approved the change.

As for new records, you could provide a separate form for that."

The subform in the new form uses the change table which is a shadow of the original table, including field properties like required, etc. For each field in subform I am defaulting the Value of corresponding field from main form. Once the subform is ready I am moving it up so the container form so the container's fields (pointing to the regular table) are hidden.

I am validating that user really changes a field in the request subform before allowing them to save a requested (changed) row.

So I seem to be on my way (finally.)

Thanks again