[AccessD] Filtering Form by Subform Value

Subforms are joined to parent forms, not the other way around.  If the
subform has a link to the parent form, it is filtered by the value in
the parent form without any filter statements.  If it does not have a
link, it isn't filtered.  Can you explain how you've linked the form and
subform, if at all?

Charlotte Foust

-----Original Message-----
From: Stephen R. Zayko [mailto:szayko at secor.com] 
Sent: Monday, August 18, 2003 6:10 PM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Filtering Form by Subform Value


Dear Group:

I was wondering if someone could tell me the syntax for filtering a form
based upon a value in a subform?

I know that the generic syntax for a filter is:
Me.Filter = "(([recordsource].[field] = 'something' )) "

I know that if you want to filter for something in a drop down box it
is:
Me.filter = ((Lookup_[Name_of_DropDown].[field]="text of dropdown"))

(When I say "text of dropdown" I mean the visible column whatever that
may be)

What I would like to do is something similar to that, 
but filter on a field on a subform instead of on the
text of a dropdown box.

What I have is a form with customer records;
And a subform with sales records (only one sale per customer). The sales
records have a customer_ID which is how the two are linked.

How do I write the filter so I get all the customers with a sales record
date = #mm/dd/yy#

Me.filter = ((Formname_SubformName__NameOfDateBox.[DateField] =
#MM/DD/yy#))

Me.filter = ((customerID in SELECT sales.Customer FROM sales WHERE
salesDate = #mm/dd/yy#))

These syntax do not work.

Am I on the right track or is this not possible without redoing the
source query to the form?

Thanks

-Z

Stephen R. Zayko, P.E.

SECOR International Inc.
2321 Club Meridian Drive, Ste E
Okemos, MI 48864 

(p) (517) 349-9499 ex24
(f) (517) 349-6863
(m) (517) 204-5136

szayko at secor.com
www.secor.com


_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com