Updating a field in an SQL statement

[dom24]

I am trying to update the Password field usign the following code:

If Me.cboLName <> "" And Me.cboFName <> "" Then
strSQL = "SELECT dbo_Passwords.ManagerNo, Password, dbo_Staff.StaffNo FROM dbo_Passwords INNER JOIN dbo_Staff ON dbo_Passwords.ManagerNo = dbo_Staff.StaffNo WHERE dbo_Staff.LName = '" & Me.cboLName & "' AND dbo_Staff.FName = '" & Me.cboFName & "' "
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
MsgBox (strSQL)

With rst
strSQL = "UPDATE dbo_Passwords SET dbo_Passwords.password = dbo_Staff.StaffNo"
db.Execute (strSQL)
End With

I know there's a problem somewhere in the last UPDATE bit but I don't know what it should be. I want the dbo_Passwords.Password field to be updated with whatever is in the dbo_Staff.StaffNo field.

Thanks.

 

[jebenson]

You need a WHERE clause in the UPDATE statement or else the update will change the value of the password field for every record in the database. So, you need some way to uniquely identify which record you want to update in dbo_Passwords. Also, you need to put an actual value in place of dbo_Staff.StaffNo, perhaps like so:

strSQL = "UPDATE dbo_Passwords SET dbo_Passwords.password = " & rst("StaffNo") & " WHERE <criteria to identify record to be updated>"

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[dom24]

Done it, thanks for the help.

UPDATE dbo_Staff INNER JOIN dbo_Passwords ON dbo_Staff.StaffNo = dbo_Passwords.ManagerNo
SET dbo_Passwords.password = dbo_Manager.ManagerNo WHERE dbo_Staff.LName = '" & Me.cboLName & "' AND dbo_Staff.FName = '" & Me.cboFName & "'"

I did need to use a WHERE clause but I didn't need an actual value for dbo_Passwords.password as it only finds one record anyway so it usesthe value in that field.