Cann't update record value

[Dou]

My Code:

Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb

MySQL="Select * From WorkOrders WHERE [WONo] = '" & txtWONo & "*' ;'"

Set rs = db.OpenRecordset(MySQL, dbOpenDynaset)

CurrentDb.Execute "UPDATE rs SET [Status]='Close';"

but I cann't update the Status's value. why?

Thanks

 

[Ken Snell [MVP]]

You don't run update queries on recordsets; you edit them.

Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb

MySQL="Select * From WorkOrders WHERE [WONo] = '" & txtWONo & "*' ;'"

Set rs = db.OpenRecordset(MySQL, dbOpenDynaset)

rs.Edit
rs![Status].Value = "Close"
rs.Update

rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

 

[John Spencer (MVP)]

Although you can change your query to an update query and execute that.

Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb

MySQL="UPDATE WorkOrders SET Status = ""Close"" WHERE [WONo] = '" & txtWONo & "'"

CurrentDb.Execute mySQL

You don't run update queries on recordsets; you edit them.

Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb

MySQL="Select * From WorkOrders WHERE [WONo] = '" & txtWONo & "*' ;'"

Set rs = db.OpenRecordset(MySQL, dbOpenDynaset)

rs.Edit
rs![Status].Value = "Close"
rs.Update

rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
--

Ken Snell
<MS ACCESS MVP>

My Code:

Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb

MySQL="Select * From WorkOrders WHERE [WONo] = '" & txtWONo & "*' ;'"

Set rs = db.OpenRecordset(MySQL, dbOpenDynaset)

CurrentDb.Execute "UPDATE rs SET [Status]='Close';"

but I cann't update the Status's value. why?

Thanks

 

[Ken Snell [MVP]]

Although you can change your query to an update query and execute that.

That'll work too! < g >