Filtering a subform using VBA

[Guest]

Hi,

I want to filter my subform using a combobox on my main form. I know how to
filter using VBA if all the combo boxes are on the same form, but I am not
yet VB-savvy enough to work out what the code would be to do the same thing
on a subform. If I was doing it all on the main form my code would read

Private Sub cmdApplyFilter_Click()
Me.Form.Filter = "[cboType] = '" & Me.cboFormType & "'"
Me.Form.FilterOn = True
End Sub

But that only works if [cboType] is on the main form, but it is instead a
field on my subform. My main form is called frmFormsSearchMAIN and my subform
is called frmFormsSearchSUB.

If anyone can tell me how to code this I would really appreciate it!

Thank you

 

[Allen Browne]

In the main form's module, you can refer to the subform's form as:
Me.frmFormsSearchSUB.Form

From the subform's module, you can refer to the combo on the main form like
this:
Me.Parent!cboFormType

So, you need something like this:

Private Sub cmdApplyFilter_Click()
Dim strFilter As String

With Me.frmFormsSearchSUB.Form
strFilter = "[cboType] = """ & .cboFormType & """"
.Filter = strFilter
.FilterOn = True
End With
End Sub

However, you don't want to filter both the main form and the subform at the
same time, or Access will get confused, as explained here:
Incorrect filtering
at:
http://allenbrowne.com/bug-02.html

 

[Rick Brandt]

Hi,

I want to filter my subform using a combobox on my main form. I know
how to filter using VBA if all the combo boxes are on the same form,
but I am not yet VB-savvy enough to work out what the code would be
to do the same thing on a subform. If I was doing it all on the main
form my code would read

Private Sub cmdApplyFilter_Click()
Me.Form.Filter = "[cboType] = '" & Me.cboFormType & "'"
Me.Form.FilterOn = True
End Sub

But that only works if [cboType] is on the main form, but it is
instead a field on my subform. My main form is called
frmFormsSearchMAIN and my subform is called frmFormsSearchSUB.

If anyone can tell me how to code this I would really appreciate it!

Thank you

Can't you just use the MasterLink and ChildLink properties to get the desired
behavior?