Query based on filtered records using recordset?

[jjsande1492]

Hi there.

I am trying to construct a query that will pull all records from a table where the Part_ID is in the list of filtered records in my subform. I want to join another table to pull in the SKU and Part_Number of the Part_ID. I thought I could accomplish this via recordsetclone, but it is only pulling the records based on the first Part_ID it sees. How can I better accomplish what I am trying to do?

My current construction:

strSQL = "SELECT tbl_BillOfMaterials.*, tbl_Parts.SKU, tbl_Parts.Part_Number FROM tbl_BillOfMaterials INNER JOIN tbl_Parts ON tbl_BillOfMaterials.Part_ID = tbl_Parts.Part_ID WHERE (((tbl_Parts.Part_ID) IN (" & rs![Part_ID] & ")))"

Cheers,


jj

[davegri]

Don't think that approach will work. And FYI, your IN clause only contains one value, making it same as a WHERE = clause.
I wouldn't take a recordsource clone approach.

The linking field between the main form and the subform is Part_ID, right?
If the recordsource for the subform is a named query, use that in your join and use the Part_ID from the main form to add your WHERE clause.
You COULD set the Part_ID to a tempvar in the Main Form_Current event, then it would be available for use the query's WHERE.
If the recordsource for the subform is a query string, just use that string to make a named query, and go with above.

[jjsande1492]

Thanks, davegri. Based on your response and my continued search on the web, it doesn't seem like what I am trying to do is possible. I will have to loop through the recordset instead.

Thanks for your help.