I have a form with Orders and within it a Subform with the
Products of each.

How can I programmatically create a Filter based on a Product
Name? I want to see only the orders which contain a
specified product.

The Form's RecordSource does not contain the Product's name or code

Please help me out.

Eyrika

Seems to me your form and subform are backwards. Your form should have products and your subform should have orders based on those products. When you set up your parent/child relationship, your subform will filter automatically.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

I agree with Greg,
However...........
you could have something like this in a command button on your subform

Form_MainForm.Filter = "((YourTable.ProductName=""" & Me.ProductName & """))"
Form_MainForm.FilterOn = True

I am a loud man with a very large hat. This means I am in charge

I agree & don't agree with Greg. Yes, normally the main form, by virtue of its Parent/Child relationship, "filters" the subform, usually on its foreign key.
But what about, when invariably, you get records, in this case products, that occur in more than one Parent record, (regardless of which form, is parent). Is it possible to "reverse" the filter (so to speak), for whatever reason?

Normally a viable suggestion Steve, but the subform does not contain any corresponding fields as the main form. The only commonality would be the linked fields, primary key & foreign key, I suppose.

If you filter the subform, hypothetically, with a combobox selection, due to the link, would the main form be filterd accordingly? I doubt it.(I'm thinking out loud here)

Here's my thought, pick a product, create recordset with that product only. Loop through, populating an array with all the foreign keys, that belong to that product. With array, create SQL, that will be appended to recordsource of main form.
All orders with that product (and a few others, I assume, if more than 1 product for an order), will be viewed.

If I've understood the post correctly, I think this may be an option.

Either way, good luck GregoryHu, it sounds like an interesting challenge.

GregoryHu, you're talking about a many to many relationship. You need an intermediary table for this.