CODEWITHSUNDEEP
ms-accessvba
Ivan
Ivan

Reputation: 55

Track user changes in MS Access

I have a table and a Form in MS Access. Data is added through the Form. I want to be able to track user changes. I have a column User in my table where the user name will be added after update of the Form. For this purpose I have the following VBA code which will be fired After Update of the form, however it doesnt work and I dont know where the mistake is. Help will be appreciated. Thanks

  Private Sub Form_AfterUpdate()

Dim UserLogin As String
Dim MyId As Integer
Dim strSQL As String
Dim rs As Recordset

 UserLogin = Environ("Username")
 MyId = Me!ID.Value

 Set strSQL = "Update [MyTable] SET [MyTable]!User = '" & UserLogin & "' WHERE  [MyTable]!ID = '" & MyId & "';"
'Set rs = dbs.OpenRecordset(strSQL)
DoCmd.RunSQL strSQL

Upvotes: 0

Views: 2952

Answers (2)

Sergey S.
Sergey S.

Reputation: 6336

You can track changes not only in particular form, but any change made in data will be registered, even if it was done directly on table. Just create Before Change data macro for your table ("Create Data Macro" menu in table design mode. In Data Macro designer add the action SetField with parameters: Name = User ("User" is your field name for storing user name) and Value = GetUserName().

In any standard module create the function like this:

Public Function GetUserName() As String
GetUserName = Environ("Username")
End Function

After this any change in your table will update changed record by name of user, who did this change.

Please note, in case of split database this function should be available in front end and in back end databases if you want to edit data also in backend.

As of your code, check data type of ID field. If it's Number, remove single quotes. Also replace "!" by "." in SQL query text.

Upvotes: 2

Krish
Krish

Reputation: 5917

Just insert a textbox with controlsource as "User"

and then instead of after update use

Private Sub Form_BeforeUpdate()
    me.user = Environ("Username")
end sub

no need for separate SQL operation

Upvotes: 0

Related Questions