subquery question

Dear experts -
I want to limit records in a record source to criteria in another field in a parent table.
Parent table = project_Parts. Key field: part_ID; other field I want to search on: part_number
Child table = Parts_revenue (includes field part_ID, but not field part_number).

I need to be able to dynamically change the recordsource to the subform showing the child table records, specifically limiting records to:
- Project_Parts_Revenue.Part_ID) In (SELECT Part_ID FROM Project_Parts WHERE Project_Parts.Project_ID=Forms!Project!txtProject_ID)
- but also project_parts.part_number falling into a named range (e.g.: in (1,4,5))

any ideas?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
Sounds like this project is increasing in complexity...

Just so I understand, want to filter a child table based on a field in the Parent table?
...because this is typically not done normally, and can get quite messy depending on what you need.

For example, You have a main form and a sub form (Customers and Orders).
Your are in the "Orders" (Child) subform (for let's say customer 27) ...and Cust 27 has 5 orders.
Now what field in the "Customer" main form do you want to filter the sub form on?
...because typically the only field a main and subform have in common is the linking field.

Do I have this correct?
Again, just so I make sure I understand your question here...

terpsichoreAuthor Commented:
In this example, i want to filter the Orders based on a field in the customer table - let's say 'state' or something - but not the foreign key that is in the orders table.
So, right now, i am filtering the 'orders' table based on the foreign key using a subquery, and this works great.
Namely: - Project_Parts_Revenue.Part_ID In (SELECT Part_ID FROM Project_Parts WHERE Project_Parts.Project_ID=Forms!Project!txtProject_ID)

But I also need to filter it based on other criteria based on a different (not key) field in the parent table.
terpsichoreAuthor Commented:
BTW - the reason I don't include that other field in the recordsource is that when we went to delete records, we ran the risk of 'confusing' the system since we always try to keep only fields in the same table in the form recordsource.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Jeffrey CoachmanMIS LiasonCommented:
But again, if the main form "State" is NY, (The Customers home state) then all of the subform orders are associated with this "NY" customer
Why would there be another "State" to filter on...?

Sorry if I am not understanding...
Can you give me an example of what you are requesting, using Customer and Orders?

terpsichoreAuthor Commented:
so let's say the customers table has a whole bunch of customers.
the orders table is set to filter for customers who have placed this order. (customer_ID field in orders table provides the direct linkage).
Now, let's say I want to further filter the orders table to only orders from customers who've placed this order but who are also from the state of NY...

One other idea I had - can I set a filter on the form based on a column in a combo box (that's tied to the foreign key, but not a field in this table)? That would solve the problem...
Jeffrey CoachmanMIS LiasonCommented:
<the orders table is set to filter for customers who have placed this order.>
So then:
1. So, this is not a classic main/subform arrangement where the main form determines the sub form records?

2. So more than one customer can place an order?

<can I set a filter on the form based on a column in a combo box (that's tied to the foreign key, but not a field in this table)? That would solve the problem...>
...You can set a filter based on any field in any table.

Your example confuses me a bit, ...but give it a try and see...

Rey Obrero (Capricorn1)Commented:
try this query

select *
from Project_Parts_Revenue as PR
inner join Project_Parts as PP on PR.Part_ID=PP.Part_ID
where PP.Project_ID=Forms!Project!txtProject_ID and PP.part_number in(1,2,3)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
terpsichoreAuthor Commented:
it works! many thanks!
Jeffrey CoachmanMIS LiasonCommented:
seems that I misunderstood the question...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.