Filter a Form on a Field in a Subform

[Baldur]

I am looking for a VBA code to filter a form based on a field in a subform.


The system in question is a (simple) CRM system.
The tables in question are:
CONTACTS (Customers/prospects).
and CATEGORY (customer category such as Prospect, Customer and 4 other fields)
The Contacts form draw its records from the CONTACTS table and
and the subform from the table CATEGORY.
The CATEGORY table fields: Category and Service are the fields I want to be able to filter on to focus on all the prospects that might be interested in a particular service.

[June7]

Not understanding relationship between CONTACTS and CATEGORY. If you want to provide db for analysis, follow instructions at bottom of my post.

[Baldur]

Category is the subform to CONTACTS.
Contacts can be of different category/types. Example; Customer, prospects and 3 other types

[Welshgasman]

Normally it is the other way around?

However, to refer to subforms and their controls http://access.mvps.org/access/forms/frm0031.htm

[June7]

I would expect CATEGORY to be a lookup table and source for a combobox to populate a field in CONTACTS. This is why I am confused as to why you have it as a subform.

[Micron]

What June7 said. Plus if this means that there is a field for every category type, the table is designed like a spreadsheet.

customer category such as Prospect, Customer and 4 other fields