Track Changes to Records

K

Karl Burrows

Is there a way to track changes made to a record and then create a report
that lists these changes for specified periods of time (user would input)?
I need to track some errors in a database and we need to see who is changing
data.

Thanks!
 
K

Karl Burrows

I spent some time programming this and it appears to work, however, now
Access will lock up for no reason. There is a section as follows:

In the Database window, select the form used to enter/edit/delete data. From
the View menu, choose Code. Access opens the form in Design View, and
displays the code window. On a fresh line in the General Declarations
section, add the line:

Dim bWasNewRecord As Boolean

Does this go above or below the Option Compare Database, Option Explicit
line at the top of the code? I have other coding in this form. Does this
need to go in any certain order? One is a copy button command and the other
unhooks the wheel mouse.

This appears to be exactly what I need, I just need a bit of help debugging.
I am learning coding, so I am still low on the learning curve! Thanks!

Those Access tips have moved to:
http://allenbrowne.com/tips.html

The specific page is:
http://allenbrowne.com/AppAudit.html
 
A

Allen Browne

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
Karl Burrows said:
I spent some time programming this and it appears to work, however, now
Access will lock up for no reason. There is a section as follows:

In the Database window, select the form used to enter/edit/delete data.
From
the View menu, choose Code. Access opens the form in Design View, and
displays the code window. On a fresh line in the General Declarations
section, add the line:

Dim bWasNewRecord As Boolean

Does this go above or below the Option Compare Database, Option Explicit
line at the top of the code? I have other coding in this form. Does this
need to go in any certain order? One is a copy button command and the
other
unhooks the wheel mouse.

This appears to be exactly what I need, I just need a bit of help
debugging.
I am learning coding, so I am still low on the learning curve! Thanks!

Those Access tips have moved to:
http://allenbrowne.com/tips.html

The specific page is:
http://allenbrowne.com/AppAudit.html
Click to expand...
 
A

Allen Browne

In the General Declarations is fine.
Typically you would place it immediately below the Option lines.

There's nothing here that should cause a lockup.
Make sure there is nothing in Form_AfterUpdate that is dirtying the record
again.

If you are referring to the code in the AppAudit.html page, and it is not
working as you expect, temporarily comment out the error handler in each of
the routines, by adding a single quote to the 2nd line of each, i.e.:
'On Error Goto...
That will let you trace the problem.
 
K

Karl Burrows

I removed the wheel mouse hook and it seems to be working fine now. Is
there a way to modify the code to only show the changes that were made and
not repeat everything in the record whether it was changed or not?

This is a great feature. Thanks for passing it along!!

In the General Declarations is fine.
Typically you would place it immediately below the Option lines.

There's nothing here that should cause a lockup.
Make sure there is nothing in Form_AfterUpdate that is dirtying the record
again.

If you are referring to the code in the AppAudit.html page, and it is not
working as you expect, temporarily comment out the error handler in each of
the routines, by adding a single quote to the 2nd line of each, i.e.:
'On Error Goto...
That will let you trace the problem.
 
A

Allen Browne

Karl, it would be possible to program this.

The key issue is the the OldValue of the controls is not available in
Form_AfterUpdate, so you would need to record these somewhere
(array/recordset/temp table) in Form_BeforeUpdate. You could then compare
each value with the previous value, and only write the changed fields and
values to your logging table.
 
K

Karl Burrows

Way over my head! I am just beginning to learn VBA and Access programming.
Can you offer any help with this? I have been looking for something like
this for a long time and it is a great feature where you have many people
making changes (many with little or no computer skills since this is a
non-profit meals on wheels) where we can track errors.

The way it is now, once the record is changed, there is no way to know what
was in the original record. This affects meal deliveries and client status,
so this is a perfect solution. The issue is there are about 5 fields that
are changed constantly and maybe 25 changed infrequently but need to be
tracked (like if someone is a "hold" for delivery if they are out of town,
in the hospital, etc.), but all these need to be in the report, so you have
to look through all the fields for maybe one record change.

Thanks for any help you can offer!

Karl, it would be possible to program this.

The key issue is the the OldValue of the controls is not available in
Form_AfterUpdate, so you would need to record these somewhere
(array/recordset/temp table) in Form_BeforeUpdate. You could then compare
each value with the previous value, and only write the changed fields and
values to your logging table.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Track changes 3
Tracking changes to an existing database 3
Who's in and who made changes 13
Can I track and view changes in Access? 3
Track Changes - Multiple reviewers 0
How do i track the changes that are done in access by as user 7
Outlook turn OFF track changes in Office 365 ms outlook 0
Change Track Changes default options 2

Top