Track changes to a record in Access

[zoro25]

Hi,

I'm creating a database and I need to find a way to track the changes to a record, more exactly, I need to know who makes changes to each record, as the users can only modify their own records.

I have one table where all the data is stored and I have between 50-100 users. Due to the high number of users, I would like to use the Windows login user ID instead of having to create user ID in Access. Is it possible? If yes, what is the code to be able to get the information (who changed the record) added to my table?

Thank you!

Marie

[ADezii]

Hi,

I'm creating a database and I need to find a way to track the changes to a record, more exactly, I need to know who makes changes to each record, as the users can only modify their own records.

I have one table where all the data is stored and I have between 50-100 users. Due to the high number of users, I would like to use the Windows login user ID instead of having to create user ID in Access. Is it possible? If yes, what is the code to be able to get the information (who changed the record) added to my table?

Thank you!

Marie

Marie, you can create a Log File that Tracks which Logged User made changes to data in a specific Form, at a give Date and Time, and it will also list the Primary Key Number of the Record that was changed. Copy and Paste the following Sub-Routine in a Standard Code Module. In its simplest Format, it will create a Log File in C:\Changes.txt that will list the changes made in order. Place the Sub Procedure Call in the AfterUpdate() Event of any Form for which you want to track data modifications:

Public Sub TrackChanges(strUser As String, frmName As String, lngPrimaryKey)
Dim Msg As String
 
Msg = Format$(Now(), "dddd, mm/dd/yyyy hh:mm AM/PM") & " - "
Msg = Msg & strUser & " made changes to data in Form " & frmName
Msg = Msg & " - {Primary Key Number " & lngPrimaryKey & "}"
 
Open "C:\Changes.txt" For Append As #1
Print #1, Msg
 
Close #1
End Sub
 

Sample Call to the Sub-Routine

Private Sub Form_AfterUpdate()
  Call TrackChanges(CurrentUser(), "frmEmployee", Me![PrimaryKey])
End Sub
 

Sample from C:\Changes.txt

Thursday, 11/08/2007 10:39 PM - Admin made changes to data in Form frmInventory - {Primary Key Number 74}
Thursday, 11/08/2007 11:48 PM - John Doe  made changes to data in Form frmEmployee - {Primary Key Number 98774}
Friday, 11/09/2007 08:05 AM - Bart Simpson made changes to data in Form frmInvoice - {Primary Key Number 876}
 

NOTE: The above code was listed only as a simple Template, make your own changes to suit your specific needs.

[wvmitchell]

Hi,

I'm creating a database and I need to find a way to track the changes to a record, more exactly, I need to know who makes changes to each record, as the users can only modify their own records.

I have one table where all the data is stored and I have between 50-100 users. Due to the high number of users, I would like to use the Windows login user ID instead of having to create user ID in Access. Is it possible? If yes, what is the code to be able to get the information (who changed the record) added to my table?

Thank you!

Marie

Hello Marie,

Take a look at my blog entry
Tracking changes in Access
this should do what you need.

Bill Mitchell