Microsoft Access Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
User Name:
Password:
Remember me
 



Go Back   Dev Articles Community ForumsDatabasesMicrosoft Access Development

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Display Modes
 
Unread Dev Articles Community Forums Sponsor:
  #1  
Old June 14th, 2005, 10:04 AM
Csteed Csteed is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jun 2005
Posts: 9 Csteed User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 3 h 35 m 13 sec
Reputation Power: 0
Creating audit trail of all edits to database

Can anyone help with creating audit trail of all edits made to a table/form, (date, time of the initial loading of the table or form and all subsequent changes, plus identification of who entered the data and who subsequently made changes and document reason why changes were made.

Data can be entered in a form. If changes are made to the form, a record is kept of the original entry and the details are stored in another table.

Reply With Quote
  #2  
Old June 14th, 2005, 02:26 PM
lwells lwells is offline
Contributing User
Dev Articles Novice (500 - 999 posts)
 
Join Date: Sep 2004
Posts: 632 lwells User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 Day 21 h 59 m 38 sec
Reputation Power: 11
Create a table "Audit" and add the following fields:

FormName
ControlName
DateChanged
TimeChanged
PriorInfo
NewInfo
CurrentUser
Reason

Behind the forms module, copy and paste the following code:

Option Explicit
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function fOSUserName() As String
' Returns the login name for Adminstrator use
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If (lngX > 0) Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = vbNullString
End If
End Function

Function TrackChanges()
Dim db As Database
Dim rs As Recordset
Dim strSQL As String
Dim strCtl As String
Dim strReason As String
strReason = InputBox("Reason For Changes")
strCtl = Me.ActiveControl.Name
strSQL = "SELECT Audit.* FROM Audit;"
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
If rs.RecordCount > 0 Then rs.MoveLast
With rs
.AddNew
rs!FormName = Me.Name
rs!ControlName = strCtl
rs!DateChanged = Date
rs!TimeChanged = Time()
rs!PriorInfo = Me.ActiveControl.OldValue
rs!NewInfo = Me.ActiveControl.Value
rs!CurrentUser = fOSUserName
rs!Reason = strReason
.Update
End With

Set db = Nothing
Set rs = Nothing
End Function

In the BeforeUpdate for each of the controls you want to audit on your form add the function TrackChanges

This will record in the Audit table the name of the Form, the specific Control, the date and time it was changed, the old value and the new value for the control, the current user making the change and the reason for the change.

It isn't as complete as what you wanted but it is what I use. I didn't find it necessary to record when the table was created or who created the table originally. I was only interested if the data was changed after the record was entered.

Hopefully this is something you can use.

lwells

Reply With Quote
  #3  
Old June 15th, 2005, 10:09 AM
Csteed Csteed is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jun 2005
Posts: 9 Csteed User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 3 h 35 m 13 sec
Reputation Power: 0
Audit Trail

Thank you for the code to create an audit trail. I had to do some tweaking as I had some error messages "Compile Error - Use-defined type not defined". I added in DAO to the Track Changes and changed the Me to Screen as the error message read "invalid use of ME keyword".

All fields to the Audit table are being updated, but the only problem I have left is the form name is not being added to the Audit table. Can you help me any further.

Many thanks.



Option Compare Database
Option Explicit

Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function fOSUserName() As String

' Returns the login name for Adminstrator use

Dim lngLen As Long, lngX As Long
Dim strUserName As String

strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)

If (lngX > 0) Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = vbNullString
End If

End Function


Function TrackChanges()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strCtl As String
Dim strReason As String

strReason = InputBox("Reason For Changes")
strCtl = Screen.ActiveControl.Name
strSQL = "SELECT Audit.* FROM Audit;"

Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)


If rs.RecordCount > 0 Then rs.MoveLast

With rs
.AddNew
rs!FormName = Screen.ActiveForm
rs!ControlName = strCtl
rs!DateChanged = Date
rs!TimeChanged = Time()
rs!PriorInfo = Screen.ActiveControl.OldValue
rs!NewInfo = Screen.ActiveControl.Value
rs!CurrentUser = fOSUserName
rs!Reason = strReason
.Update
End With

Set db = Nothing
Set rs = Nothing
End Function

Reply With Quote
  #4  
Old June 15th, 2005, 11:43 AM
lwells lwells is offline
Contributing User
Dev Articles Novice (500 - 999 posts)
 
Join Date: Sep 2004
Posts: 632 lwells User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 Day 21 h 59 m 38 sec
Reputation Power: 11
The function TrackChanges() needs to be in the forms module not a global module in order for the Me.Name to work. So try this:

Screen.ActiveForm.Name

See if that will correct your problem.

lwells

Reply With Quote
  #5  
Old June 16th, 2005, 08:25 AM
Csteed Csteed is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jun 2005
Posts: 9 Csteed User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 3 h 35 m 13 sec
Reputation Power: 0
Thank you this worked!

If I want to add additional data from the form to the audit table i.e. control "RefId", how would the code be inserted into module?

Reply With Quote
  #6  
Old June 17th, 2005, 08:01 AM
lwells lwells is offline
Contributing User
Dev Articles Novice (500 - 999 posts)
 
Join Date: Sep 2004
Posts: 632 lwells User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 Day 21 h 59 m 38 sec
Reputation Power: 11
I assume you are wanting to identify which record was changed which is the Refld in your form. Add the following field to your Audit table - RecordID Next add this additional snippet to the existing code.

rs!RecordID = Screen.ActiveForm.CurrentRecord

This will add the current record to the Audit table to identify which record was changed.

lwells

Reply With Quote
  #7  
Old January 12th, 2009, 09:07 AM
FionaT FionaT is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jan 2009
Posts: 1 FionaT User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 16 m 24 sec
Reputation Power: 0
Unhappy Help!!!!

I used your code and it is working perfectly, but how do I stop the 'Reason for changes' box coming up every time a new record is added.

Quote:
Originally Posted by lwells
Create a table "Audit" and add the following fields:

FormName
ControlName
DateChanged
TimeChanged
PriorInfo
NewInfo
CurrentUser
Reason

Behind the forms module, copy and paste the following code:

Option Explicit
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function fOSUserName() As String
' Returns the login name for Adminstrator use
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If (lngX > 0) Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = vbNullString
End If
End Function

Function TrackChanges()
Dim db As Database
Dim rs As Recordset
Dim strSQL As String
Dim strCtl As String
Dim strReason As String
strReason = InputBox("Reason For Changes")
strCtl = Me.ActiveControl.Name
strSQL = "SELECT Audit.* FROM Audit;"
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
If rs.RecordCount > 0 Then rs.MoveLast
With rs
.AddNew
rs!FormName = Me.Name
rs!ControlName = strCtl
rs!DateChanged = Date
rs!TimeChanged = Time()
rs!PriorInfo = Me.ActiveControl.OldValue
rs!NewInfo = Me.ActiveControl.Value
rs!CurrentUser = fOSUserName
rs!Reason = strReason
.Update
End With

Set db = Nothing
Set rs = Nothing
End Function

In the BeforeUpdate for each of the controls you want to audit on your form add the function TrackChanges

This will record in the Audit table the name of the Form, the specific Control, the date and time it was changed, the old value and the new value for the control, the current user making the change and the reason for the change.

It isn't as complete as what you wanted but it is what I use. I didn't find it necessary to record when the table was created or who created the table originally. I was only interested if the data was changed after the record was entered.

Hopefully this is something you can use.

lwells

Reply With Quote
  #8  
Old January 24th, 2009, 07:46 AM
Spirited Away Spirited Away is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jan 2009
Posts: 6 Spirited Away User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 5 h 42 m 54 sec
Reputation Power: 0
deleting the reason dialog box

Hi Fiona

If you delete Dim strReason As String

strReason = InputBox("Reason For Changes")

Anywhere where it appears in the code, the reason dialog box will not come up.

Cheers

Spirited Away

Quote:
Originally Posted by Csteed
[COLOR=Blue]Thank you for the code to create an audit trail. I had to do some tweaking as I had some error messages "Compile Error - Use-defined type not defined". I added in DAO to the Track Changes and changed the Me to Screen as the error message read "invalid use of ME keyword".

All fields to the Audit table are being updated, but the only problem I have left is the form name is not being added to the Audit table. Can you help me any further.

Many thanks.


Option Compare Database
Option Explicit

Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function fOSUserName() As String

' Returns the login name for Adminstrator use

Dim lngLen As Long, lngX As Long
Dim strUserName As String

strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)

If (lngX > 0) Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = vbNullString
End If

End Function


Function TrackChanges()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strCtl As String
Dim strReason As String

strReason = InputBox("Reason For Changes")
strCtl = Screen.ActiveControl.Name
strSQL = "SELECT Audit.* FROM Audit;"

Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)


If rs.RecordCount > 0 Then rs.MoveLast

With rs
.AddNew
rs!FormName = Screen.ActiveForm
rs!ControlName = strCtl
rs!DateChanged = Date
rs!TimeChanged = Time()
rs!PriorInfo = Screen.ActiveControl.OldValue
rs!NewInfo = Screen.ActiveControl.Value
rs!CurrentUser = fOSUserName
rs!Reason = strReason
.Update
End With

Set db = Nothing
Set rs = Nothing
End Function

Reply With Quote
  #9  
Old February 20th, 2009, 10:49 AM
dof2001 dof2001 is offline
Contributing User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Feb 2009
Posts: 52 dof2001 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 11 h 26 m 54 sec
Reputation Power: 6
hi Spireted Away, i used this code and is great just one thing, i am new at this and i am trying to add copuple fileds to show in the audit table that are in the form. Project NAme and Project Area, do know exactly how to accomplish any help is welcome.

Thanks!



Quote:
Originally Posted by Spirited Away
Hi Fiona

If you delete Dim strReason As String

[COLOR=Red]strReason = InputBox("Reason For Changes")

Anywhere where it appears in the code, the reason dialog box will not come up.

Cheers

Spirited Away

Reply With Quote
  #10  
Old February 20th, 2009, 03:27 PM
Spirited Away Spirited Away is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jan 2009
Posts: 6 Spirited Away User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 5 h 42 m 54 sec
Reputation Power: 0
adding further fields to the audit table

Hi dof2001, Add the following field to your Audit table - ProjectName and ProjectArea and then next add this additional snippet to the existing code.

rs!ProjectName = Screen.ActiveForm.CurrentRecord
rs!ProjectArea = Screen.ActiveForm.CurrentRecord


As Below....

Option Explicit
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function fOSUserName() As String
' Returns the login name for Adminstrator use
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If (lngX > 0) Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = vbNullString
End If
End Function

Function TrackChanges()
Dim db As Database
Dim rs As Recordset
Dim strSQL As String
Dim strCtl As String
Dim strReason As String
strReason = InputBox("Reason For Changes")
strCtl = Me.ActiveControl.Name
strSQL = "SELECT Audit.* FROM Audit;"
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
If rs.RecordCount > 0 Then rs.MoveLast
With rs
.AddNew
rs!FormName = Me.Name
rs!ControlName = strCtl
rs!DateChanged = Date
rs!TimeChanged = Time()
rs!PriorInfo = Me.ActiveControl.OldValue
rs!NewInfo = Me.ActiveControl.Value
rs!CurrentUser = fOSUserName
rs!ProjectName = Screen.ActiveForm.CurrentRecord
rs!ProjectArea = Screen.ActiveForm.CurrentRecord

rs!Reason = strReason
.Update
End With

Set db = Nothing
Set rs = Nothing
End Function

This should then record changes to those two fields.

Cheers

Spirited Away

Last edited by Spirited Away : February 20th, 2009 at 03:31 PM. Reason: adding new information

Reply With Quote
  #11  
Old February 23rd, 2009, 09:10 AM
dof2001 dof2001 is offline
Contributing User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Feb 2009
Posts: 52 dof2001 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 11 h 26 m 54 sec
Reputation Power: 6
Hello Spireted Away,

Thanks for the prompt response, i follow your isntruction but there is one problem, in the fields addedd i don't get the actual contents of the fields, instead i get the record number. i need to get the actual project name and area if possible.

Thanks in advance for your time.


Quote:
Originally Posted by Spirited Away
Hi dof2001, Add the following field to your Audit table - ProjectName and ProjectArea and then next add this additional snippet to the existing code.

rs!ProjectName = Screen.ActiveForm.CurrentRecord
rs!ProjectArea = Screen.ActiveForm.CurrentRecord


As Below....

Option Explicit
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function fOSUserName() As String
' Returns the login name for Adminstrator use
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If (lngX > 0) Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = vbNullString
End If
End Function

Function TrackChanges()
Dim db As Database
Dim rs As Recordset
Dim strSQL As String
Dim strCtl As String
Dim strReason As String
strReason = InputBox("Reason For Changes")
strCtl = Me.ActiveControl.Name
strSQL = "SELECT Audit.* FROM Audit;"
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
If rs.RecordCount > 0 Then rs.MoveLast
With rs
.AddNew
rs!FormName = Me.Name
rs!ControlName = strCtl
rs!DateChanged = Date
rs!TimeChanged = Time()
rs!PriorInfo = Me.ActiveControl.OldValue
rs!NewInfo = Me.ActiveControl.Value
rs!CurrentUser = fOSUserName
rs!ProjectName = Screen.ActiveForm.CurrentRecord
rs!ProjectArea = Screen.ActiveForm.CurrentRecord

rs!Reason = strReason
.Update
End With

Set db = Nothing
Set rs = Nothing
End Function

This should then record changes to those two fields.

Cheers

Spirited Away

Reply With Quote
  #12  
Old February 26th, 2009, 01:30 PM
dof2001 dof2001 is offline
Contributing User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Feb 2009
Posts: 52 dof2001 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 11 h 26 m 54 sec
Reputation Power: 6
Never Mind i figure it out.
Code:
			rs!ProjectName = Me.Projects_Name
			rs!ProjectArea = Me.Project_Area


Thanks

Reply With Quote
  #13  
Old March 2nd, 2009, 06:41 PM
teseract teseract is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Mar 2009
Posts: 1 teseract User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 18 m 32 sec
Reputation Power: 0
Hello,

This works in a form... but is there a way to make it audit what is entered or changed in a drop down menu in a data access page?

Thanks,

MattB

Reply With Quote
  #14  
Old March 20th, 2009, 01:17 PM
ramez75 ramez75 is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Mar 2009
Posts: 1 ramez75 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 23 m 56 sec
Reputation Power: 0
I copied the code into the module and named it TrackChanges. Created the Table Audit. I went into each control field in the form and in the Beforeupdate added "Event Procedure" and then did Call TrackChanges.

Nothing happens what I am doing wrong, I get an error though

Reply With Quote
  #15  
Old March 22nd, 2010, 11:30 AM
ade7985 ade7985 is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Mar 2010
Posts: 2 ade7985 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 27 m 32 sec
Reputation Power: 0
Smile Help

Quote:
Originally Posted by lwells
Create a table "Audit" and add the following fields:

FormName
ControlName
DateChanged
TimeChanged
PriorInfo
NewInfo
CurrentUser
Reason

Behind the forms module, copy and paste the following code:

Option Explicit
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function fOSUserName() As String
' Returns the login name for Adminstrator use
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If (lngX > 0) Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = vbNullString
End If
End Function

Function TrackChanges()
Dim db As Database
Dim rs As Recordset
Dim strSQL As String
Dim strCtl As String
Dim strReason As String
strReason = InputBox("Reason For Changes")
strCtl = Me.ActiveControl.Name
strSQL = "SELECT Audit.* FROM Audit;"
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
If rs.RecordCount > 0 Then rs.MoveLast
With rs
.AddNew
rs!FormName = Me.Name
rs!ControlName = strCtl
rs!DateChanged = Date
rs!TimeChanged = Time()
rs!PriorInfo = Me.ActiveControl.OldValue
rs!NewInfo = Me.ActiveControl.Value
rs!CurrentUser = fOSUserName
rs!Reason = strReason
.Update
End With

Set db = Nothing
Set rs = Nothing
End Function

In the BeforeUpdate for each of the controls you want to audit on your form add the function TrackChanges

This will record in the Audit table the name of the Form, the specific Control, the date and time it was changed, the old value and the new value for the control, the current user making the change and the reason for the change.

It isn't as complete as what you wanted but it is what I use. I didn't find it necessary to record when the table was created or who created the table originally. I was only interested if the data was changed after the record was entered.

Hopefully this is something you can use.

lwells


In the BeforeUpdate for each of the controls forms, how do you add function trackchanges. I am new on access 2007 database. how do you add trackchanges in module to work with form?
Thanking you in advanced, ade

Last edited by ade7985 : March 22nd, 2010 at 12:56 PM. Reason: addditional question

Reply With Quote
  #16  
Old January 20th, 2011, 08:44 PM
ajg ajg is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jan 2011
Posts: 1 ajg User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 11 m 54 sec
Reputation Power: 0
audit trail question

Quote:
Originally Posted by lwells
I assume you are wanting to identify which record was changed which is the Refld in your form. Add the following field to your Audit table - RecordID Next add this additional snippet to the existing code.

rs!RecordID = Screen.ActiveForm.CurrentRecord

This will add the current record to the Audit table to identify which record was changed.

lwells


I know this is an old post, but I'm hoping someone can still answer. I have forms where records have been deleted. So the currentrecord doesn't return my actual record number. What code would return the value of my records autonumber field?

thanks in advance for any assistance.

Reply With Quote
  #17  
Old February 14th, 2011, 02:39 PM
suryakant.kale suryakant.kale is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Feb 2011
Posts: 4 suryakant.kale User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 h 5 m 27 sec
Reputation Power: 0
Post I am trying to add audit for another field called Address

Added "Address" field in audit table and following code has been updated.. Need help to get the right code ....Address field get the record number only not the actual data.....


Option Compare Database

Option Explicit

Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function fOSUserName() As String

' Returns the login name for Adminstrator use

Dim lngLen As Long, lngX As Long
Dim strUserName As String

strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)

If (lngX > 0) Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = vbNullString
End If

End Function


Function TrackChanges()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strCtl As String
Dim strReason As String

strReason = InputBox("Reason For Changes")
strCtl = Screen.ActiveControl.Name
strSQL = "SELECT Audit.* FROM Audit;"

Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)


If rs.RecordCount > 0 Then rs.MoveLast

With rs
.AddNew
rs!FormName = Screen.ActiveForm.Name
rs!ControlName = strCtl
rs!DateChanged = Date
rs!TimeChanged = Time()
rs!PriorInfo = Screen.ActiveControl.OldValue
rs!NewInfo = Screen.ActiveControl.Value
rs!CurrentUser = fOSUserName
rs!Address = Screen.ActiveForm.Name

rs!Reason = strReason
.Update
End With

Set db = Nothing
Set rs = Nothing
End Function

Reply With Quote
  #18  
Old April 26th, 2011, 12:02 PM
gl7pan gl7pan is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Apr 2011
Posts: 1 gl7pan User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 8 m 44 sec
Reputation Power: 0
Hi everyone.

I have used the code but i get a message

"Run-time error'13':Type mismatch"
and inside the code is highlighted the line

"Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)"


can you please help me on this?

thank you in advance.

Reply With Quote
  #19  
Old May 10th, 2011, 03:25 PM
bettyboo bettyboo is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: May 2011
Posts: 1 bettyboo User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 24 m 19 sec
Reputation Power: 0
Question Audit Trail for MS Access 2010

[audit trail] [ms access 2010] I realize also that this an old thread - but just in case there's still someone monitoring. How can this same procedure be done with ACCESS 2010 ?? Any help would be appreciated...Thank You!

Reply With Quote
  #20  
Old October 20th, 2011, 12:18 PM
USDAdmin USDAdmin is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Oct 2011
Posts: 1 USDAdmin User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 15 m 22 sec
Reputation Power: 0
Modifying for Combo box and check box

I would like to know how to modify this code to recognize combo boxes and check boxes. Any help is greatly appreciated. I've tried to incorporate a select case statement but that is not working.

Reply With Quote
  #21  
Old October 20th, 2011, 05:01 PM
June7's Avatar
June7 June7 is offline
Contributing User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Oct 2010
Location: The Great Land
Posts: 256 June7 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 Days 9 h 29 m 29 sec
Reputation Power: 4
Code should be the same for Access 2010.

As far as I can tell, code doesn't care what type the control is. As long as the control's name is in the Audit table and retrieved in the recordset, should work.

Reply With Quote
  #22  
Old November 9th, 2011, 05:26 AM
Jf4frWil Jf4frWil is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Nov 2011
Posts: 3 Jf4frWil User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 10 m 11 sec
Reputation Power: 0
details are stored in another table.

Reply With Quote
  #23  
Old November 9th, 2011, 08:57 PM
Theron451 Theron451 is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Nov 2011
Posts: 4 Theron451 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 12 m 42 sec
Reputation Power: 0
The function TrackChanges() needs to be in the forms module not a global module in order for the Me.Name to work. So try this:

Screen.ActiveForm.Name

See if that will correct your problem.
URL
URL

Reply With Quote
  #24  
Old November 27th, 2011, 09:55 PM
Debra 785 Debra 785 is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Nov 2011
Posts: 6 Debra 785 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 7 m
Reputation Power: 0
The function TrackChanges() needs to be in the forms module not a global module in order for the Me.Name to work. So try this:

Screen.ActiveForm.Name

See if that will correct your problem.


Reply With Quote
  #25  
Old March 14th, 2012, 09:48 AM
shuashef shuashef is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Mar 2012
Posts: 1 shuashef User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 19 m 6 sec
Reputation Power: 0
Audit trail Help!

Hi.
I used the code above but I'm having to problem:
1) The old value and the new value are always coming up as the same
2) I have a check box that when clicked deletes the values of other boxes on the same form but the audit trail only records the change to the checked box and not those automatically deleted. Any way to track those changes?

Thanks!

Reply With Quote
  #26  
Old March 15th, 2012, 03:35 AM
Ohagan1234 Ohagan1234 is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Mar 2012
Posts: 1 Ohagan1234 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 20 m 50 sec
Reputation Power: 0
One word.....Awesome.

Reply With Quote
  #27  
Old February 18th, 2013, 03:18 AM
horodav horodav is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Feb 2013
Posts: 2 horodav User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 8 m 41 sec
Reputation Power: 0
how to make it "global"

Hi,

I used this code wich works like a charme
But i would like to optimize it: currently we need to copy the code in each forms that needs to be audited.

I will need to use it in a Db where I will have aroud 50 forms to audit; How can I made the code global to avoid aving to copy it 50 times?

Thanks in advance for your suggestions

david

Reply With Quote
  #28  
Old February 18th, 2013, 03:23 AM
horodav horodav is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Feb 2013
Posts: 2 horodav User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 8 m 41 sec
Reputation Power: 0
how to make ot global?

Hi,

I used this code wich works like a charme
But i would like to optimize it: currently we need to copy the code in each forms that needs to be audited.

I will need to use it in a Db where I will have aroud 50 forms to audit; How can I made the code global to avoid aving to copy it 50 times?

Thanks in advance for your suggestions

david

Reply With Quote
  #29  
Old February 24th, 2013, 01:39 PM
June7's Avatar
June7 June7 is offline
Contributing User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Oct 2010
Location: The Great Land
Posts: 256 June7 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 Days 9 h 29 m 29 sec
Reputation Power: 4
I expect that will be very complicated code. I do something similar to replicate data from existing record to create a new record but the source and destination tables will vary. An excerpt from my code is:
Code:
rsDataOld.Open "SELECT * FROM [" & strTestTable & "] WHERE LabNum='" & strOldLabNum & "';", cn, adOpenStatic, adLockReadOnly
rsDataNew.Open "SELECT * FROM [" & strTestTable & "] WHERE 0=1;", cn, adOpenDynamic, adLockOptimistic
rsDataNew.AddNew Array("Labnum", "method"), Array(.tbxLABNUM, rsDataOld!method)
For j = 0 To rsDataOld.Fields.Count - 1
    If rsDataNew.Fields(j).Name <> "LabNum" And rsDataNew.Fields(j).Name <> "method" _
        And Not (Me.cbxStateNum = "99998" And rsDataNew.Fields(j).Name Like "Spec*") Then
            rsDataNew.Fields(j) = rsDataOld.Fields(j)
    End If
Next j
rsDataNew.Update
rsDataOld.Close
rsDataNew.Close
In your case the source data is on form. Dynamic reference to form and controls using variables is like:
Forms(strFormName).Controls(strControlName)
You should attempt code and when you encounter specific issue start a thread with your question.
__________________
I like to know if my suggestion helped you. One way to let me know is by clicking scales at top right corner of post.
Debug!Debug!Debug!http://www.cpearson.com/excel/debug.htm

Last edited by June7 : February 24th, 2013 at 01:53 PM.

Reply With Quote
  #30  
Old April 10th, 2013, 12:07 PM
Sadiutra Sadiutra is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Mar 2013
Location: usa
Posts: 6 Sadiutra User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 4 h 8 m 20 sec
Reputation Power: 0
Send a message via Skype to Sadiutra
How to create an audit log to record the history of changes to records in a Microsoft ?

Reply With Quote
Reply

Viewing: Dev Articles Community ForumsDatabasesMicrosoft Access Development > Creating audit trail of all edits to database


Developer Shed Advertisers and Affiliates


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 


Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.

© 2003-2014 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap