Filtering a subform

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

Guest

Hi there,

I have a search form and a text box on it into which the user will type the
item they want to filter on. I have a main form with a subform and I want
the form to open filtering the subform's records based on what is typed into
form search.
On my main form, I have based it on the same query as the subform but am not
displaying any of the fields. One of these is called "Vendor" and I have
tried the following:

If IsOpen("frmSearch") Then
Me.frmExpenses_sub.Filter = Me.Vendor
Me.frmExpenses_sub.FilterOn = True
End If

The click event of the appropriate button on the search form has the
following code:
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmExpenses"
stLinkCriteria = "[strVendor]like" & "'*" & Me![txtExpVend] & "*'"
'wildcard search
DoCmd.OpenForm stDocName, , , stLinkCriteria

This works fine and when I make the "Vendor" field visible shows the correct
info but I would like to pass this onto my subform. What am I doing wrong?

Thanks for any help offered!

JR
 
You are not displaying anything in the main form except the unbound boxes
where the user enters their search criteria, so the main form does not need
anything in its RecordSource. In fact, you don't need a main form at all:
you could just use a Continuous view subform, with the unbound boxes in its
Form Header section.

This solves all the problems of trying to filter both the main form and the
subform. Most importantly, it also works around the bugs in Access that
happen when you apply filters to both a main form and a subform. Details of
the bugs:
Incorrect filtering of forms and reports
at:
http://allenbrowne.com/bug-02.html

There is a sample database that shows this kind of technique (using multiple
filter boxes in conjunction) in this link:
http://allenbrowne.com/unlinked/Search2000.zip
Requires Access 2000 or later.

If you really do need to filter a main form based on records that are in a
subform bound to a different (related) table, see:
Filter a Form on a Field in a Subform
at:
http://allenbrowne.com/ser-28.html
 
Back
Top