FilterByForm a Subform

  • Thread starter Thread starter Mike C.
  • Start date Start date
M

Mike C.

Hello.

I am working on a database whose main input form has a
subform which contains a list of items. When I do a
FilterByForm action and choose items from the
subform "subfrom_groups", the filter is not applied. At
first I thought it was just my database, but when I tried
it on the Northwind database, I get the same result.

I looked up this issue on the Microsoft Knowledge Base
(209482) and they say that it is a known problem but do
not give a fix.

Any help would be appreciated.

Thanks in advance,

MC
 
Hi MC.

Yes, this is another problem with filters in Access, and AFAIK, there is no
fix.

A workaround might be to provide some unbound boxes on the main form where
the user can choose their criteria values and then click a button to apply
the filter. Your command button can set the Filter property of the subform.

This workaround provides a more obvious filtering options for the user, and
avoids the other problems associated with Filter By Form. You will
eventually want to create an MDE for your users to prevent
security/corruption issues, and Filter By Form does not work with MDEs
anyway.

Post back if you need further details on how to develop this workaround.
 
Thanks for the reply. I was affraid you would say there
was no fix :o(

As for the workaround, I would appreciate further details
on the work around.

I did find a website with a fix on it, by you:

http://members.iinet.net.au/~allenbrowne/ser-28.html

Is this the work around you are talking about. If so, I
will try this.

Thanks again for your help.

MC
 
The article you refer to is about filtering the main form so it contains
only records that have a match in the subform. If you are working with a
bound main form and want to do that, then follow the article.

However, if you just need to apply a filter to the subform from the
(unbound?) main form, you can just set the Filter property of the subform.
This example assumes unbound text boxes on the main form where the user can
enter a surname and firstname to match against fields in the subform. It can
easily be extented to add other combos/text boxes as well.

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.txtSurname) Then
strWhere = strWhere & "([Surname] = """ & txtSurname & """) AND "
End If
If Not IsNull(Me.txtFirstName) Then
strWhere = strWhere & "([FirstName] = """ & txtFirstName & """) AND
"
End If
'etc for other boxes.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "no criteria found"
Else
With Me.[NameOfYourSubformHere].Form
.Filter = Left(strWhere, lngLen)
.FilterOn = True
End With
End If
End Sub
 
Back
Top