One more on SQL

[Cindy]

Thanks for you help. This is what is going on now...

strCriteria = "SELECT Agency_Name FROM Identification WHERE ACH_ID = " & IDNum
rs.AddNew
rs("Agency")= strCriteria

This is where I get an error telling me the Agency field is not long enough to accept the value of strCriteria. I stepped through the program and when it gets to the rs("Agency") = strCriteria, the value of strCriteria is all words in the "s. What am I doing wrong?

[bashfirst]

You have to open the recordset to get your agency name... then push the name into your other recordset.

strCriteria = "SELECT Agency_Name FROM Identification WHERE ACH_ID = " & IDNum
set rs1 = db.openrecordset(strCriteria)
rs.AddNew
rs("Agency")= rs1!Agency_Name

Is the rs recordset attached to a data control such that the user can see this happening? If not, why not do the insert in an action query?

db.Execute ("Insert into Table1 (Agency) Values (SELECT Agency_name FROM Identification WHERE ACH_ID = " & IDNum )

Bash

[Cindy]

Ok, I think this is the last question, I did it as you suggested, the only thing is when the Agency field is updated after the query with the rs1!Agency_Name, all of the fields fill in with the first Agency_Name in the table and it disregards the query info.
Thanks....Cindy