By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,948 Members | 795 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,948 IT Pros & Developers. It's quick & easy.

Help - Record Of Changes

P: 5
I am fairly new to access. I have a created a form (form #1) with a subform within it. The form contains a auto#id that the subform contents are tied to.

I have created another form with the contents from the main form of form#1 and there are only 2 fields that can be updated.

What I need to happen is when anything is updated on these 2 fields it creates a record of it either in a table or a query. I thought a query would work best.

My goal is that everytime something is changed/modified in the main form it stores a copy either in a table or query. Other than just updating the field. I need to keep a record of any changes.

Any help is appreciated.
May 8 '07 #1
Share this Question
Share on Google+
8 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
I am fairly new to access. I have a created a form (form #1) with a subform within it. The form contains a auto#id that the subform contents are tied to.

I have created another form with the contents from the main form of form#1 and there are only 2 fields that can be updated.

What I need to happen is when anything is updated on these 2 fields it creates a record of it either in a table or a query. I thought a query would work best.

My goal is that everytime something is changed/modified in the main form it stores a copy either in a table or query. Other than just updating the field. I need to keep a record of any changes.

Any help is appreciated.
You can't store anything in a query. It is just a way of viewing data that is currently in the tables.

I think what you are talking about is an audit log of updates to the table. There are a number of previous questions on this issue. I am putting a couple of links below.

http://www.thescripts.com/forum/thread628282.html
http://www.thescripts.com/forum/thread622555.html

If you need any further information or clarification let us know.

Mary
May 8 '07 #2

P: 5
You can't store anything in a query. It is just a way of viewing data that is currently in the tables.

I think what you are talking about is an audit log of updates to the table. There are a number of previous questions on this issue. I am putting a couple of links below.

http://www.thescripts.com/forum/thread628282.html
http://www.thescripts.com/forum/thread622555.html

If you need any further information or clarification let us know.

Mary

In one of your reply you say something about creating a log and adding a record to that log whenever a record is added, edited or deleted with the username and timestamp also being recorded.

How do I go about doing this? I'm fairly new to access so step by step help would be appreciated.
May 9 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
In one of your reply you say something about creating a log and adding a record to that log whenever a record is added, edited or deleted with the username and timestamp also being recorded.

How do I go about doing this? I'm fairly new to access so step by step help would be appreciated.
Create a table to hold the log. Include the fields

tblLog
LogID (Primary key and Autonumber)
TableName (stores the name of the table being updated
User (stores the username)
Timestamp (stores the date and time of the change.

Create a Form Close event as follows:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Close()
  2. ' unsaved changes will automatically be saved when the form closes.
  3. Dim strSQL As String
  4.  
  5.    If Me.Dirty then ' there are unsaved changes to the form
  6.       strSQL = "INSERT INTO tblLog (TableName, User, Timestamp) " & _
  7.          "VALUES ('TableName', '" & Environ("username") & "',  " & Now() & ")" 
  8.       DoCmd.RunSQL strSQL
  9.    End If
  10.  
  11. End Sub
Mary
May 9 '07 #4

P: 5
Create a table to hold the log. Include the fields

tblLog
LogID (Primary key and Autonumber)
TableName (stores the name of the table being updated
User (stores the username)
Timestamp (stores the date and time of the change.

Create a Form Close event as follows:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Close()
  2. ' unsaved changes will automatically be saved when the form closes.
  3. Dim strSQL As String
  4.  
  5.    If Me.Dirty then ' there are unsaved changes to the form
  6.       strSQL = "INSERT INTO tblLog (TableName, User, Timestamp) " & _
  7.          "VALUES ('TableName', '" & Environ("username") & "',  " & Now() & ")" 
  8.       DoCmd.RunSQL strSQL
  9.    End If
  10.  
  11. End Sub
Mary
Thanks for that Mary.

But I don't really need to store the name of the user who modified the info. What I want is everytime something is modified in the form. For example: Priority field - Priority 1 is changed to Priority 3 and Status field is change to completed. I want it to save the record in a table with the time and with the info from the fields that were changed. Almost like a record/log of any modifications to the record.

Is it something similar to what you gave me the code for? It may not be on Close though.

Thanks again for all your help thus far.
May 9 '07 #5

missinglinq
Expert 2.5K+
P: 3,532
Here's a paper by Access guru Allen Browne on audit logs. You may find it helpful.
May 10 '07 #6

P: 5
Here's a paper by Access guru Allen Browne on audit logs. You may find it helpful.
Thanks. I think you may have forgot to attach the paper as I don't see it.
May 10 '07 #7

NeoPa
Expert Mod 15k+
P: 31,342
Thanks for that Mary.

But I don't really need to store the name of the user who modified the info. What I want is everytime something is modified in the form. For example: Priority field - Priority 1 is changed to Priority 3 and Status field is change to completed. I want it to save the record in a table with the time and with the info from the fields that were changed. Almost like a record/log of any modifications to the record.

Is it something similar to what you gave me the code for? It may not be on Close though.

Thanks again for all your help thus far.
Mary's code gives you that PLUS logs the user too. If that is a problem then simply remove that part (I can't see why it would be).
I think the idea of the On Close event is that there is a form which is closed on every update - to be opened again to a new record when selected.
If that is not your situation, then you need to be more clear about what your situation actually is. What triggers the updates? Are deletions required too? Etc.
May 10 '07 #8

NeoPa
Expert Mod 15k+
P: 31,342
Thanks. I think you may have forgot to attach the paper as I don't see it.
I caught this after posting my last.
I couldn't find the link that Linq was referring to, but you can look around Allen Browne's site and see if you can find it yourself. I can say it's a very interesting place to visit.

*edit*
I had another look and found Creating an Audit Log
May 10 '07 #9

Post your reply

Sign in to post your reply or Sign up for a free account.