Subforms and filtering

  • Thread starter Thread starter Stephen @ ZennHAUS
  • Start date Start date
S

Stephen @ ZennHAUS

Hi Guys and Gals

I have what is essentially a contacts database. Each contact has the ability
to have essentially unlimited Addresses, Phone numbers, Email Addresses,
Industry Roles, Available Equipment, Qualifications etc.

As such, I have implemented each of those details about a contact as a
separate table. My problem is that now that I have a form with multiple
subforms, if I try to search or filter based on say a city, piece of
equipment or qualification etc, the main form (which has the general contact
details such as name etc) still displays all records. The subform records
are the only filtered records. So, when scrolling through the list of
contacts, the subform only shows records for those contacts that match the
filter criteria, all other records just show no records in the subform.

How can I filter or search by say City or Equipment with a form like this?

Thanks

Stephen @ ZennHAUS
 
Use a subquery in the Filter of the main form to restrict it to contacts who
have the desired value in the related table.

Here's a basic example that selects the clients who have New York as one of
their addresses:

strFilter = "EXISTS (SELECT AddressID FROM tblAddress WHERE
(tblAddress.ClientID = tblClient.ClientID) AND (tblAddress.City = 'New
York'))"
Me.Filter = strFilter
Me.FilterOn = True

More details and examples of subqueries:
http://allenbrowne.com/subquery-01.html#Search

The final screenshot on that page illustrates a search form where you
combine several subqueries into the filter, i.e. you end up with:
strFilter = "EXISTS (SELECT ...) AND EXISTS (SELECT ...) AND ...
 
Thanks Allen

Allen Browne said:
Use a subquery in the Filter of the main form to restrict it to contacts
who have the desired value in the related table.

Here's a basic example that selects the clients who have New York as one
of their addresses:

strFilter = "EXISTS (SELECT AddressID FROM tblAddress WHERE
(tblAddress.ClientID = tblClient.ClientID) AND (tblAddress.City = 'New
York'))"
Me.Filter = strFilter
Me.FilterOn = True

More details and examples of subqueries:
http://allenbrowne.com/subquery-01.html#Search

The final screenshot on that page illustrates a search form where you
combine several subqueries into the filter, i.e. you end up with:
strFilter = "EXISTS (SELECT ...) AND EXISTS (SELECT ...) AND ...
Click to expand...
 
Back
Top