Quote:
Originally Posted by 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
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.
