Subform Linking To Multiple Master Fields Optional

[Ange Kappas]

Hi,
I posted a question yesterday which Karl helped me, but I want
to take it further by linking the master fields to the subform where
whichever control I place a value I want it to return in the subform
accordingly.
Let me clarify on the setup:

The subform is based on a query named ACCOUNTS
with the fields:
Name
Company
Arrival (which is date field)
Departure (which is date field)
Charge

In the Form named ACCOUNTS SEARCH I have controls named:
Pick Company (Which is a combo box)
Pick Arrival (which is date field)
Pick Departure (which is date field)


Now the criteria is set in the controls Pick Company, Pick Arrival and
Pick Departure to filter out in the subform with the records I want, where
on the subform the:
Link Child Field: [COMPANY];[ARRIVAL];[DEPARTURE]
Master Child Field:[PICK COMPANY];[PICK ARRIVAL];[PICK DEPARTURE]

The query however has in it's criteria field in the fields Company, Arrival
and Departure the following:
[Forms]![ACCOUNTS SEARCH]![PICK COMPANY]
[Forms]![ACCOUNTS SEARCH]![PICK ARRIVAL]
[Forms]![ACCOUNTS SEARCH]![PICK DEPARTURE]

which is fine because it returns me a Report based on the query.

The problem is that in my Form ACCOUNTS SEARCH I have to place a value in
all controls Company, Arrival and Departure to return the result in the
subform.
I want to have a choice of either placing lets say Company and Departure to
return all relevant results or Arrival and Departure and so on.

Much Appreciated
Ange

 

[Allen Browne]

Ange, it would be better to take a different approach. What you are doing is
possible, but very messy to build and maintain, and quite inefficient to
execute.

A better solution would be to build the WhereCondition string for OpenReport
based only on the controls that have a value.

Here's a downloadable example of how to do that
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

That example sounds like it is quite similar to yours, but places the search
boxes in the Form Header section so you don't need a subform. It illustrates
how to filter the form based on any combination of criteria the user
chooses. And the Trouble-shooting And Extending part talks about how to
apply the results to a report.