Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL- Update an multiple user table 1

Status
Not open for further replies.

PJFry

Technical User
Feb 6, 2005
93
US
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.

Code:
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:
Code:
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
 
What about this ?
Code:
strSQL = "UPDATE tAuditLog SET txtNewVal=" & NewVal & _
" WHERE anID=(SELECT Max(anID) FROM tAuditLog WHERE txtNTName='" & fOSUserName() & "')"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Perfect! I was thinking it may be the syntax, but I am just too fried to figure it out.

Thanks!
PJ
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top