Searching for records through sub-form

  • Thread starter Thread starter Jonathan Blitz
  • Start date Start date
J

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
 
Jonathan Blitz said:
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.
Click to expand...

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
 
Thx, works great.

Rick Brandt said:
Jonathan Blitz said:
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.
Click to expand...

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
Click to expand...
 
Back
Top