Update Table after Record Update with Form

[speciman_A]

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

[pbaldy]

What exactly does "doesn't work" mean? You're opening a recordset but not using it. The date value would need # as the delimiter instead of '. Or simply use Now() directly, without concatenation. You could also set both fields with one query; generically:

UPDATE TableName SET Field1 = OneThing, Field2 = Another WHERE...

[speciman_A]

pbaldy,
Thank you for the very prompt response. It doesn't populate any data into the respective table; I'm a bit confused about the # delimiter, but if I understand correctly, I can put Now() directly into the query? Can I call the public function ReturnUserName directly from within the query? Also, how can I write this without the RecordSet? I'm going to play with it some more in an hour or so. Thank you for your patience.

V/R

Josh

[speciman_A]

I tried a different script, but I'm getting Error 3021: No Current Record. Maybe the SQL is wrong?

Option Compare Database
Option Explicit
Private Sub Form_AfterUpdate()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim SQLstring As String
Set db = CurrentDb
SQLstring = "SELECT * FROM Employee_Data WHERE EMP_ID = '" & Me.EMP_ID & "'"
Set rs = db.OpenRecordset(SQLstring, dbOpenDynaset)

With rs
.Edit
!LastUpdate = Now()
!LastUpdateID = ReturnUserName
.Update
End With
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub

V/R

Josh

[pbaldy]

My guess is that the SQL string isn't returning a record. Try this method to test it:

BaldyWeb-Immediate window

[speciman_A]

Interestingly, the code functions fine when I use the Last_Name field instead of the EMP_ID field in the SQL string. Is there a reason I can't reference the replication ID in my SQL, or does it have to be referenced differently since it is the primary key for the table?

V/R

Josh

[speciman_A]

Ok, so it looks like the Me.WhateverField in my SQL is actually calling the name of the control on the form, i.e. if I were to name the control on the form (as referenced in my previous post) Last_Name_txt, the SQL would bug out on me. Is there anyway to work around this so that the replication ID doesn't have to be on the form to be called in the script?

[speciman_A]

Ok, so it still won't let me use the replication ID, even if I add it as a control in my form (and set the control to not visible); it gives me Error 3021. Anyway to work around this without cloning the replication ID in another field?

V/R

Josh

[pbaldy]

If it's a numeric field, you wouldn't want the ' delimiters. Try

SQLstring = "SELECT * FROM Employee_Data WHERE EMP_ID = " & Me.EMP_ID

[speciman_A]

Editing my code as prescribed produced Run-time error 3061, too few parameters. I will google the code and see if I can figure out what is going on.

V/R

Josh

[pbaldy]

What does the Debug show the SQL to be now?

[speciman_A]

The intermediate window shows the following:

SELECT * FROM Employee_Data WHERE EMP_ID = ????????

[pbaldy]

There are actually question marks? That's new. In any case, it would appear that it's not finding the proper value from Me.EMP_ID. Is that the correct name of a control?

[speciman_A]

That is the correct name of the control. I guess I will continue to Google the problem and see if I can't come up with something.

V/R

Josh

[pbaldy]

Can you post the db here?