Good evening,
I am attempting to update a table with the date/time and user name after a record is updated in a form using an AfterUpdate event. The code I plugged in doesn't work (obviously, right?) and I am at wits end trying to debug it. ReturnUserName is a function that calls the login ID of the current user using the advapi32.dll system file. I know that function works because I use it to grant permissions based on whether the logged in user is on the Authorized Users List (a seperate table). Employee_Data is where the information is that the form manipulates and where the date/time and User ID information goes. LastUpdate is the field set aside for the date/time, and LastUpdateID is where the user id should go. The intent is that everytime a record is manipulated, I know exactly when and who edited the record. My experience with VBA is limited to that which I have just read in Microsoft Access 2010 VBA Macro Programming by Richard Shepard; also, I am (unfortunately) using Access 2007. Thank you in advance.
Option Compare Database
Option Explicit
Private Sub Form_AfterUpdate()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim lu As Variant
Dim lui As Variant
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM [Employee_Data] WHERE [EMP_ID] = '" & Me.EMP_ID & "'")
lu = Now()
lui = ReturnUserName
db.Execute ("UPDATE [Employee_Data] SET [LastUpdate]= '" & lu & "' WHERE [EMP_ID] = '" & Me.EMP_ID & "'")
db.Execute ("UPDATE [Employee_Data] SET [LastUpdateID]= '" & lui & "' WHERE [SM_ID] = '" & Me.EMP_ID & "'")
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub
V/R
Josh