Access object 424

Question

I am running an expression to loop through a recordset and with a string from each record run an update query on second table. Based on a LIKE match it updates a field to create a relation. I am having problem with Runtime Error '424' Object Required at CurrentDb.Execute.

Tables: Transactions (main table to update) TransactionType (relation table of types or categories) TransSet (List of strings and transactiontype to set to)

Private Sub Toggle1_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset   
Set db = CurrentDb Set rst = db.OpenRecordset("TransSet")   
Do Until rst.EOF

CurrentDb.Execute ("UPDATE Transactions SET Transactions.TransactionType =" & (TransSet.TransTypeSet) & " WHERE ((Transactions.TransactionText1) Like * " &   (TransSet.TransIdent) & "*))")


rst.MoveNext Loop   
rst.Close Set rst = Nothing

End Sub

Answer 1

Reference the recordset object, not the table or query the recordset is based on. Need apostrophe delimiters for text type fields paramaters.

CurrentDb.Execute ("UPDATE Transactions SET TransactionType ='" & rst!TransTypeSet & "'" & _  
" WHERE TransactionText1 Like '*" & rst!TransIdent & "*'")

Answer 2

Thank you - I also had realised my error and fixed the code. I also changed the table name to not confuse with reserved words.

Private Sub Toggle1_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("Select * FROM TransSet")

Do Until rst.EOF


CurrentDb.Execute ("UPDATE Trans SET trans.TransactionType =" & (rst!TransTypeSet) & " WHERE ((Trans.TransactionText1) Like '*" & (rst!TransIdent) & "*');")

' MsgBox ("UPDATE Trans SET trans.TransactionType =" & (rst!TransTypeSet) &     " WHERE ((Trans.TransactionText1) Like '*" & (rst!TransIdent) & "*');")


   rst.MoveNext
Loop

rst.Close
Set rst = Nothing

End Sub