filtering subforms and forms

[dkliment]

I have a main form that has a field called the state_number as a unique
key. I then have a tab control that contains four sub forms and they
also have a state_number on each form. I have it setup for a one to
many relationship. My goal is to be able to search on the main form in
any field and bring up related records in both the main form and
subform. Example, search for a name on the main forma and bring up all
records with the same name in the main form and related records based
on the state_number in the sub forms. I would also like to go the
other way. I would like to search a field on any sub form and bring up
all related records for that field on the sub form and on the main
form. Example, look for a certain color in a field and filter for all
records with that color on the sub form and have the related records on
the main form only. All this filtering could be done on multiple
fields on the forms. A realistic example would be to search on the sub
form jewelry for a gold (field 1) women's (field 2) ring (field3).
These fields could be check boxes or text boxes. I would want to
search for the items entered and just bring back records on the sub
form pertaining to those three items and then on the main form bring
back the related records for those items. I have looked at the "filter
a form on a field in a subform" article but I am not sure this is
helping me. Any help would be appreciated or let me know if this is
not possible to do.

 

[Kel]

I am currently working on something similar - I have had to create my own
search and filter functions which consist of dialog boxes to obtain the
search/filter criteria and then using a recordset to Findnext record for the
search function, and setting the form's Filter property based on the
criteria selected.

I obtained the criteria for the search by listing the form's fields in the
drop box and then had a criteria box (similar to Access's built in find
form - but you can select any of the fields) and then using the Findnext
method on a recordset (based on the recordsource of the SUBFORM). Then using
the field(s) that link the subform to the main form (with yours I think it
would be state_number), find the matching main record and move to it on your
form.

With the filter, I collect the filter criteria for the subform on a form,
then use this to build the filter for the main form - setting the filter to
be something like -

state_number IN (SELECT State_Number FROM <Subform Recordsource> WHERE " &
Filter_Info & ")"

I get multiple filter criteria by having my criteria form based on a temp
table with fields Field_Name, Comparison_Type, Comparison_Value,
Logic_Operator. For each of these, the user is provided with options in a
drop list i.e. List of field names, comparison_types (Like, Is Null, Equal
To, etc), potential comparison values (by selecting all unique values for
the selected field as the rowsource), and AND/OR logic operators. The user
can then select as many criteria as they like and link with AND/OR and each
of these criteria is stored as a record in the filters table. You can then
compile the filter by looping through a recordset based on the Filters temp
table and then set teh forms filter as above.

Hope this isn't too confusing!! It sounds longwinded - but I racked my
brains long and hard and this was the best solution I came up with!! If you
need any more info let me know - or just ignore anything I said of its no
good Its up to you!

Kel