is this a valid statment

[akrylik]

Can someone look at the code below and see why im getting a run-time 424 error?

[Bob Fitz]

I can't see any code in your post.

[akrylik]

Dur....sorry about that. Here it is

Set rs2 = CurentDb.OpenRecordset("SELECT * FROM ResourceAllocation WHERE ResourceAllocation.LastName(0) = rs1(0) AND [Resource Start Date] < #1/31/2012# AND [Resource End Date] > #1/1/2012#")

trying to create a recordset by using records from the ResourceAllocation table where the last name is = to a value i designate, and the values for the dates are in January.

[Bob Fitz]

LastName(0) = rs1(0)

Can you explain the bracketed number after LastName and what is rs1(0)

[akrylik]

Sorry, that field is a combobox and i was trying to pull the value from column 0 so it should read ResourceAllocation.LastName.Column(0) . rs1(0) is the first field from the recordset.

[Bob Fitz]

Try:
Set rs2 = CurentDb.OpenRecordset("SELECT * FROM ResourceAllocation WHERE ResourceAllocation.LastName(0) = rs1.("FieldName") AND [Resource Start Date] < #1/31/2012# AND [Resource End Date] > #1/1/2012#")

[akrylik]

Changed it to

Set rs2 = CurentDb.OpenRecordset("SELECT * FROM ResourceAllocation WHERE ResourceAllocation.LastName.Column(0) = rs1.HRID AND [Resource Start Date] < #1/31/2012# AND [Resource End Date] > #1/1/2012#")

but still have the same error.

[Bob Fitz]

Did you try:
Set rs2 = CurentDb.OpenRecordset("SELECT * FROM ResourceAllocation WHERE ResourceAllocation.LastName.Column(0) = rs1.Fields("HRID") AND [Resource Start Date] < #1/31/2012# AND [Resource End Date] > #1/1/2012#")

[akrylik]

Yes and it made the line of code red. Doesnt like that change.

[TheShabz]

string within a string. try creating a string type variable and set it to
"SELECT * FROM ResourceAllocation WHERE ResourceAllocation.LastName.Column(0) = rs1.Fields(""HRID"") AND [Resource Start Date] < #1/31/2012# AND [Resource End Date] > #1/1/2012#"

Then open the recordset on the string variable.

[akrylik]

I changed the code to :

query = "SELECT * FROM ResourceAllocation WHERE ResourceAllocation.LastName.Column(0) = rs1.Fields(""HRID"") AND [Resource Start Date] < #1/31/2012# AND [Resource End Date] > #1/1/2012#"
Set rs2 = CurentDb.OpenRecordset(query)

but im still getting the error. This time however, the problem was in the line :
Set rs2 = CurentDb.OpenRecordset(query)

ideas?

[akrylik]

is it even possible to open a recordset using a query with where conditions?

[orange]

I think the problem is with your trying to use a combobox in the sql in vba

ResourceAllocation.LastName.Column(0)

and possibly not setting the rs2 object somewhere

If you are referring to a combobox, it will be on a form.
If you want to include a form control in sql in vba, you need to "render'( my term) the form control's value.

along these lines
query = "SELECT * FROM ResourceAllocation WHERE ResourceAllocation.LastName = '" & Me.yourCombobox.value "' ......"


But I think that rs1 is probably not set.


You could try a debug.print query to see what the SQL is in Access' view.

In any case please show all of the code.