Filter a subform from main form

[Guest]

I'd like to filter a subform from a main form using VBA. I've set up a combo
box control and written code to store the value selected in the combo box. I
am having trouble writing the code such that it will filter the subform. Can
anyone help?

The following is the code I used. It does not filter the subform, but
instead it calls up another form and applies the filter to this form. This
works fine. Can this code be modified to filter a subform? If so, what code
should I use and where?

Dim frm As Form
Dim strInput As Variant
Dim strFilter As String

strInput = [ComboBox_Project] ' store value selected by user in combo
box
DoCmd.OpenForm "frm_Reports_Datasheet", acFormDS ' open form
Set frm = Forms!frm_Reports_Datasheet

' Build criteria string
strFilter = BuildCriteria("PROJ_NAME", dbText, strInput)

' Set Filter property to apply filter
frm.Filter = strFilter

' Set FilterOn property to show filtered records
frm.FilterOn = True

 

[Guest]

No sooner did I enter a plea for help, then I suddenly got the filter to
work. For the benefit of those who are beginners or have little programming
experience, here's a sample of the code:

'Declare variables
Dim frm As Form
Dim strInput As Variant
Dim strFilter As String

strInput = [ComboBox_Project] ' store value selected in combo box by user

' This is the line of code that refers to the subform from the main form:
Set frm = Forms![name_of_main_form]![name_of_subform/subreport].Form

' Build criteria string
strFilter = BuildCriteria("PROJ_NAME", dbText, strInput)

' Set Filter property to apply filter
frm.Filter = strFilter

' Set FilterOn property to show filtered records
frm.FilterOn = True


Notes:

The name of the subform (or subreport) should be the name that is stored in
the Name text box in the properties box.

The code can be pasted and used in a separate Private Sub procedure. You can
refer to this procedure using a command button and ON CLICK event and that
has a one line statement calling up this procedure.

Create a combo box pointing to a query or table with data you would like to
populate in the combo box. Name your combo box. Replace [ComboBox_Project]
with the name of your combo control.

For BuildCriteria, I have specified "PROJ_NAME", which is the first field
used under the Row Source in the properties box. You will have to replace
this field name with something else. Note: your combo box may be reading data
from another query or table, but it must remain unbound (i.e., leave Control
Source blank in Properties). The 'dbtext' part of BuildCriteria merely tells
Access that what you selected in the combo box is text. See Access online
help for more details.

To control Null values, use error trapping.

Disclaimer: Not responsible if anything goes wrong. Use at own risk. My
solution doesn't cover all the details, but at least this will help beginners
get started. :-D

Cheers,
Rupertsland

I'd like to filter a subform from a main form using VBA. I've set up a combo
box control and written code to store the value selected in the combo box. I
am having trouble writing the code such that it will filter the subform. Can
anyone help?

The following is the code I used. It does not filter the subform, but
instead it calls up another form and applies the filter to this form. This
works fine. Can this code be modified to filter a subform? If so, what code
should I use and where?

Dim frm As Form
Dim strInput As Variant
Dim strFilter As String

strInput = [ComboBox_Project] ' store value selected by user in combo
box
DoCmd.OpenForm "frm_Reports_Datasheet", acFormDS ' open form
Set frm = Forms!frm_Reports_Datasheet

' Build criteria string
strFilter = BuildCriteria("PROJ_NAME", dbText, strInput)

' Set Filter property to apply filter
frm.Filter = strFilter

' Set FilterOn property to show filtered records
frm.FilterOn = True

 

[Marshall Barton]

I'd like to filter a subform from a main form using VBA. I've set up a combo
box control and written code to store the value selected in the combo box. I
am having trouble writing the code such that it will filter the subform. Can
anyone help?

The following is the code I used. It does not filter the subform, but
instead it calls up another form and applies the filter to this form. This
works fine. Can this code be modified to filter a subform? If so, what code
should I use and where?

Dim frm As Form
Dim strInput As Variant
Dim strFilter As String

strInput = [ComboBox_Project] ' store value selected by user in combo
box
DoCmd.OpenForm "frm_Reports_Datasheet", acFormDS ' open form
Set frm = Forms!frm_Reports_Datasheet

' Build criteria string
strFilter = BuildCriteria("PROJ_NAME", dbText, strInput)

' Set Filter property to apply filter
frm.Filter = strFilter

' Set FilterOn property to show filtered records
frm.FilterOn = True

You can use a similar approach with a subform:

Dim strInput As Variant
Dim strFilter As String
' store value selected by user in combo box
strInput = [ComboBox_Project]
' Build criteria string
strFilter = BuildCriteria("PROJ_NAME", dbText, strInput)
Me.subformcontrol.Form.Filter = strFilter
Me.subformcontrol.Form.FilterOn = True

By the way, you can open the other form with less code by
using the OpenForm method's WhereCondition argument:

Dim strInput As Variant
Dim strFilter As String
' store value selected by user in combo box
strInput = [ComboBox_Project]
' Build criteria string
strFilter = BuildCriteria("PROJ_NAME", dbText, strInput)
DoCmd.OpenForm "frm_Reports_Datasheet", acFormDS, , _
strFilter