Problem! Why can't I update JUST 1 RECORD??

[Bill Garrett]

Hello again everyone. I got the help I needed last time, but now another problem has popped up. I am using the following code to update the "last person to update" field. For some reason, it takes the current user's name (database is MS Access 2002), and puts the user's name is EVERY record in the field. Is there a way to specify to ONLY put the name in the record that is currently on the screen in Access? Maybe passing the variable from Access somehow, and searching, or something? Or am I not going to be able to do this?

CODE:

Private Sub SAVE_RECORD_Click()

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Dim colItems As Items
Dim tblContacts As Recordset
Dim upContactId As UserProperty
Dim strMessage As String
Dim strTableName As String



Set db = OpenDatabase("c:\sulphur db\sulphur2002.mdb")

'Open the table.
Set tblContacts = db.OpenRecordset("Contacts", dbOpenDynaset)

sSQL = "update Contacts set Last_Person = '" & CurrentUser() & "'"
db.Execute sSQL


End Sub
Thanks!

Bill Garrett

[Harini]

Hello,
Attach the where condition, at the end of the sql statement like where [columnname]=[value of the record in that column]

If u use the above condition it will update the record that is shown one the screen, provided the where condition returns only the record that is shown on the screen.

Please rate if the solution is helpful

Thanks
Harini

[enigmaos]

private Sub SAVE_RECORD_Click()

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Dim colItems as Items
Dim tblContacts as Recordset
Dim upContactId as UserProperty
Dim strMessage as string
Dim strTableName as string



set db = OpenDatabase("c:\sulphur db\sulphur2002.mdb")

'Open the table.
set tblContacts = db.OpenRecordset("Contacts", dbOpenDynaset)

'make sure sSQL is empty
sSQL = ""
'add sql statement to sSQL
sSQL = sSQL & "UPDATE Contacts "
sSQL = sSQL & "set Last_Person = " & "'" & CurrentUser() & "' "
sSQL = sSQL & "WHERE fieldname = & "'" & value/variable & "'"

db.Execute sSQL

End Sub




[email protected]