Tracking table in Access

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
jorgito4Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
mark2150Connect With a Mentor Commented:
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

M
0
 
mark2150Commented:
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" & obj.name & "changed from: " & obj.tag & " to: " & obj.text & " by: " & user & " on: " & format(now, "hh:mm dd-mmm-yy") & rs!memo
  endif
  '
endif
'
next obj

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

M
0
 
jorgito4Author Commented:
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?
Thanks!!!!
0
 
jorgito4Author Commented:
Very helpful and knowledgeable. Excellent help.
Thank you Mark.
0
 
mark2150Commented:
Thank you for the lovely points. They look really nice over the mantle. (grin)

M
0
All Courses

From novice to tech pro — start learning today.