Attention: The forums will be placed on read only mode this Saturday (Oct. 20, 2018)

During this outage (6:30 AM to 8 PM PDT) the forums will be placed on read only mode. We apologize for this inconvenience. Click here to read details

Office & Productivity Software forum

General discussion

MS Access: Track Changes

by wiiownzzz / July 17, 2006 3:59 PM PDT

Hi everyone,
I would love some advice about something I'd like to do in Microsoft Access - I don't know if it's even possible yet!

I have a table in access, and would like to be able to track all changes made to any fields in the table. For example, if I change a field with the word 'cat' in it to the word 'dog', I'd like a separate table to automatically record the following:

Date of change
User who changed it
Change (eg "Changed 'cat' to 'dog')

I'd also like the option of adding:

Who authorised the change
Reason for change

I would really appreciate any help here - I know it should be possible with a bit of VB scripting, but I really don't know where to start.

Any help would be fantastic.

Thankyou!

Discussion is locked
You are posting a reply to: MS Access: Track Changes
The posting of advertisements, profanity, or personal attacks is prohibited. Please refer to our CNET Forums policies for details. All submitted content is subject to our Terms of Use.
Track this discussion and email me when there are updates

If you're asking for technical help, please be sure to include all your system info, including operating system, model number, and any other specifics related to the problem. Also please exercise your best judgment when posting in the forums--revealing personal information such as your e-mail address, telephone number, and address is not recommended.

You are reporting the following post: MS Access: Track Changes
This post has been flagged and will be reviewed by our staff. Thank you for helping us maintain CNET's great community.
Sorry, there was a problem flagging this post. Please try again now or at a later time.
If you believe this post is offensive or violates the CNET Forums' Usage policies, you can report it below (this will not automatically remove the post). Once reported, our moderators will be notified and the post will be reviewed.
Collapse -
I've done that. Not in access but the frontend.
by R. Proffitt Forum moderator / July 17, 2006 10:44 PM PDT

We don't use Access (the program) but the database. Our code is VB and it interacts with the access database.

With that we implemented what you asked.

Bob

Collapse -
Agree with Bob
by Kees Bakker / July 18, 2006 6:03 AM PDT

Anwriter,

This kind of logging isn't a function of the Jet database that's used by MS Access. I don't know what the possibilities are if you use SQL Server as your database.
And, of course, the last 2 items (reason of change and the person who autorized it) can't be guessed by the database engine (how could it possibly?) and should be entered on the screen by the user herself.

So you would have to code everything under the OK-button on the form(s) used to change this table, using VBA. Things to take into account:
- you should disable automatic update (when tabbing through the end of the record) and update via the menu bar (only update by the OK-button)
- you should disable all direct access to the table in update mode
- same for update-queries
- batch updates need attention also
- form only accessible in full forms mode, not in spreadsheet-like mode (sorry, I keep forgetting the right English term, but I'm you understand what I mean).

All of this being done, it's something like 2 pages of VBA, assuming it's one form and one table only. Assuming a programmer productivity of 200 lines of VBA a day, it seems like a day's work at most. Note, however, that your crude design doesn't mention the unique key to identify the record (but that's easy to add) nor the possibility of multiple fields changed in one request (that's a repeating group and migth take much more time depending on how you want to tackle it). And it includes only the logging part, not the access, report and analysis of the log once made.
This isn't an offer to do it. Just a very crude estimate.


Kees

Collapse -
Thanks for your help
by wiiownzzz / July 18, 2006 4:28 PM PDT
In reply to: Agree with Bob

Thank you both very much for your advice - I'll have a play with it and see what I can do.

Any other advice would be appreciated too! Happy

Collapse -
A couple of tips on this
by richardp123 / July 19, 2006 7:12 PM PDT
In reply to: Thanks for your help

For the code to log the changes to your record, place this in the "Form_BeforeUpdate" event. This ensures that whatever means the user saves the record within the form (even if in Datasheet View), the changes will get logged.

Additionally, using this event enables you to save a lot of coding hassle by using a couple of useful inbuilt properties designed for this sort of purpose. For each field you want to test whether the value has been modified or not, you can compare the .Value and .OldValue properties to see if they're the same, and if not, then write the details of the change to your log table. One "gotcha" is that NULL values will need to be tested for during this comparison, or errors may occur.

Richard

Popular Forums

icon
Computer Newbies 10,686 discussions
icon
Computer Help 54,365 discussions
icon
Laptops 21,181 discussions
icon
Networking & Wireless 16,313 discussions
icon
Phones 17,137 discussions
icon
Security 31,287 discussions
icon
TVs & Home Theaters 22,101 discussions
icon
Windows 7 8,164 discussions
icon
Windows 10 2,657 discussions

FALL TV PREMIERES

Your favorite shows are back!

Don’t miss your dramas, sitcoms and reality shows. Find out when and where they’re airing!