Filter a form based on a value in a subform

K

Katie

I have a form with several subforms. I want to create a button that will
filter the form to show only those records that have a particular value in
one of the fields on the subform.

The form is a customer form, and the subform lists potential sales to that
customer. I want to filter the customer list to show only those with
potential sales in the current quarter (this is the field in the subform.

Any ideas?

Thanks in advance.
 
O

Ofer

You can use the filter of the form, add this code to the Onclick event of
the button that located on the main form

' Use that if the field that you want to filter on type is number
Me.Filter = "[Field Name In the table] = " & Me.[Sub From control
name].form![Field Name in the sub form]
Me.FilterOn = True

' Use that if the field that you want to filter on type is text
Me.Filter = "[Field Name In the table] = '" & Me.[Sub From control
name].form![Field Name in the sub form] & "'"
Me.FilterOn = True
 
A

Allen Browne

Two solutions.

1. Use an Inner Join in the RecordSource of the main form, so you get the
same result as a filter. Details in:
Filter a Form on a Field in a Subform
at:
http://allenbrowne.com/ser-28.html

2. Create a subquery to use in the Filter of the form. The Filter is
actually the WHERE clause of a query, so anything you can use the a WHERE
clause can go in the filter string.

This kind of thing:

Dim strWhere As String 'Filter string.
Dim dt As Date 'Start of the quarter.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'Date format for JET.

dt = DateSerial(Year(Date), 3 * (DatePart("q", Date) - 1) - 2, 1)
strWhere = "EXISTS (SELECT SaleID FROM tblSale " & _
"WHERE (tblSale.CustomerID = " & Me.CustomerID & _
") AND (tblSale.SaleDate Between " & Format(dt, conJetDate) & _
" And " & Format(DateAdd("q", 1, dt) - 1, conJetDate) & "))"

If Me.Dirty Then Me.Dirty = False 'save first.
Me.Filter = strWhere
Me.FilterOn = True


If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066
 
K

Katie

Thank you. The website you linked to helped, and was exactly what I was
looking for! I ended up creating a query using the inner joins as de facto
filter, and using that as the alternate record source.
 
Top