Filter eliminating records without and detail records

J

Jeannie

I need a filter to be able to hide records that don't have any records in the
detail form. My example is Member Master and Event Detail. If there is no
data in the Event Detail, I need to hide that Member Master record.
 
A

Allen Browne

I think you have a main form bound to the [Member Master] table, and a
subform bound to the [Event Detail] table, and the two tables are linked by
a [MemberID] field? Now you want to filter the main form to eliminate
members who don't have any event details?

You could do that with a subquery as the filter.
Something like this:

Dim strWhere As String
strWhere = "EXISTS (SELECT [Member ID] FROM [Event Detail] " & _
"WHERE [Event Detail].[Member ID] = [Member Master].[Member ID])"
Me.Filter = strWhere
Me.FilterOn = True

For more information, see:
Filter a Form on a Field in a Subform
at:
http://allenbrowne.com/ser-28.html

If subqueries are a new concept, here's an introduction:
http://allenbrowne.com/subquery-01.html
 
Top