creating recordset based on query

[akrylik]

im trying to open a recordset from a table based on a query statement. I have attached the code up to the point where I get a "Run-time error '424' : Object Required". Can anyone see the problem in the code?



Dim query As String
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset


Set rs1 = CurrentDb.OpenRecordset("EmployeeData")
Do Until rs1.EOF 'D0 runs the code below until it reaches the end of the recordset
'creates a new recordset w/ records from ResourceAllocation table
'where HRID(RA) = HRID(ED) and project lies within the month
query = "SELECT ResourceAllocation.* FROM ResourceAllocation WHERE (((ResourceAllocation.[HRID]) = rs1.Fields(""HRID"") AND ((ResourceAllocation.[Resource Start Date])<#1/31/2012#) AND ((ResourceAllocation.[Resource End Date])>#1/1/2012#))"
Set rs2 = CurentDb.OpenRecordset("query")

Tables in the database are EmployeeData and ResourceAllocation. HRID is a field set in both tables and the Resource Start Date and Resource End Date fields are both dates we are using for comparisons.

[pbaldy]

You have to concatenate the recordset value into the string, like:

"...WHERE (((ResourceAllocation.[HRID]) = " & rs1.Fields("HRID") & " AND..."

Which assumes it's numeric.

[akrylik]

Still getting the same error. when i debug, i highlights this line:

Set rs2 = CurentDb.OpenRecordset("query")

i think the query itself is working, just doesnt like me to use the string as the parameter...

[pbaldy]

This should help us figure out what's wrong with the string:

http://www.baldyweb.com/ImmediateWindow.htm

If you don't spot the problem, post the SQL here. Oh, if that's a copy/paste, you've misspelled "Current".

[akrylik]

ok now i feel like an idiot. it cleared up the 424 code but now the same line is throwing a 3464 run-time error. says "data type mismatch in criteria expression. Do i need put query in quotations? So currentdb.openrecordset("query")

[pbaldy]

No because it's a variable (you'd put an SQL string in quotes). I suspect HRID is a text data type, so this (note the single quotes added in red):


"...WHERE (((ResourceAllocation.[HRID]) = '" & rs1.Fields("HRID") & "' AND..."

[akrylik]

Yes it is, let me try it

[akrylik]

looks like that did the trick. Now everytime i run the code the DB stops responding. probably from the large amount of computing it has to do. Ill let you know if i have any more questions. Thanks for the help

[pbaldy]

Happy to help. Don't know what you're trying to do, but loops can certainly be slow.