Filter-by-Form in a subform (Access 2000)

Topic

New Reply

I have a main form with invoice info ( Invoice #, Date of Purchase, Company Name) and a subform with product info (model, serial #, …). I want to be able to filter -by-form on any field, either in the main form or the subform, and have it work like it does when there isn’t any subform. Can this be done without getting too complex, or is there a better way to accomplish filtering with subforms?

Reply | Quote

Replies

What kind of behavior would you like if you filter on a field in the subform? Do you want to show only matching records in the subform, or do you want to restrict the main form to records that have sub-records that match the criteria, or something else?

Reply | Quote

I would like to restrict the main form to to records that have sub-records that match the criteria when I apply a filter to a field in the subform. This way I see the data in the main form and subform.

Reply | Quote

I have attached some code written by Tim Curran for Access 95. It works – more or less – in more recent versions too, though not without problems, in particular if you try to filter on a lookup field.

The code is meant to go into the On Apply Filter event of the subform.

Reply | Quote

Most of the fields in my subform are lookup fields. Is there a way to get the find/replace button to work in the subform. It works on the Main form but the find/replace button only finds subform records on the current main form record, not all main form records.

Reply | Quote

There is no way (as far as I know) to make the built-in Find/Replace search both the main form and subform. You can create your own search option, but if you want to make it flexible, it’ll be complicated. I have only tried to provide a “global” search option on one or two fields in a subform in my databases, not a complete solution. You’ll need two recordsets. The first must be based on a query/SQL statement that joins the record source of the main form to the record source of the subform. Search for the value provided by the user in this recordset. Retrieve the value of a key field (or fields) from the record source of the main form from the record you found, and use this value to search the RecordsetClone of the main form. Clear as mud?

Reply | Quote