Object Reference Error in Code

[VBA Programmer]

Hi,

I am trying to insert data from one table (Emp_Details) into another table (Requests) based on the employee ID entered in the access form. I have writen the following code:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim sel As String
'Set con = New dao.Connection
'Set rs = New DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("Emp_Details", dbOpenDynaset)
Set rs1 = db.OpenRecordset("Requests", dbOpenDynaset)
rs1.AddNew
DoCmd.OpenQuery "Insert into Requests(ID, LName, FName, Manager, Email, Level) Select Emp_Details.ID, Emp_Details.LName, Emp_Details.FName, Emp_Details.EManager, Emp_Details.Email, Emp_Details.L from Emp_Details Where Emp_Details.ID = '" & txtID & "'"
rs1.Update
rs1.Close
db.Close
Set rs1 = Nothing
Set db = Nothing

But it gives an error 7874 saying that MS Access cant fine the Object.

How can I solve this problem?
Thanks in advance,
MK

[mshmyob]

I think you had more of an error message than that but you truncated it. You also didn't indicated where the error occured.

But quickly looking at your code, your query references a control or variable called txtID - I don't see it as a variable and if it is a control it is not referenced properly.

Did you mean to use the variable sel instead of txtID?

cheers,

[Rabbit]

Please use code tags when posting code.

The OpenQuery function is used to open an existing query object. Not to run SQL. To run SQL, use the RunSQL function.

Also, why did you create two recordsets? The only thing you're doing is adding a BLANK record. One of the recordsets you don't even use. The other one seems to want to do the same thing as the insert SQL but you never follow through.

All in all, the code is a mess.

[NeoPa]

Some of the points in this linked thread (Before Posting (VBA or SQL) Code) are very obvious, yet it seems you need them pointed out for you.

Your post suffers from various other avoidable problems if you'd simply read the threads explaining how to use the forums, or alternatively applied a minimal amount of consideration before submitting your question.

You have wasted a deal of time of those members trying to help you, so please be more considerate in future. A number of threads like this get deleted every day. You should try to ensure that none of your future questions follow that route.