Searching for records through sub-form

[Jonathan Blitz]

I have an entry form which directs control to an output form.

The user enters search criteria on the entry form and clicks a button to
have the results displayed.

The output form includes a main form and a sub-form and the user can page
between entries on the main form.

This all works ok if the search criteria is on a field in the main form.
How can I do the same thing when the searhc criteria is on a field in a
subform.

For example, I only want customers in a certain state - but the state field
is in the sub-form and not the main one.

Jonathan Blitz
AnyKey Limited
Israel

 

[Rick Brandt]

I have an entry form which directs control to an output form.

The user enters search criteria on the entry form and clicks a button to
have the results displayed.

The output form includes a main form and a sub-form and the user can page
between entries on the main form.

This all works ok if the search criteria is on a field in the main form.
How can I do the same thing when the searhc criteria is on a field in a
subform.

For example, I only want customers in a certain state - but the state field
is in the sub-form and not the main one.

I assume you are building up a filter string in code and then applying it to the
second form when opening it. To limit the results in the main form based on matches
in the sub you need to use an In() clause.

Assuming that both maine and sub have a related field named [ID] filtering for
Colorado would look something like. . .

MyFilter = "[ID] In(SELECT [ID] FROM SubTableName WHERE [State] = 'CO')"

DoCmd.OpenForm "YourMainForm",,, MyFilter

 

[Jonathan Blitz]

Thx, works great.

I have an entry form which directs control to an output form.

The user enters search criteria on the entry form and clicks a button to
have the results displayed.

The output form includes a main form and a sub-form and the user can page
between entries on the main form.

This all works ok if the search criteria is on a field in the main form.
How can I do the same thing when the searhc criteria is on a field in a
subform.

For example, I only want customers in a certain state - but the state field
is in the sub-form and not the main one.

I assume you are building up a filter string in code and then applying it to the
second form when opening it. To limit the results in the main form based on matches
in the sub you need to use an In() clause.

Assuming that both maine and sub have a related field named [ID] filtering for
Colorado would look something like. . .

MyFilter = "[ID] In(SELECT [ID] FROM SubTableName WHERE [State] = 'CO')"

DoCmd.OpenForm "YourMainForm",,, MyFilter