Filter from Main Form to SubForm

[Larry Salvucci]

I want to be able to filter my records on my subform based on 2 combo boxes
on my main form. The first combo is "cboYearSelect" and the second is
"cboMonthSelect. I want to first filter them by Year and then by month. The
two forms are linked by the VendorID. I'm using this code in the after update
event for my cboYearSelect:

Me.Filter = "[Forms]![frmSearchCriteriaSub]![Years] = '" & Me!cboYearSelect
& "'"
Me.FilterOn = True

And then this code for the after update event for my cboMonthSelect:

Me.Filter = "[Forms]![frmSearchCriteriaSub]![Months] = '" &
Me!cboMonthSelect & "'"
Me.FilterOn = True

Why won't it filter my data? Everytime I try it I get a parameter box that
pops up looking for [Forms]![frmSearchCriteriaSub]![Years].

 

[Tom Wickerath]

Hi Larry,

I'm not sure what the issue is with your search form. However, if you are
willing to consider using an unbound form, with a subform, then try the
tutorial that is available on the Seattle Access User Group site (soon to be
a page on my new web site, but not quite there yet).

http://www.seattleaccess.org/downloads.htm

See the download for February 12, 2008.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Ken Sheridan]

A subform is not a member of the Forms collection, which only includes open
main forms. Also Me.Filter in the main form's module refers to the parent
form, not the subform.

You reference a subform via the Form property of the subform control, i.e.
the control the parent form housing the subform. As you are basing the
filter on two controls you need to reference both when setting the filter, so
the code for the cboYearSelect and the cboMonthSelect controls' AfterUpdate
event procedures would go like this:

Dim frm as Form
Dim ctrlYear As Control, ctrlMonth As Control
Dim strFilter As String

Set frm = Me.YourSubformControlName.Form
Set ctrlYear = Me.cboYearSelect
Set ctrlMonth = Me.cboMonthSelect

If Not IsNull(ctrlYear) Then
strFilter = "Years = """ & Me.cboYearSelect & """"
End If

If Not IsNull(ctrlMonth) Then
If strFilter <> "" Then
strFilter = strFilter & " And " & _
"Months = """ & Me.cboMonthSelect & """"
Else
strFilter = "Months = """ & Me.cboMonthSelect & """"
End If
End If

frm.Filter = strFilter
frm.FilterOn = True

This assumes Years and Months are both of text data type.

To clear the filter, e.g. with a button on the parent form, you'd use:

Dim frm as Form

Set frm = Me.YourSubformControlName.Form

frm.FilterOn = False

However, if you always want subform filtered to a year and month rather than
showing all the rows normally and being able to filter on demand via the
combo boxes you can do it without any code at all by making the subform
control's LinkMasterFields property:

VendorID;cboYearSelect;cboMonthSelect

and its LinkChildFields property:

VendorID;Years;Months

If the combo boxes are Null when the form opens the subform would be empty,
but once a year and month are selected should automatically update to show
the relevant rows. You could if you wished set the DefaultValue properties
of the combo boxes to Year(Date()) and Month(Date()) so they show the current
year/month when the form opens.

Ken Sheridan
Stafford, England

I want to be able to filter my records on my subform based on 2 combo boxes
on my main form. The first combo is "cboYearSelect" and the second is
"cboMonthSelect. I want to first filter them by Year and then by month. The
two forms are linked by the VendorID. I'm using this code in the after update
event for my cboYearSelect:

Me.Filter = "[Forms]![frmSearchCriteriaSub]![Years] = '" & Me!cboYearSelect
& "'"
Me.FilterOn = True

And then this code for the after update event for my cboMonthSelect:

Me.Filter = "[Forms]![frmSearchCriteriaSub]![Months] = '" &
Me!cboMonthSelect & "'"
Me.FilterOn = True

Why won't it filter my data? Everytime I try it I get a parameter box that
pops up looking for [Forms]![frmSearchCriteriaSub]![Years].

 

[Larry Salvucci]

Hi Ken,

Thanks for your help. One question though...I've got the filters working
properly and I have them setting to the current month & year as the default
value when the form opens but it doesn't filter the data on my subform until
I select something from one of those two combo boxes. It still shows all the
records on my subform even though I have the default values set. Do I need to
requery the subform to have it show just these records?

A subform is not a member of the Forms collection, which only includes open
main forms. Also Me.Filter in the main form's module refers to the parent
form, not the subform.

You reference a subform via the Form property of the subform control, i.e.
the control the parent form housing the subform. As you are basing the
filter on two controls you need to reference both when setting the filter, so
the code for the cboYearSelect and the cboMonthSelect controls' AfterUpdate
event procedures would go like this:

Dim frm as Form
Dim ctrlYear As Control, ctrlMonth As Control
Dim strFilter As String

Set frm = Me.YourSubformControlName.Form
Set ctrlYear = Me.cboYearSelect
Set ctrlMonth = Me.cboMonthSelect

If Not IsNull(ctrlYear) Then
strFilter = "Years = """ & Me.cboYearSelect & """"
End If

If Not IsNull(ctrlMonth) Then
If strFilter <> "" Then
strFilter = strFilter & " And " & _
"Months = """ & Me.cboMonthSelect & """"
Else
strFilter = "Months = """ & Me.cboMonthSelect & """"
End If
End If

frm.Filter = strFilter
frm.FilterOn = True

This assumes Years and Months are both of text data type.

To clear the filter, e.g. with a button on the parent form, you'd use:

Dim frm as Form

Set frm = Me.YourSubformControlName.Form

frm.FilterOn = False

However, if you always want subform filtered to a year and month rather than
showing all the rows normally and being able to filter on demand via the
combo boxes you can do it without any code at all by making the subform
control's LinkMasterFields property:

VendorID;cboYearSelect;cboMonthSelect

and its LinkChildFields property:

VendorID;Years;Months

If the combo boxes are Null when the form opens the subform would be empty,
but once a year and month are selected should automatically update to show
the relevant rows. You could if you wished set the DefaultValue properties
of the combo boxes to Year(Date()) and Month(Date()) so they show the current
year/month when the form opens.

Ken Sheridan
Stafford, England

I want to be able to filter my records on my subform based on 2 combo boxes
on my main form. The first combo is "cboYearSelect" and the second is
"cboMonthSelect. I want to first filter them by Year and then by month. The
two forms are linked by the VendorID. I'm using this code in the after update
event for my cboYearSelect:

Me.Filter = "[Forms]![frmSearchCriteriaSub]![Years] = '" & Me!cboYearSelect
& "'"
Me.FilterOn = True

And then this code for the after update event for my cboMonthSelect:

Me.Filter = "[Forms]![frmSearchCriteriaSub]![Months] = '" &
Me!cboMonthSelect & "'"
Me.FilterOn = True

Why won't it filter my data? Everytime I try it I get a parameter box that
pops up looking for [Forms]![frmSearchCriteriaSub]![Years].

 

[Larry Salvucci]

Hi Ken,

Another follow up question. Say I wanted to just filter my records based on
a year without having to select a month and have it show all the records for
that year until I was to select a month from the month combo box. How would I
do this?

A subform is not a member of the Forms collection, which only includes open
main forms. Also Me.Filter in the main form's module refers to the parent
form, not the subform.

You reference a subform via the Form property of the subform control, i.e.
the control the parent form housing the subform. As you are basing the
filter on two controls you need to reference both when setting the filter, so
the code for the cboYearSelect and the cboMonthSelect controls' AfterUpdate
event procedures would go like this:

Dim frm as Form
Dim ctrlYear As Control, ctrlMonth As Control
Dim strFilter As String

Set frm = Me.YourSubformControlName.Form
Set ctrlYear = Me.cboYearSelect
Set ctrlMonth = Me.cboMonthSelect

If Not IsNull(ctrlYear) Then
strFilter = "Years = """ & Me.cboYearSelect & """"
End If

If Not IsNull(ctrlMonth) Then
If strFilter <> "" Then
strFilter = strFilter & " And " & _
"Months = """ & Me.cboMonthSelect & """"
Else
strFilter = "Months = """ & Me.cboMonthSelect & """"
End If
End If

frm.Filter = strFilter
frm.FilterOn = True

This assumes Years and Months are both of text data type.

To clear the filter, e.g. with a button on the parent form, you'd use:

Dim frm as Form

Set frm = Me.YourSubformControlName.Form

frm.FilterOn = False

However, if you always want subform filtered to a year and month rather than
showing all the rows normally and being able to filter on demand via the
combo boxes you can do it without any code at all by making the subform
control's LinkMasterFields property:

VendorID;cboYearSelect;cboMonthSelect

and its LinkChildFields property:

VendorID;Years;Months

If the combo boxes are Null when the form opens the subform would be empty,
but once a year and month are selected should automatically update to show
the relevant rows. You could if you wished set the DefaultValue properties
of the combo boxes to Year(Date()) and Month(Date()) so they show the current
year/month when the form opens.

Ken Sheridan
Stafford, England

I want to be able to filter my records on my subform based on 2 combo boxes
on my main form. The first combo is "cboYearSelect" and the second is
"cboMonthSelect. I want to first filter them by Year and then by month. The
two forms are linked by the VendorID. I'm using this code in the after update
event for my cboYearSelect:

Me.Filter = "[Forms]![frmSearchCriteriaSub]![Years] = '" & Me!cboYearSelect
& "'"
Me.FilterOn = True

And then this code for the after update event for my cboMonthSelect:

Me.Filter = "[Forms]![frmSearchCriteriaSub]![Months] = '" &
Me!cboMonthSelect & "'"
Me.FilterOn = True

Why won't it filter my data? Everytime I try it I get a parameter box that
pops up looking for [Forms]![frmSearchCriteriaSub]![Years].

 

[Ken Sheridan]

In answer to your first question you might well have to requery the subform
control when the main form opens.

To filter just by year you could do it using the code in my first reply
rather than including the year and month in the linking of the parent form
and subform. That would set the Filter property so it filters solely by year
if the month combo box is Null. Another way would be to base the subform on
a query which references the combo boxes on the parent form, e.g.

SELECT *
FROM YourTable
WHERE VendorID = Forms!YourMainForm!VendorID
AND (YEAR(YourDateField) = Forms!YourMainForm!cboYearSelect
OR Forms!YourMainForm!cboYearSelect IS NULL)
AND (MONTH(YourDateField) = Forms!YourMainForm!cboMonthSelect
OR Forms!YourMainForm!cboMonthSelect IS NULL);

By testing the two controls for NULL within the parenthesised expressions,
it in effect makes selecting a year or month optional, so if neither were
selected all rows for the current vendor would be returned, if only a year is
selected only rows for the current vendor and selected year would be
returned. It would also be possible to select just a month, but as that
would return rows for that month in every year for which there are rows for
the current vendor it wouldn’t make a lot of sense to do so. If a query
which references the controls like this is used then you'd need to requery
the subform control in the AfterUpdate event procedures of both combo boxes.

Ken Sheridan
Stafford, England

Hi Ken,

Another follow up question. Say I wanted to just filter my records based on
a year without having to select a month and have it show all the records for
that year until I was to select a month from the month combo box. How would I
do this?

A subform is not a member of the Forms collection, which only includes open
main forms. Also Me.Filter in the main form's module refers to the parent
form, not the subform.

You reference a subform via the Form property of the subform control, i.e.
the control the parent form housing the subform. As you are basing the
filter on two controls you need to reference both when setting the filter, so
the code for the cboYearSelect and the cboMonthSelect controls' AfterUpdate
event procedures would go like this:

Dim frm as Form
Dim ctrlYear As Control, ctrlMonth As Control
Dim strFilter As String

Set frm = Me.YourSubformControlName.Form
Set ctrlYear = Me.cboYearSelect
Set ctrlMonth = Me.cboMonthSelect

If Not IsNull(ctrlYear) Then
strFilter = "Years = """ & Me.cboYearSelect & """"
End If

If Not IsNull(ctrlMonth) Then
If strFilter <> "" Then
strFilter = strFilter & " And " & _
"Months = """ & Me.cboMonthSelect & """"
Else
strFilter = "Months = """ & Me.cboMonthSelect & """"
End If
End If

frm.Filter = strFilter
frm.FilterOn = True

This assumes Years and Months are both of text data type.

To clear the filter, e.g. with a button on the parent form, you'd use:

Dim frm as Form

Set frm = Me.YourSubformControlName.Form

frm.FilterOn = False

However, if you always want subform filtered to a year and month rather than
showing all the rows normally and being able to filter on demand via the
combo boxes you can do it without any code at all by making the subform
control's LinkMasterFields property:

VendorID;cboYearSelect;cboMonthSelect

and its LinkChildFields property:

VendorID;Years;Months

If the combo boxes are Null when the form opens the subform would be empty,
but once a year and month are selected should automatically update to show
the relevant rows. You could if you wished set the DefaultValue properties
of the combo boxes to Year(Date()) and Month(Date()) so they show the current
year/month when the form opens.

Ken Sheridan
Stafford, England

I want to be able to filter my records on my subform based on 2 combo boxes
on my main form. The first combo is "cboYearSelect" and the second is
"cboMonthSelect. I want to first filter them by Year and then by month. The
two forms are linked by the VendorID. I'm using this code in the after update
event for my cboYearSelect:

Me.Filter = "[Forms]![frmSearchCriteriaSub]![Years] = '" & Me!cboYearSelect
& "'"
Me.FilterOn = True

And then this code for the after update event for my cboMonthSelect:

Me.Filter = "[Forms]![frmSearchCriteriaSub]![Months] = '" &
Me!cboMonthSelect & "'"
Me.FilterOn = True

Why won't it filter my data? Everytime I try it I get a parameter box that
pops up looking for [Forms]![frmSearchCriteriaSub]![Years].

Ken Sheridan
Stafford, England