Filter a form based on a value in a subform

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
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
 
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.
 
Back
Top