Tracking table in Access

Posted on 2000-04-24
Last Modified: 2010-05-02
Ok here is my question:
I have an access application that I use to enter sensitive information. If somebody makes a change to one record I want to be able to track the field that was changed the user name of the person who logged on to my database and the change made. I want to create a table that gets this information thru vb code or queries. Please send me sample code assuming a simple record with name, age, DOB fields and the mechanism to track changes to this record. Remember I need to know who did it, when, what fields were changed and what were the old and new values for those fields. thanks
Question by:jorgito4
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
LVL 12

Expert Comment

ID: 2745141
I did this by adding a memo field to the data record. When you populate the fields place the *same* data into the .Tag of the control as you put in the .Text. Then at save time you watch to see if .Text=.Tag, if they don't, then you know that particular field has been updated. I then just add the info to the start of the memo field with something like:

DIM obj as Object
for each obj in Form.Controls
if typename(obj) = "TextBox" then
  if obj.text <> obj.tag then
    RS!memo = "Data Field" & & "changed from: " & obj.tag & " to: " & obj.text & " by: " & user & " on: " & format(now, "hh:mm dd-mmm-yy") & rs!memo
next obj

This is off the top of my head, but I have written this (the code is at home) and it DOES work.


Author Comment

ID: 2748429
I believe you understood what i am ying to do very well but i did not quite get the idea. Please clarify when you say:
1)"adding a memo field to the data record"
you mean adding a field to let's say the customer table that i am trying to track.
2)"populate the fields" means adding a new member right?
3)*same* what does this mean?
4)".Tag of the control as you put in the .Text" what control?, are you talking about the same text box where i am entering data?
5)Do you place the code that you gave me on the general section of the code window?
LVL 12

Accepted Solution

mark2150 earned 50 total points
ID: 2748674
1) Yes
2) When you fill your screen with data from the record you do:

Text1.Text = Trim("" & RS!Fieldname)
Text1.Tag = Text1.Text

3) Same means *same*
4) Yes
5) No, code goes in click event of [Update] button


Author Comment

ID: 2751352
Very helpful and knowledgeable. Excellent help.
Thank you Mark.
LVL 12

Expert Comment

ID: 2751404
Thank you for the lovely points. They look really nice over the mantle. (grin)


Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Using "ScreenUpdating" 6 86
Access 2013 combo box not working 3 76
Convert VB6 MSXML2.ServerXMLHTTP process to C# 2 81
Fastest way to find and count same items VB6 16 62
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question