Allen B - Code to Audit changes

  • Thread starter Thread starter sara
  • Start date Start date
S

sara

I am trying Allen's code from his web site to audit
changes and I have a few problems. Dim db As
DAO.Database didn't compile, so I searched and found that
I had to go into VBA module, Tools, References and check
Microsoft DAO 3.6 Object Library. Maybe I need something
else too?

But the code isn't working for me to audit changes (I'm
not auditing Deletes and Inserts)

1. I NEVER got the "Edit TO" record. I get the AutoNum
in the auditTable (key) as 1,3,5,7 (missing the 2,4,6,8
which would be the "Edit TO" records.

2. I got some "Edit From" records, but not consistently. I
had tried to capture "ChangeDate" with the record (for
reporting, queries), and was having trouble with that. I
wanted the form to say "ChangeDate Default Value =Date()"
but that didn't update the table. So, I put an Update
Query in the code when the user presses "Save my Changes"
to put the Change date in. (I was told it's better to do
this in the form, but I can't get that to work).

Now I'm getting ChangeDate all the time in my table, which
is good. But I ONLY get the "Edit From" record. And If I
Edit the SAME record twice, I don't get the second (or
third, etc) "Edit From" record. But when I do edit
another record, the AutoNum Key in the Audit table is
incremented by 4 (showing that the table "knew" about the
others, but didn't write them).

Background:
I have a main form and subform. The user selects the
record to edit and clicks "Update record" which brings up
the "frmUpdateRecord". When done, the user chooses "Save
my Changes" button (or Undo or Close without Save). I
have the Save Record code with the Click Event of
the "Update Record" button. Perhaps this is in conflict
with the Before Update and After Update events recommended
in Allen's code?

Thanks - this is one of the last steps for me to show this
application to our President (a real techno-phobe who
doesn't believe all this CAN be done!)

Sara
 
Temporarily comment out the error handler at the beginning of each of the
procedures, i.e.:
' On Error GoTo Err_Handler
Now you will be able to see which line is failing, which gives you the clue
what to fix.

The most common mistake people make is to either not have the correct fields
in the audit tables, or to no have them in the correct order.

The fact that you get the audit record once only suggests that you have left
a unique index on the audit table, and it is blocking subsequent writes.
 
Allen -
I first looked at the unique key issue and you were right
(no surprise to me) - an index on PONumber said "No
dups". Once that was fixes EVERYTHING works - multiple
updates, FROM, TO etc.

I know you said it's best to make the change thru the
form, but I am not getting ChangeDate to work with =Date()
(Only) on the form. The field starts and stays blank.
Any suggestions? I put the Update Query back in for now
and it works fine - but I need BOTH - the form says =Date
() on the hidden field and the "Save" runs the update
query. I can't get it to work with only one of the
optiosn.

As it happens, we don't have users changing the same PO -
each clerk is assigned to a unique set of merchants, so I
wouldn't have the concurrent update issues, but as long as
I'm learning, I'd like to learn properly if I can.

Again, thanks.
Sara
 
Further, I see that if I edit a record that was changed on
another day, the CHANGE DATE is NOT being overwritten.
It's staying at 9/28 in the From AND TO records, and the
ChangeDate on the Table is showing the NEW date
(10/4/04). Can this be fixed somehow to have the TO
record also have the new date?

AudDate is correct. I am thinking that since I am
auditing and capturing all this, should I remove the
ChangeDate from my Main Table record? If I link the Audit
table and the MainPO Table won't that work somehow? I DO
have a form and reports that look for "Changed POs", but
my current approach seems to be storing duplicate data,
right? I am weakest with Data design, which is why this
is such a tough one for me. I'm playing with it, but
don't really know that I'm doing it right.

Thanks,
Sara
 
The Default Value applies only when you create a new record.

As explained previously, use the BeforeUpdate event of the form if you want
it assigned whenever this is a change.
 
Sorry to be so thick here -
I am NOT getting the ChangeDate to change on the Audit
record, only the master table. I have tried putting code
in both beforeUpdate and Afterupdate (changeDate = Date);
I have tried BeforeUpdate on the ChangeDate field (but
that field itself is not changed by the user).

In short, if the record is changing for the 2nd, 3rd, etc
time, the ChangeDate is NOT changing. Should I get rid of
ChangeDate entirely and rely on AuditDate only? Or is
there a way to have ChangeDate set to CurrentDate whenever
the user presses "Save this Record"?

Sara
 
The ChangeDate is probably redundant, so you can probably dump it.

Just as an aside, your "Save Record" button may not actually do anything
unless the record needed saving at the time.
 
Thanks. I think I'll put what I've got (working now) into
production and save the 2 suggestions here for "cleanup",
which I'm sure I'll have plenty of!

On to the next project. Thanks again.
Sara
 
Back
Top