Search on field in subform

[Miaplacidus]

If I search on a field in a subform it only searches
records associated with the entry currently showing on the
main form. I would like to search all the records in the
subtable and move to the assocated records on the main
table.

Does Access have this functionality? I suppose I could put
in a search command button and use it to enter a value for
a parameter search, use that to create a query on the
joined tables and then do sequential finds based on the
contents of the record set.

Any other approaches would be welcome.

 

[Graham Mandeno]

Hi Mia

You can do this by setting a filter on the main form which uses a subquery
on the child table.

For example, lets say you have three tables:

Orders (one) <<< (many) OrderItems (many) >>> (one) Products

In words: "one order may include many items and one product may be ordered
many times".

Let's assume the related fields are:
Orders PK (primary key): OrderID
OrderItems FK (foreign key): ItemOrderID

Products PK: ProductID
OrderItems FK: ItemProductID

Now, say you have a form/subform showing an order and its items, and you
want to show only those orders which include a particular product, with
ProductID=32. You can find the OrderIDs of OrderItems with that product
using the query:
SELECT ItemOrderID from OrderItems where ItemProductID=32

Now, you can filter your main form to include only those orders:

Me.Filter = "OrderID IN (SELECT ItemOrderID from OrderItems where
ItemProductID=32)"
Me.FilterOn = True

 

[MacDermott]

Sounds as if you're on the right track.
Your query could be something like this:
SELECT * FROM MyMainTable WHERE MyMainTable.PK IN (SELECT DISTINCT
MyDetailTable.FK FROM MyDetailTable WHERE
MyDetailTable.MyField=[MyParameter]

HTH
- Turtle