SQL- Update an multiple user table 1

[PJFry]

I am creating an audit log for a database that tracks contract in my company. When a user makes a change to a field in the database I need to know what the original value was and what the new value is. The original value is captured on an 'On Dirty' event. I want the new value to be captured in the same record. Below is the original code I used.

userID = fOSUserName()
FormUsed = "Edit"
ChangeDesc = "Changed After The Fact"
RecordNum = Me.RecordID
NewVal = Me.cmbAFact

strSQL = "UPDATE tAuditLog SET tAuditLog.txtNewVal = " & NewVal & _
" WHERE (((tAuditLog.anID)=(SELECT Max(anID) FROM tAuditLog)));"

This code works fine if only one user in the databse. But then it occured to me that as many as 10 people could be using the database (set up as a frontend-backend). If two users were making changes at the same time, then is may be possible for user one to create an audit record, have user two create a record, user one's query would then record a change in user two's record and then user two would overwrite that record.

My first thought was the code below:

strSQL = "UPDATE tAuditLog SET tAuditLog.txtNewVal = " & NewVal & _
" WHERE (((tAuditLog.anID)=(SELECT Max(anID) FROM tAuditLog) AND [tAuditLog].[txtNTName] = " & fOSUserName() & "));"

This would only update the most recent record of that user. But this code does not work and I am not sure why. It might have something to do with the subquery. I have never tried a subquery in a form.

Here are my questions:
1. Given the speed of a query, what are the odds that the user one, user two, scenario happening? I don't imagine it would be great, but I have seen a lot of good guys go down thinking that.
2. Is there row-level record locking that I could add that would prevent the whole issue?
3. Is there an easier way to this? (If you want to answer this one, please be sure to answer one of the two above questions. My problem is how to get the code work. Given the structure of the database, the above code is probably the best way to go, but who knows?)

I am using Access 2003.

Thanks!
PJ

 

[PHV]

What about this ?

strSQL = "UPDATE tAuditLog SET txtNewVal=" & NewVal & _
" WHERE anID=(SELECT Max(anID) FROM tAuditLog WHERE txtNTName='" & fOSUserName() & "')"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PJFry]

Perfect! I was thinking it may be the syntax, but I am just too fried to figure it out.

Thanks!
PJ