object invalid or no longer set; code works fine when I run it through an sql query (1 Viewer)

[brainox]

I am trying to run this update query through vba and when it goes to run it gives me the error "Object invalid or no longer set" yet when I copy the code and push it through the sql query window it works fine?
Any Ideas?
Here's the update query;

dbs.Execute ("UPDATE tbl_CapexStaff SET tbl_CapexStaff.Status = '" & Status_Update & "'" _
& " WHERE (((tbl_CapexStaff.CAP_ID)= " & CAP_Staff & ") AND ((tbl_CapexStaff.Employee_no)=" & emp_LIVE & "));")

Many thanks in advance

 

[namliam]

the error probably has nothign to do with your query, rather with your dbs which is a variable that should be set.

try using
Currentdb.execute ...
to circumvent it or setting the dbs variable

Also do yourself a (maintenance) favour and format your query to be more readable...
soemthing along the lines of:

dbs.Execute (" UPDATE tbl_CapexStaff "& _ 
             " SET tbl_CapexStaff.Status = '" & Status_Update & "'" & _
             " WHERE (((tbl_CapexStaff.CAP_ID)= " & CAP_Staff & ") "&_
             "   AND  ((tbl_CapexStaff.Employee_no)=" & emp_LIVE & "));")

 

[pr2-eugin]

This is the problem of showing us your Snippet CODE, as the problem lies not in the line (as you have figured this out already), but somewhere down the CODE before this line..

My guess is that somewhere in the CODE you would have assigned the dbs object to Nothing. Or its scope cannot be reached, maybe the declaration of the Object is inside a If statement. Possibility for why this error is quiet a lot.. best if we could see the rest of the code.

 

[brainox]

Cheers guys, realised that the execution was in an if statement and I also accidentally closed the recordset and the database before I'd even finished with them!

 

[pr2-eugin]

Thought that would be the case. Glad to help. Good luck.