why does this vba code not work ?

[Guest]

The msgbox shows the correct data, but the table is not updated. I'm totally lost. Any ideas?

Dim db As Database
Dim rs As Recordset
Set db = DBEngine.Workspaces(0).Databases(0)
Set rs = db.OpenRecordset("tblUserOptions")
rs.MoveFirst
MsgBox "updating " & rs("User_Name") & " to " & strUser
rs("User_Name") = strUser
rs.Update
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

 

[Beemer]

If you are using DAO then you must begin the update
section with rs.edit

-----Original Message-----
The msgbox shows the correct data, but the table is not

updated. I'm totally lost. Any ideas?

 

[Kevin Ayton]

You need to add the line shown below:

The msgbox shows the correct data, but the table is not updated. I'm totally lost. Any ideas?

Dim db As Database
Dim rs As Recordset
Set db = DBEngine.Workspaces(0).Databases(0)
Set rs = db.OpenRecordset("tblUserOptions")
rs.MoveFirst
MsgBox "updating " & rs("User_Name") & " to " & strUser

' Insert this line.....
rs.Edit

rs("User_Name") = strUser
rs.Update
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

HTH

Kevin

 

[Van T. Dinh]

In addition to Beemer & Kevin, are you aware that your code only updates ONE
Record in the tblUserOptions?

Since you don't have any sorting / ordering, AFAIK, the Recordset may be
constructed with Rows / Records unordered which means that the *first* Row
in your Recordset can be any Record in your Table. If you actually want to
modify only one Record, you will need to identify the Record properly.