Filter by Field in Subform - Repost

[Jaybird]

I need to filter a main form based on fields in my subform. For example, my
main form has a field [Order Number] and the header contains standard
information about the origin of the parts and what is to be done with them,
like the PO Number, and Customer information. My subform contains
information like the Part Number, Qty, the ID Number for that particular line
item, etc. My boss needs to be able to tell how many orders contain Part
Number so and so... information that is specific to the underlying table in
the subform, but he also needs the associated information in the main form.
Before I normalized the table structure, all of this information was
contained on one table, and could easily be searched and sorted and filtered
using the tools on the menu bar. Well, I've gone and screwed that all up,
and the users are gunning for my blood. I need to restore these features
pronto. I attempted to implement Allen Browne's 'Filter a Form on a Field in
a Subform' at: http://allenbrowne.com/ser-28.html but I had a lot of trouble
with the coding. I couldn't get my SQL to be accepted as my new
recordsource. Anyhow, I'm thinking of dropping that technique in favor of a
popup form that contains the queried information, using a query as the
recordsource that includes the table from the subform as search criteria.
It's working, but I just thought I'd ask if you guys thought I did the right
thing.

 

[OldPro]

I need to filter a main form based on fields in my subform. For example, my
main form has a field [Order Number] and the header contains standard
information about the origin of the parts and what is to be done with them,
like the PO Number, and Customer information. My subform contains
information like the Part Number, Qty, the ID Number for that particular line
item, etc. My boss needs to be able to tell how many orders contain Part
Number so and so... information that is specific to the underlying table in
the subform, but he also needs the associated information in the main form.
Before I normalized the table structure, all of this information was
contained on one table, and could easily be searched and sorted and filtered
using the tools on the menu bar. Well, I've gone and screwed that all up,
and the users are gunning for my blood. I need to restore these features
pronto. I attempted to implement Allen Browne's 'Filter a Form on a Field in
a Subform' at: http://allenbrowne.com/ser-28.htmlbut I had a lot of trouble
with the coding. I couldn't get my SQL to be accepted as my new
recordsource. Anyhow, I'm thinking of dropping that technique in favor of a
popup form that contains the queried information, using a query as the
recordsource that includes the table from the subform as search criteria.
It's working, but I just thought I'd ask if you guys thought I did the right
thing.

I doubt you will find anyone who recommends merging a header and
detail table into one table. Your post displays a general level of
inexperience. I recommend going to Yahoo or Google and doing a search
for MS Access tutorials. Walk through a few of them. Then go to the
bookstore, and get two or three different books on Access. Try the
examples until you are comfortable. It isn't enough to go to the
ocean and bring back a bucket of water. Dive in!

 

[Jaybird]

OldPro,

Thanks for the response! Yeah, I agree, I'm missing a few of the important
lessons I need to learn. However, short of Allen Browne's solution, and a
few others (apologies to you all, I can't remember the names!), I can find
very little reference to filtering data in a main form by fields in a
subform. My popup form solution is very versatile and does what I want, but
the users hate it. Mostly, I think because they are used to being able to
sort and filter from the original form. I must admit, it seems a little
clunky to me. Normalizing the table data makes a lot of sense to me, but in
this case, it also causes some problems. I've been developing other forms
that really MUST have a normalized table structure, so I can't really go back
to the old way of doing things. If you have any pointers for filtering data
in the main form by data in the subform, I would appreciate it. My main
problem is (I think) making my SQL statement the new record source. I'm
pretty sure my syntax is incorrect. I've already deleted it, or I would post
it here. Your response is prompting me to try again. I will post again if I
continue to have trouble.
--
Why are you asking me? I dont know what Im doing!

Jaybird

I need to filter a main form based on fields in my subform. For example, my
main form has a field [Order Number] and the header contains standard
information about the origin of the parts and what is to be done with them,
like the PO Number, and Customer information. My subform contains
information like the Part Number, Qty, the ID Number for that particular line
item, etc. My boss needs to be able to tell how many orders contain Part
Number so and so... information that is specific to the underlying table in
the subform, but he also needs the associated information in the main form.
Before I normalized the table structure, all of this information was
contained on one table, and could easily be searched and sorted and filtered
using the tools on the menu bar. Well, I've gone and screwed that all up,
and the users are gunning for my blood. I need to restore these features
pronto. I attempted to implement Allen Browne's 'Filter a Form on a Field in
a Subform' at: http://allenbrowne.com/ser-28.htmlbut I had a lot of trouble
with the coding. I couldn't get my SQL to be accepted as my new
recordsource. Anyhow, I'm thinking of dropping that technique in favor of a
popup form that contains the queried information, using a query as the
recordsource that includes the table from the subform as search criteria.
It's working, but I just thought I'd ask if you guys thought I did the right
thing.

I doubt you will find anyone who recommends merging a header and
detail table into one table. Your post displays a general level of
inexperience. I recommend going to Yahoo or Google and doing a search
for MS Access tutorials. Walk through a few of them. Then go to the
bookstore, and get two or three different books on Access. Try the
examples until you are comfortable. It isn't enough to go to the
ocean and bring back a bucket of water. Dive in!

 

[Jaybird]

Now that I'm thinking about it, I'm not even sure that Allen Browne's
solution will do what I want. It seems to me that changing the recordsource
to a query such as he suggests will disable my form's primary function:
Order Entry. The modified query would render the form non updatable. There
would have to be a mechanism for restoring the form back to its original
recordsource. Steve Schapel was another MVP who suggested a similar
approach. However, I think this method is best suited for a situation where
a second form is opened to the filtered records by some method from the main
form. If so, I don't believe that this solution is any better than my own.
As I said earlier, it is very flexible and easy to use, as well as simple to
understand, but it does remove the user from the main form (which my users
hate). My solution brings up a non updatable version of the main form that
joins the Orders table to the Order Details table in its recordsource. This
is, I think, similar to what Allen and Steve are suggesting. This difference
is that their form is already filtered and mine is not. Do you think I am
correct? Do you have any other suggestions for filtering by data in the
subform?

 

[OldPro]

Now that I'm thinking about it, I'm not even sure that Allen Browne's
solution will do what I want. It seems to me that changing the recordsource
to a query such as he suggests will disable my form's primary function:
Order Entry. The modified query would render the form non updatable. There
would have to be a mechanism for restoring the form back to its original
recordsource. Steve Schapel was another MVP who suggested a similar
approach. However, I think this method is best suited for a situation where
a second form is opened to the filtered records by some method from the main
form. If so, I don't believe that this solution is any better than my own.
As I said earlier, it is very flexible and easy to use, as well as simple to
understand, but it does remove the user from the main form (which my users
hate). My solution brings up a non updatable version of the main form that
joins the Orders table to the Order Details table in its recordsource. This
is, I think, similar to what Allen and Steve are suggesting. This difference
is that their form is already filtered and mine is not. Do you think I am
correct? Do you have any other suggestions for filtering by data in the
subform?

Okay, let me clear up a few things...
1) The form that you use to input new orders is designed to do that
and little else.
2) Your boss wants to know what orders contain a particular item. You
could do this by creating a new form that is linked to a query. You
could also do this by creating a report.
3) A listbox can display a SELECT query. For example, the following
code lists customer names and their open orders:
listbox.rowsource = "SELECT FirstName, LastName, OrderNumber FROM
tblCustomers INNER JOIN tblOrders ON tblCustomers.CustomerNumber =
tblOrders.CustomerNumber WHERE tblOrders.OpenOrder = True"
3) There are several strategies for managing multiple forms. I use a
main form which is always in the background, that shows popular
queries and has a menu with which several other forms can be called.
The secondary forms are non-modal and non-popup, but I call them with
the acDialog parameter which makes them modal and popup.
4) Menus can be either the top-of-the-page dropdown type, or a series
of command buttons. The main Access menu can be turned off, or left
alone.

 

[Jaybird]

1) The form that you use to input new orders is designed to do that
and little else.

Well, we'd like it to be fully filterable and searchable, but I can't figure
out a way to do it with the current normalized table structure.

2) Your boss wants to know what orders contain a particular item. You
could do this by creating a new form that is linked to a query.

We may be saying the same thing in two different languages here... My
search form is kind of a data clone of the Order Entry form. It has all the
same fields, but the recordsource is a query which joins my Orders and Order
Details table together on the Order ID field. It is not updatable, but is
fully filterable, sortable, and searchable. It is called by a command button
on the main form.

You could also do this by creating a report.

Yes, but reports aren't dynamic enough for our purposes.

3) A listbox can display a SELECT query. For example, the following
code lists customer names and their open orders:
listbox.rowsource = "SELECT FirstName, LastName, OrderNumber FROM
tblCustomers INNER JOIN tblOrders ON tblCustomers.CustomerNumber =
tblOrders.CustomerNumber WHERE tblOrders.OpenOrder = True"

I had started out doing just this, but it was taking forever to complete
when I thought of the popup form solution.

It's starting to look as if I might have a good solution after all. I know
my users hate having to bring up a new form, but they may just have to live
with it. If you have any alternatives for filtering the main form by
criteria in the subform, I would love to hear them. I guess I could always
have filter and sort features built into a set of combo boxes, but my
thoughts are that it is still too limited for the sorts of dynamic filters
and searches that we do here. I'm just trying to make sure that there isn't
a better solution out there somewhere.[/QUOTE]