why does this vba code not work ?

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
Click to expand...
updated. I'm totally lost. Any ideas?
 
You need to add the line shown below:

Ryan said:
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
Click to expand...

' Insert this line.....
rs.Edit
rs("User_Name") = strUser
rs.Update
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
Click to expand...



HTH

Kevin
 
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.
 
Back
Top