Show all records through combo box

Well i would guess that a filter isnt exactly used the way you think that it
might. It's weird, because the filter button on the toolbar is disabled. So
when a MCReport is selected, the filter remains disabled.

For the tables there is a Suppliers table, which has the Supplier_ID and the
Supplier (name of the supplier). The Products and MCReport are set up in the
same fashion. Then there is a linking table. In this table there are 3
foreign keys: Supplier_ID, Product_ID and MCReport_ID. These are the only
fields in the table. The other tables are set up in this fashion also, so
the structure is uniform throughout the Database.

"BruceM" wrote:

Have you tried removing the filter using *exactly* what I have suggested?

What are the relationships between the tables?

"JKarchner" wrote in message
...
I am sorry that i have confused you even more. Let's see if i can explain
this better. I should not have used the term report to describe what it
is I
am trying to do, because this is a DB term. I should say that the report
i
was refering to is a MCReport (multi-client report). The MCReport is one
of
the main ways all the data is related. Each company appears in at least
one
MCReport. And in each MCReport, the company supplies different Products,
has
different Customers, Affiliates, Strategies, and Processes. Products,
Customers, Affiliates, Strategies, Processes, Companies, and MCReports are
all tables. Hopefully this is starting to clear things up. The subform
for
Products used to display all products that the company supplied for all
MCReports. Now that i have added the combo box, only products that the
company supplied for the chosen MCReport are displayed. Like i said i
linked
the subforms to the main form using the Company_ID and the MCReport_ID.
The
MCReport_ID is not field that is associated with the query that returns
all
of the company names that is tied to the main form. Instead it is solely
used on the combo box, and this is where i believe i am having my problem.
Now there is code linked to After Update on the combo box, however it
calls a
requery only for the subform associated with the Products subform.
Because
all of the other subforms change according to the MCReport selected in the
combo box, i do not think that the code in the After Update is necessary
let
alone working. There is no code in the filter section. This question has
not deviated from my original question, rather it is much much more
complex
than originally thought. My question is still: how do i go about
displaying
all records for that company in each subform using the combo box? (i.e.
all
products supplied by said company regardless of MCReport, all process used
by
company regardless of MCReport, and so on and so forth.)

For now let's only concentrate on this matter and if we ever come to a
solution i can ask about my other question.

I hope that this clarifies things for you and again i thank you for any
help
you can provide. I will try to look through the communities again to find
any previous topics about this to see if i can come across any information
that will be helpful in completing this.

"BruceM" wrote:

I'm still confused. What do you mean by the following:
"Before I added the combo box ... to filter the data, each subform
displayed
the data across all of the report types." If the subforms contain
related
records, what are you filtering? And what do you mean by "report types"?

"I added an unbound combo box (choices are the names of the reports) and
set
up links that link the subforms to the combo box in order to filter the
data
by report." Filtering data by report means nothing to me. You can
filter
by form, and by selection, and maybe something else I've forgotten, but
not
by report. Where did the reports come from anyhow? How are they
connected
to the subform?

"I did not inlude any events tied to After Update for any of the
subforms."
When using a combo box for filtering you would typically use the combo
box
After Update event to apply the filter. Does the combo box have an After
Update event? If so, what is it? If not, how is the filter being
applied?
What is the filter?

To open the form with the filter off, place the line of code I have
mentioned several times into the form's Open event.

Have you tried the command button I mentioned? It would remove the
filter.
The methods in the links I provided would also accomplish that.

You will need to describe in non-database terms just what you want to
accomplish. You will also need to provide your table structure and
relationships. What you are asking now is far, far from your original
question.

"JKarchner" wrote in message
...
I'm sorry if I am confusing you about this matter i will try to explain
it
better. The main form is a single form that displays one company at a
time.
For each company there this 5 subforms, that each hold information
regarding
customers, products, processes, strategies, and affiliates. Before i
added
the combo box (the combo box is on the main form and not on the
subforms)
to
filter the data, each subform displayed the data across all of the
report
types. I added an unbound combo box (choices are the names of the
reports)
and set up links that link the subforms to the combo box in order to
filter
the data by report. When i did this i lost the option to display all
of
the
data in subforms, and that is when i asked for your help in order to
display
all of the data. I did not inlude any events tied to After Update for
any
of
the subforms. It appears that the link fields are solely what are
filtering
the data in the subforms. I do not know if this was the correct way to
go
about doing this or not. Of all the suggestions i found for filtering
the
data, this method seemed to be the only one that worked. If you have a
better method to go about doing this i would appreciate it.

By default, i mean that when i first open up the form i want all data
to
appear, i do not want it to be filtered at all. I would like the
option
to
be able to "go back" and view all data again after a certain report is
selected in the combo box.

Let me know if you need any more explanation than this, as i believe
that
i
can answer any questions you might have. Again thank you very much for
helping me do this. It is greatly appreciated.

"BruceM" wrote:

The union query is to add (All) to the recordset. Without the union
query
you would probably need to create a dummy record. By the way, there
are
two
choices at the link. One is to use a user-defined function, the other
is
to
use a union query. There is no union function as such.

What do you mean "default choice in the combo box"? Do you want (All)
to
appear any time you move to another record? What exactly do you want
to
accomplish?

If you are filtering the data, do you want to remove the filter, or do
you
want to go from one filtered recordset to another? I assume the point
of
(All) is to remove any filters. If so, the link provides two options
for
doing so by way of a combo box. An "All Records" command button as I
described is another way.

"JKarchner" wrote in message
...
Thank you for the help. I was having trouble trying to find some of
the
results. They all vary slightly and some are a lot different than
what
i
want.

Ok the page you sent me to shows how to add "All" to my combo box.
I
do
not
need to use the union function to display all of the data, because
my
data
should already be there. The combo box is what is filtering out
most
of
the
data to only the needed results. Also how would you go about
setting
it
up
so that "All" is the default choice in the combo box. I tried
setting
the
default value to "All" but i was given an error.

Again, im sorry that this request has been asked many times, i just
thought
that this varied enough from the others to be asked. Thank you for
any
help
you can provide.

"BruceM" wrote:

If you mean to select an All button from the combo box, you could
try
the
method he
http://support.microsoft.com/?id=210290
You could also just add a command button with the following in its
Click
event:

Me.FilterOn = False

That code could go elsewhere, such as the combo box double-click
event,
if
you like. It would be more efficient for the user than having to
select
something from the combo box, and much simpler for you to set up.

This question has been asked and answered before. A Google groups
search
should lead you to a variety of ideas.

"JKarchner" wrote in message
...
I currently have a combo box set up to filter records in all of my
subforms.
How would i go about using the combo box to show all records at
the
same
time?

You would have a linking table (also known as a junction table) in a
many-to-many relationship. In the case of Suppliers and Products, each
supplier can have many products and each product can be associated with many
suppliers. Similarly, each supplier can be associated with many MCReports,
and each MCReport can be associated with many suppliers. Is this the case?
I can only help if I understand the situation. To do that, I don't need to
know about combo boxes and filters and queries, but rather the real-life
situation. For instance, what is an MCReport?

"JKarchner" wrote in message
...
Well i would guess that a filter isnt exactly used the way you think that
it
might. It's weird, because the filter button on the toolbar is disabled.
So
when a MCReport is selected, the filter remains disabled.

For the tables there is a Suppliers table, which has the Supplier_ID and
the
Supplier (name of the supplier). The Products and MCReport are set up in
the
same fashion. Then there is a linking table. In this table there are 3
foreign keys: Supplier_ID, Product_ID and MCReport_ID. These are the only
fields in the table. The other tables are set up in this fashion also, so
the structure is uniform throughout the Database.

"BruceM" wrote:

Have you tried removing the filter using *exactly* what I have suggested?

What are the relationships between the tables?

"JKarchner" wrote in message
...
I am sorry that i have confused you even more. Let's see if i can
explain
this better. I should not have used the term report to describe what
it
is I
am trying to do, because this is a DB term. I should say that the
report
i
was refering to is a MCReport (multi-client report). The MCReport is
one
of
the main ways all the data is related. Each company appears in at
least
one
MCReport. And in each MCReport, the company supplies different
Products,
has
different Customers, Affiliates, Strategies, and Processes. Products,
Customers, Affiliates, Strategies, Processes, Companies, and MCReports
are
all tables. Hopefully this is starting to clear things up. The
subform
for
Products used to display all products that the company supplied for all
MCReports. Now that i have added the combo box, only products that the
company supplied for the chosen MCReport are displayed. Like i said i
linked
the subforms to the main form using the Company_ID and the MCReport_ID.
The
MCReport_ID is not field that is associated with the query that returns
all
of the company names that is tied to the main form. Instead it is
solely
used on the combo box, and this is where i believe i am having my
problem.
Now there is code linked to After Update on the combo box, however it
calls a
requery only for the subform associated with the Products subform.
Because
all of the other subforms change according to the MCReport selected in
the
combo box, i do not think that the code in the After Update is
necessary
let
alone working. There is no code in the filter section. This question
has
not deviated from my original question, rather it is much much more
complex
than originally thought. My question is still: how do i go about
displaying
all records for that company in each subform using the combo box? (i.e.
all
products supplied by said company regardless of MCReport, all process
used
by
company regardless of MCReport, and so on and so forth.)

For now let's only concentrate on this matter and if we ever come to a
solution i can ask about my other question.

I hope that this clarifies things for you and again i thank you for any
help
you can provide. I will try to look through the communities again to
find
any previous topics about this to see if i can come across any
information
that will be helpful in completing this.

"BruceM" wrote:

I'm still confused. What do you mean by the following:
"Before I added the combo box ... to filter the data, each subform
displayed
the data across all of the report types." If the subforms contain
related
records, what are you filtering? And what do you mean by "report
types"?

"I added an unbound combo box (choices are the names of the reports)
and
set
up links that link the subforms to the combo box in order to filter
the
data
by report." Filtering data by report means nothing to me. You can
filter
by form, and by selection, and maybe something else I've forgotten,
but
not
by report. Where did the reports come from anyhow? How are they
connected
to the subform?

"I did not inlude any events tied to After Update for any of the
subforms."
When using a combo box for filtering you would typically use the combo
box
After Update event to apply the filter. Does the combo box have an
After
Update event? If so, what is it? If not, how is the filter being
applied?
What is the filter?

To open the form with the filter off, place the line of code I have
mentioned several times into the form's Open event.

Have you tried the command button I mentioned? It would remove the
filter.
The methods in the links I provided would also accomplish that.

You will need to describe in non-database terms just what you want to
accomplish. You will also need to provide your table structure and
relationships. What you are asking now is far, far from your original
question.

"JKarchner" wrote in message
...
I'm sorry if I am confusing you about this matter i will try to
explain
it
better. The main form is a single form that displays one company at
a
time.
For each company there this 5 subforms, that each hold information
regarding
customers, products, processes, strategies, and affiliates. Before
i
added
the combo box (the combo box is on the main form and not on the
subforms)
to
filter the data, each subform displayed the data across all of the
report
types. I added an unbound combo box (choices are the names of the
reports)
and set up links that link the subforms to the combo box in order to
filter
the data by report. When i did this i lost the option to display
all
of
the
data in subforms, and that is when i asked for your help in order to
display
all of the data. I did not inlude any events tied to After Update
for
any
of
the subforms. It appears that the link fields are solely what are
filtering
the data in the subforms. I do not know if this was the correct way
to
go
about doing this or not. Of all the suggestions i found for
filtering
the
data, this method seemed to be the only one that worked. If you
have a
better method to go about doing this i would appreciate it.

By default, i mean that when i first open up the form i want all
data
to
appear, i do not want it to be filtered at all. I would like the
option
to
be able to "go back" and view all data again after a certain report
is
selected in the combo box.

Let me know if you need any more explanation than this, as i believe
that
i
can answer any questions you might have. Again thank you very much
for
helping me do this. It is greatly appreciated.

"BruceM" wrote:

The union query is to add (All) to the recordset. Without the
union
query
you would probably need to create a dummy record. By the way,
there
are
two
choices at the link. One is to use a user-defined function, the
other
is
to
use a union query. There is no union function as such.

What do you mean "default choice in the combo box"? Do you want
(All)
to
appear any time you move to another record? What exactly do you
want
to
accomplish?

If you are filtering the data, do you want to remove the filter, or
do
you
want to go from one filtered recordset to another? I assume the
point
of
(All) is to remove any filters. If so, the link provides two
options
for
doing so by way of a combo box. An "All Records" command button as
I
described is another way.

"JKarchner" wrote in message
...
Thank you for the help. I was having trouble trying to find some
of
the
results. They all vary slightly and some are a lot different
than
what
i
want.

Ok the page you sent me to shows how to add "All" to my combo
box.
I
do
not
need to use the union function to display all of the data,
because
my
data
should already be there. The combo box is what is filtering out
most
of
the
data to only the needed results. Also how would you go about
setting
it
up
so that "All" is the default choice in the combo box. I tried
setting
the
default value to "All" but i was given an error.

Again, im sorry that this request has been asked many times, i
just
thought
that this varied enough from the others to be asked. Thank you
for
any
help
you can provide.

"BruceM" wrote:

If you mean to select an All button from the combo box, you
could
try
the
method he
http://support.microsoft.com/?id=210290
You could also just add a command button with the following in
its
Click
event:

Me.FilterOn = False

That code could go elsewhere, such as the combo box double-click
event,
if
you like. It would be more efficient for the user than having
to
select
something from the combo box, and much simpler for you to set
up.

This question has been asked and answered before. A Google
groups
search
should lead you to a variety of ideas.

"JKarchner" wrote in
message
...
I currently have a combo box set up to filter records in all of
my
subforms.
How would i go about using the combo box to show all records
at
the
same
time?

Sorry that this reply took so long, but i have been rather busy. Yes i do
have a many-to-many relationship. MCReports are types of reports that my
company creates. They are reports that deal with certain aspects of the car
industry. Each report deals with a different system in the car. Therefore
each company supplies many different parts for many different systems in the
car (i choose to call the table MCReports rather then Systems, because some
of the systems are combined in our reports). Hopefully this helped clear up
some of the confusion.

"BruceM" wrote:

You would have a linking table (also known as a junction table) in a
many-to-many relationship. In the case of Suppliers and Products, each
supplier can have many products and each product can be associated with many
suppliers. Similarly, each supplier can be associated with many MCReports,
and each MCReport can be associated with many suppliers. Is this the case?
I can only help if I understand the situation. To do that, I don't need to
know about combo boxes and filters and queries, but rather the real-life
situation. For instance, what is an MCReport?

"JKarchner" wrote in message
...
Well i would guess that a filter isnt exactly used the way you think that
it
might. It's weird, because the filter button on the toolbar is disabled.
So
when a MCReport is selected, the filter remains disabled.

For the tables there is a Suppliers table, which has the Supplier_ID and
the
Supplier (name of the supplier). The Products and MCReport are set up in
the
same fashion. Then there is a linking table. In this table there are 3
foreign keys: Supplier_ID, Product_ID and MCReport_ID. These are the only
fields in the table. The other tables are set up in this fashion also, so
the structure is uniform throughout the Database.

"BruceM" wrote:

Have you tried removing the filter using *exactly* what I have suggested?

What are the relationships between the tables?

"JKarchner" wrote in message
...
I am sorry that i have confused you even more. Let's see if i can
explain
this better. I should not have used the term report to describe what
it
is I
am trying to do, because this is a DB term. I should say that the
report
i
was refering to is a MCReport (multi-client report). The MCReport is
one
of
the main ways all the data is related. Each company appears in at
least
one
MCReport. And in each MCReport, the company supplies different
Products,
has
different Customers, Affiliates, Strategies, and Processes. Products,
Customers, Affiliates, Strategies, Processes, Companies, and MCReports
are
all tables. Hopefully this is starting to clear things up. The
subform
for
Products used to display all products that the company supplied for all
MCReports. Now that i have added the combo box, only products that the
company supplied for the chosen MCReport are displayed. Like i said i
linked
the subforms to the main form using the Company_ID and the MCReport_ID.
The
MCReport_ID is not field that is associated with the query that returns
all
of the company names that is tied to the main form. Instead it is
solely
used on the combo box, and this is where i believe i am having my
problem.
Now there is code linked to After Update on the combo box, however it
calls a
requery only for the subform associated with the Products subform.
Because
all of the other subforms change according to the MCReport selected in
the
combo box, i do not think that the code in the After Update is
necessary
let
alone working. There is no code in the filter section. This question
has
not deviated from my original question, rather it is much much more
complex
than originally thought. My question is still: how do i go about
displaying
all records for that company in each subform using the combo box? (i.e.
all
products supplied by said company regardless of MCReport, all process
used
by
company regardless of MCReport, and so on and so forth.)

For now let's only concentrate on this matter and if we ever come to a
solution i can ask about my other question.

I hope that this clarifies things for you and again i thank you for any
help
you can provide. I will try to look through the communities again to
find
any previous topics about this to see if i can come across any
information
that will be helpful in completing this.

"BruceM" wrote:

I'm still confused. What do you mean by the following:
"Before I added the combo box ... to filter the data, each subform
displayed
the data across all of the report types." If the subforms contain
related
records, what are you filtering? And what do you mean by "report
types"?

"I added an unbound combo box (choices are the names of the reports)
and
set
up links that link the subforms to the combo box in order to filter
the
data
by report." Filtering data by report means nothing to me. You can
filter
by form, and by selection, and maybe something else I've forgotten,
but
not
by report. Where did the reports come from anyhow? How are they
connected
to the subform?

"I did not inlude any events tied to After Update for any of the
subforms."
When using a combo box for filtering you would typically use the combo
box
After Update event to apply the filter. Does the combo box have an
After
Update event? If so, what is it? If not, how is the filter being
applied?
What is the filter?

To open the form with the filter off, place the line of code I have
mentioned several times into the form's Open event.

Have you tried the command button I mentioned? It would remove the
filter.
The methods in the links I provided would also accomplish that.

You will need to describe in non-database terms just what you want to
accomplish. You will also need to provide your table structure and
relationships. What you are asking now is far, far from your original
question.

"JKarchner" wrote in message
...
I'm sorry if I am confusing you about this matter i will try to
explain
it
better. The main form is a single form that displays one company at
a
time.
For each company there this 5 subforms, that each hold information
regarding
customers, products, processes, strategies, and affiliates. Before
i
added
the combo box (the combo box is on the main form and not on the
subforms)
to
filter the data, each subform displayed the data across all of the
report
types. I added an unbound combo box (choices are the names of the
reports)
and set up links that link the subforms to the combo box in order to
filter
the data by report. When i did this i lost the option to display
all
of
the
data in subforms, and that is when i asked for your help in order to
display
all of the data. I did not inlude any events tied to After Update
for
any
of
the subforms. It appears that the link fields are solely what are
filtering
the data in the subforms. I do not know if this was the correct way
to
go
about doing this or not. Of all the suggestions i found for
filtering
the
data, this method seemed to be the only one that worked. If you
have a
better method to go about doing this i would appreciate it.

By default, i mean that when i first open up the form i want all
data
to
appear, i do not want it to be filtered at all. I would like the
option
to
be able to "go back" and view all data again after a certain report
is
selected in the combo box.

Let me know if you need any more explanation than this, as i believe
that
i
can answer any questions you might have. Again thank you very much
for
helping me do this. It is greatly appreciated.

"BruceM" wrote:

The union query is to add (All) to the recordset. Without the
union
query
you would probably need to create a dummy record. By the way,
there
are
two
choices at the link. One is to use a user-defined function, the
other
is
to
use a union query. There is no union function as such.

What do you mean "default choice in the combo box"? Do you want
(All)
to
appear any time you move to another record? What exactly do you
want
to
accomplish?

If you are filtering the data, do you want to remove the filter, or
do
you
want to go from one filtered recordset to another? I assume the
point
of
(All) is to remove any filters. If so, the link provides two
options
for
doing so by way of a combo box. An "All Records" command button as
I
described is another way.

"JKarchner" wrote in message
...
Thank you for the help. I was having trouble trying to find some
of
the
results. They all vary slightly and some are a lot different
than
what
i
want.

Ok the page you sent me to shows how to add "All" to my combo
box.
I
do
not
need to use the union function to display all of the data,
because
my
data
should already be there. The combo box is what is filtering out
most
of
the
data to only the needed results. Also how would you go about
setting
it
up
so that "All" is the default choice in the combo box. I tried
setting
the
default value to "All" but i was given an error.

Again, im sorry that this request has been asked many times, i
just
thought
that this varied enough from the others to be asked. Thank you
for
any
help
you can provide.

"BruceM" wrote:

If you mean to select an All button from the combo box, you
could
try
the
method he
http://support.microsoft.com/?id=210290
You could also just add a command button with the following in
its
Click
event:

Let me see if I understand so far. Each system consists of parts that are
provided by an assortment of suppliers (companies). Is there more than one
company for a particular part in a given system? Let's ignore for the
moment that several systems may be combined on one report. You said that
the main form is based on the Company table. How do you intend to associate
a system with a company?
Does this describe the relationship between companies and systems? Each
system has many parts, and each company supplies parts for many systems (it
doesn't matter if some companies supply only a single part for a single
system).
Each company supplies a variety of parts, so you could have a Company table
linked to a Parts table. For each company you would have a listing of
parts. Company and Parts are related one-to-many, so you have a Company
form and a Parts subform. I would suggest that a reasonable approach may be
to have a separate main form based on a Systems table, with a subform based
on SystemDetails table. Assuming that some parts are used in several
systems, SystemDetails could be a junction table between Systems and Parts,
with fields linked to the PKs from each table. *The assumption that some
parts are used in several systems is an important point.* If this is not
the case, the design will be somewhat different. In any case, the
SystemDetails subform would have a combo box based on the Parts table. By
selecting the part you are also selecting the company with which it is
associated. A system comprises parts, not companies. If the company is
associated with the part you can bring it all together as needed.
If this basic model sounds like it would work, the report can be devised.
You are starting by thinking about the final report rather than the database
design. If the structure is sound, you can produce any report you need.
If what I have described is not how things are, please provide an example of
a system or two, and how companies, parts, and systems are associated. I
don't need to know every part in a system. If you are describing an AC
system it is enough to list the compressor and a hose or something like
that. Then, explain how you want that system to show up on a report.
Describe two systems if it makes your point clearer.

.."JKarchner" wrote in message
...
Sorry that this reply took so long, but i have been rather busy. Yes i do
have a many-to-many relationship. MCReports are types of reports that my
company creates. They are reports that deal with certain aspects of the
car
industry. Each report deals with a different system in the car.
Therefore
each company supplies many different parts for many different systems in
the
car (i choose to call the table MCReports rather then Systems, because
some
of the systems are combined in our reports). Hopefully this helped clear
up
some of the confusion.

"BruceM" wrote:

You would have a linking table (also known as a junction table) in a
many-to-many relationship. In the case of Suppliers and Products, each
supplier can have many products and each product can be associated with
many
suppliers. Similarly, each supplier can be associated with many
MCReports,
and each MCReport can be associated with many suppliers. Is this the
case?
I can only help if I understand the situation. To do that, I don't need
to
know about combo boxes and filters and queries, but rather the real-life
situation. For instance, what is an MCReport?

"JKarchner" wrote in message
...
Well i would guess that a filter isnt exactly used the way you think
that
it
might. It's weird, because the filter button on the toolbar is
disabled.
So
when a MCReport is selected, the filter remains disabled.

For the tables there is a Suppliers table, which has the Supplier_ID
and
the
Supplier (name of the supplier). The Products and MCReport are set up
in
the
same fashion. Then there is a linking table. In this table there are
3
foreign keys: Supplier_ID, Product_ID and MCReport_ID. These are the
only
fields in the table. The other tables are set up in this fashion also,
so
the structure is uniform throughout the Database.

"BruceM" wrote:

Have you tried removing the filter using *exactly* what I have
suggested?

What are the relationships between the tables?

"JKarchner" wrote in message
...
I am sorry that i have confused you even more. Let's see if i can
explain
this better. I should not have used the term report to describe
what
it
is I
am trying to do, because this is a DB term. I should say that the
report
i
was refering to is a MCReport (multi-client report). The MCReport
is
one
of
the main ways all the data is related. Each company appears in at
least
one
MCReport. And in each MCReport, the company supplies different
Products,
has
different Customers, Affiliates, Strategies, and Processes.
Products,
Customers, Affiliates, Strategies, Processes, Companies, and
MCReports
are
all tables. Hopefully this is starting to clear things up. The
subform
for
Products used to display all products that the company supplied for
all
MCReports. Now that i have added the combo box, only products that
the
company supplied for the chosen MCReport are displayed. Like i said
i
linked
the subforms to the main form using the Company_ID and the
MCReport_ID.
The
MCReport_ID is not field that is associated with the query that
returns
all
of the company names that is tied to the main form. Instead it is
solely
used on the combo box, and this is where i believe i am having my
problem.
Now there is code linked to After Update on the combo box, however
it
calls a
requery only for the subform associated with the Products subform.
Because
all of the other subforms change according to the MCReport selected
in
the
combo box, i do not think that the code in the After Update is
necessary
let
alone working. There is no code in the filter section. This
question
has
not deviated from my original question, rather it is much much more
complex
than originally thought. My question is still: how do i go about
displaying
all records for that company in each subform using the combo box?
(i.e.
all
products supplied by said company regardless of MCReport, all
process
used
by
company regardless of MCReport, and so on and so forth.)

For now let's only concentrate on this matter and if we ever come to
a
solution i can ask about my other question.

I hope that this clarifies things for you and again i thank you for
any
help
you can provide. I will try to look through the communities again
to
find
any previous topics about this to see if i can come across any
information
that will be helpful in completing this.

"BruceM" wrote:

I'm still confused. What do you mean by the following:
"Before I added the combo box ... to filter the data, each subform
displayed
the data across all of the report types." If the subforms contain
related
records, what are you filtering? And what do you mean by "report
types"?

"I added an unbound combo box (choices are the names of the
reports)
and
set
up links that link the subforms to the combo box in order to filter
the
data
by report." Filtering data by report means nothing to me. You can
filter
by form, and by selection, and maybe something else I've forgotten,
but
not
by report. Where did the reports come from anyhow? How are they
connected
to the subform?

"I did not inlude any events tied to After Update for any of the
subforms."
When using a combo box for filtering you would typically use the
combo
box
After Update event to apply the filter. Does the combo box have an
After
Update event? If so, what is it? If not, how is the filter being
applied?
What is the filter?

To open the form with the filter off, place the line of code I have
mentioned several times into the form's Open event.

Have you tried the command button I mentioned? It would remove the
filter.
The methods in the links I provided would also accomplish that.

You will need to describe in non-database terms just what you want
to
accomplish. You will also need to provide your table structure and
relationships. What you are asking now is far, far from your
original
question.

"JKarchner" wrote in message
...
I'm sorry if I am confusing you about this matter i will try to
explain
it
better. The main form is a single form that displays one company
at
a
time.
For each company there this 5 subforms, that each hold
information
regarding
customers, products, processes, strategies, and affiliates.
Before
i
added
the combo box (the combo box is on the main form and not on the
subforms)
to
filter the data, each subform displayed the data across all of
the
report
types. I added an unbound combo box (choices are the names of
the
reports)
and set up links that link the subforms to the combo box in order
to
filter
the data by report. When i did this i lost the option to display
all
of
the
data in subforms, and that is when i asked for your help in order
to
display
all of the data. I did not inlude any events tied to After
Update
for
any
of
the subforms. It appears that the link fields are solely what
are
filtering
the data in the subforms. I do not know if this was the correct
way
to
go
about doing this or not. Of all the suggestions i found for
filtering
the
data, this method seemed to be the only one that worked. If you
have a
better method to go about doing this i would appreciate it.

By default, i mean that when i first open up the form i want all
data
to
appear, i do not want it to be filtered at all. I would like the
option
to
be able to "go back" and view all data again after a certain
report
is
selected in the combo box.

Let me know if you need any more explanation than this, as i
believe
that
i
can answer any questions you might have. Again thank you very
much
for
helping me do this. It is greatly appreciated.

"BruceM" wrote:

The union query is to add (All) to the recordset. Without the
union
query
you would probably need to create a dummy record. By the way,
there
are
two
choices at the link. One is to use a user-defined function, the
other
is
to
use a union query. There is no union function as such.

What do you mean "default choice in the combo box"? Do you want
(All)
to
appear any time you move to another record? What exactly do you
want
to
accomplish?

If you are filtering the data, do you want to remove the filter,
or
do
you
want to go from one filtered recordset to another? I assume the
point
of
(All) is to remove any filters. If so, the link provides two
options
for
doing so by way of a combo box. An "All Records" command button
as
I
described is another way.

"JKarchner" wrote in
message
...
Thank you for the help. I was having trouble trying to find
some
of
the
results. They all vary slightly and some are a lot different
than
what
i
want.

Ok the page you sent me to shows how to add "All" to my combo
box.
I
do
not
need to use the union function to display all of the data,
because
my
data
should already be there. The combo box is what is filtering
out
most
of
the
data to only the needed results. Also how would you go about
setting
it
up
so that "All" is the default choice in the combo box. I tried
setting
the
default value to "All" but i was given an error.

Again, im sorry that this request has been asked many times, i
just
thought
that this varied enough from the others to be asked. Thank
you
for
any
help
you can provide.

"BruceM" wrote:

If you mean to select an All button from the combo box, you
could
try
the
method he
http://support.microsoft.com/?id=210290
You could also just add a command button with the following
in
its
Click
event:

There is more than one company for a particular part in a given system. The
reason i mentioned that several systems may be combined in one report
(MCReport), was to get the idea of creating a new form on systems. I would
like to keep the forms based on the company because it is much easier to look
at one company and the parts they supply rather than at one system and the
parts the company supplies. i understand where your thinking comes in, a sub
form that would have a field for the company and a field for the parts.

"You said that the main form is based on the Company table. How do you
intend to associate a system with a company?"

The companies are associate do a system through a linking table. The
linking table has a company field, a system field, and a part field.
Therefore each part is linked to a system and a company.

"Each company supplies a variety of parts, so you could have a Company table
linked to a Parts table. For each company you would have a listing of parts.
Company and Parts are related one-to-many, so you have a Company form and a
Parts subform. "

This is currently what i have.

"If what I have described is not how things are, please provide an example
of a system or two, and how companies, parts, and systems are associated. I
don't need to know every part in a system. If you are describing an AC
system it is enough to list the compressor and a hose or something like that.
Then, explain how you want that system to show up on a report. Describe two
systems if it makes your point clearer"

Like i had said i just think that it will be easier to look at a single
company at a time with a subform that shows all the parts that company
supplies. Then i would like to option to filter those parts depending on the
system, through a combo box on the main form. I would like to have it on the
main form, because there are several other subforms that i would also like to
have filtered by the same control, but did not mention them in order to keep
the problem as simple as problem.

For a small description say you have Companies A, B, C. Now there is a
system for fuel which has rubber tubing, plastic tubing, and tanks. Also
there is a system for air intake which has parts rubber tubing, plastic
tubing, and cooling modules.

So my table will look like this (it is a linking table, so all three are IDs
and lookup the values):

Company System Part
------------------------------------------------------------
A Fuel Rubber tubing
A Air intake rubber tubing
A Air Intake cooling modules
B Fuel plastic tubing
C Air intake rubber tubing

hopefully this has clarified how things work in my database.
"BruceM" wrote:

Let me see if I understand so far. Each system consists of parts that are
provided by an assortment of suppliers (companies). Is there more than one
company for a particular part in a given system? Let's ignore for the
moment that several systems may be combined on one report. You said that
the main form is based on the Company table. How do you intend to associate
a system with a company?
Does this describe the relationship between companies and systems? Each
system has many parts, and each company supplies parts for many systems (it
doesn't matter if some companies supply only a single part for a single
system).
Each company supplies a variety of parts, so you could have a Company table
linked to a Parts table. For each company you would have a listing of
parts. Company and Parts are related one-to-many, so you have a Company
form and a Parts subform. I would suggest that a reasonable approach may be
to have a separate main form based on a Systems table, with a subform based
on SystemDetails table. Assuming that some parts are used in several
systems, SystemDetails could be a junction table between Systems and Parts,
with fields linked to the PKs from each table. *The assumption that some
parts are used in several systems is an important point.* If this is not
the case, the design will be somewhat different. In any case, the
SystemDetails subform would have a combo box based on the Parts table. By
selecting the part you are also selecting the company with which it is
associated. A system comprises parts, not companies. If the company is
associated with the part you can bring it all together as needed.
If this basic model sounds like it would work, the report can be devised.
You are starting by thinking about the final report rather than the database
design. If the structure is sound, you can produce any report you need.
If what I have described is not how things are, please provide an example of
a system or two, and how companies, parts, and systems are associated. I
don't need to know every part in a system. If you are describing an AC
system it is enough to list the compressor and a hose or something like
that. Then, explain how you want that system to show up on a report.
Describe two systems if it makes your point clearer.

.."JKarchner" wrote in message
...
Sorry that this reply took so long, but i have been rather busy. Yes i do
have a many-to-many relationship. MCReports are types of reports that my
company creates. They are reports that deal with certain aspects of the
car
industry. Each report deals with a different system in the car.
Therefore
each company supplies many different parts for many different systems in
the
car (i choose to call the table MCReports rather then Systems, because
some
of the systems are combined in our reports). Hopefully this helped clear
up
some of the confusion.

"BruceM" wrote:

You would have a linking table (also known as a junction table) in a
many-to-many relationship. In the case of Suppliers and Products, each
supplier can have many products and each product can be associated with
many
suppliers. Similarly, each supplier can be associated with many
MCReports,
and each MCReport can be associated with many suppliers. Is this the
case?
I can only help if I understand the situation. To do that, I don't need
to
know about combo boxes and filters and queries, but rather the real-life
situation. For instance, what is an MCReport?

"JKarchner" wrote in message
...
Well i would guess that a filter isnt exactly used the way you think
that
it
might. It's weird, because the filter button on the toolbar is
disabled.
So
when a MCReport is selected, the filter remains disabled.

For the tables there is a Suppliers table, which has the Supplier_ID
and
the
Supplier (name of the supplier). The Products and MCReport are set up
in
the
same fashion. Then there is a linking table. In this table there are
3
foreign keys: Supplier_ID, Product_ID and MCReport_ID. These are the
only
fields in the table. The other tables are set up in this fashion also,
so
the structure is uniform throughout the Database.

"BruceM" wrote:

Have you tried removing the filter using *exactly* what I have
suggested?

What are the relationships between the tables?

"JKarchner" wrote in message
...
I am sorry that i have confused you even more. Let's see if i can
explain
this better. I should not have used the term report to describe
what
it
is I
am trying to do, because this is a DB term. I should say that the
report
i
was refering to is a MCReport (multi-client report). The MCReport
is
one
of
the main ways all the data is related. Each company appears in at
least
one
MCReport. And in each MCReport, the company supplies different
Products,
has
different Customers, Affiliates, Strategies, and Processes.
Products,
Customers, Affiliates, Strategies, Processes, Companies, and
MCReports
are
all tables. Hopefully this is starting to clear things up. The
subform
for
Products used to display all products that the company supplied for
all
MCReports. Now that i have added the combo box, only products that
the
company supplied for the chosen MCReport are displayed. Like i said
i
linked
the subforms to the main form using the Company_ID and the
MCReport_ID.
The
MCReport_ID is not field that is associated with the query that
returns
all
of the company names that is tied to the main form. Instead it is
solely
used on the combo box, and this is where i believe i am having my
problem.
Now there is code linked to After Update on the combo box, however
it
calls a
requery only for the subform associated with the Products subform.
Because
all of the other subforms change according to the MCReport selected
in
the
combo box, i do not think that the code in the After Update is
necessary
let
alone working. There is no code in the filter section. This
question
has
not deviated from my original question, rather it is much much more
complex
than originally thought. My question is still: how do i go about
displaying
all records for that company in each subform using the combo box?
(i.e.
all
products supplied by said company regardless of MCReport, all
process
used
by
company regardless of MCReport, and so on and so forth.)

For now let's only concentrate on this matter and if we ever come to
a
solution i can ask about my other question.

I hope that this clarifies things for you and again i thank you for
any
help
you can provide. I will try to look through the communities again
to
find
any previous topics about this to see if i can come across any
information
that will be helpful in completing this.

"BruceM" wrote:

I'm still confused. What do you mean by the following:
"Before I added the combo box ... to filter the data, each subform
displayed
the data across all of the report types." If the subforms contain
related
records, what are you filtering? And what do you mean by "report
types"?

"I added an unbound combo box (choices are the names of the
reports)
and
set
up links that link the subforms to the combo box in order to filter
the
data
by report." Filtering data by report means nothing to me. You can
filter
by form, and by selection, and maybe something else I've forgotten,
but
not
by report. Where did the reports come from anyhow? How are they
connected
to the subform?

"I did not inlude any events tied to After Update for any of the
subforms."
When using a combo box for filtering you would typically use the
combo
box
After Update event to apply the filter. Does the combo box have an
After
Update event? If so, what is it? If not, how is the filter being
applied?
What is the filter?

To open the form with the filter off, place the line of code I have
mentioned several times into the form's Open event.

Have you tried the command button I mentioned? It would remove the
filter.
The methods in the links I provided would also accomplish that.

You will need to describe in non-database terms just what you want
to
accomplish. You will also need to provide your table structure and
relationships. What you are asking now is far, far from your
original
question.

"JKarchner" wrote in message
...
I'm sorry if I am confusing you about this matter i will try to
explain
it
better. The main form is a single form that displays one company
at
a
time.
For each company there this 5 subforms, that each hold
information
regarding
customers, products, processes, strategies, and affiliates.
Before
i
added
the combo box (the combo box is on the main form and not on the
subforms)
to
filter the data, each subform displayed the data across all of
the
report
types. I added an unbound combo box (choices are the names of
the
reports)
and set up links that link the subforms to the combo box in order
to
filter
the data by report. When i did this i lost the option to display
all
of
the
data in subforms, and that is when i asked for your help in order
to
display
all of the data. I did not inlude any events tied to After
Update
for
any
of
the subforms. It appears that the link fields are solely what
are
filtering
the data in the subforms. I do not know if this was the correct
way
to
go
about doing this or not. Of all the suggestions i found for
filtering
the
data, this method seemed to be the only one that worked. If you
have a
better method to go about doing this i would appreciate it.

By default, i mean that when i first open up the form i want all
data
to
appear, i do not want it to be filtered at all. I would like the
option
to
be able to "go back" and view all data again after a certain
report
is
selected in the combo box.

Let me know if you need any more explanation than this, as i

Your original question was "I currently have a combo box set up to filter
records in all of my subforms. How would i go about using the combo box to
show all records at the same time?" Simple enough, so I offered an answer.
My level of knowledge about Access may not be up to what is needed for your
somewhat complex situation. I will add what I can, but it may be best to
start a new thread if my contributions continue to leave unanswered
questions.
One inmportant point here is that it is of great help if you describe what
you need to do. It is good to describe what you have tried, but those with
experience who see these posts need to know what you hope to accomplish. In
your most recent post you have sketched it out fairly well.
I understand that you want to look at the company and see the parts they
supply, and that you want to be able to filter the parts for the system, but
rubber tubing (for instance) is used for a variety of systems.
You say there are companies, parts, and systems. Each company supplies many
parts, each part may be used in many systems, and each system uses many
parts. If you want to list the parts a company supplies you need a
one-to-many relationship between Company and Parts. If you make a form
based on company with a subform based on parts you can go to a company's
records and fill in all of the parts they supply. You can make a report
based on a query combining Company and parts. If you make it a right join
(the third choice in the Join Properties dialog box) and group by Part you
can see a listing of parts and all of the companies that supply that part.
Frankly, beyond that I don't know what to say. If it was my project I might
spend a while learning how to make it work, but I can't take that amount of
time on this. I can only stress that you need to describe what you intend
to accomplish from the beginning. I would *not* have answered the question
had I known about all of the other questions behind it. Sorry I couldn't be
more help.

"JKarchner" wrote in message
...
There is more than one company for a particular part in a given system.
The
reason i mentioned that several systems may be combined in one report
(MCReport), was to get the idea of creating a new form on systems. I
would
like to keep the forms based on the company because it is much easier to
look
at one company and the parts they supply rather than at one system and the
parts the company supplies. i understand where your thinking comes in, a
sub
form that would have a field for the company and a field for the parts.

"You said that the main form is based on the Company table. How do you
intend to associate a system with a company?"

The companies are associate do a system through a linking table. The
linking table has a company field, a system field, and a part field.
Therefore each part is linked to a system and a company.

"Each company supplies a variety of parts, so you could have a Company
table
linked to a Parts table. For each company you would have a listing of
parts.
Company and Parts are related one-to-many, so you have a Company form and
a
Parts subform. "

This is currently what i have.

"If what I have described is not how things are, please provide an example
of a system or two, and how companies, parts, and systems are associated.
I
don't need to know every part in a system. If you are describing an AC
system it is enough to list the compressor and a hose or something like
that.
Then, explain how you want that system to show up on a report. Describe
two
systems if it makes your point clearer"

Like i had said i just think that it will be easier to look at a single
company at a time with a subform that shows all the parts that company
supplies. Then i would like to option to filter those parts depending on
the
system, through a combo box on the main form. I would like to have it on
the
main form, because there are several other subforms that i would also like
to
have filtered by the same control, but did not mention them in order to
keep
the problem as simple as problem.

For a small description say you have Companies A, B, C. Now there is a
system for fuel which has rubber tubing, plastic tubing, and tanks. Also
there is a system for air intake which has parts rubber tubing, plastic
tubing, and cooling modules.

So my table will look like this (it is a linking table, so all three are
IDs
and lookup the values):

Company System Part
------------------------------------------------------------
A Fuel Rubber tubing
A Air intake rubber tubing
A Air Intake cooling modules
B Fuel plastic tubing
C Air intake rubber tubing

hopefully this has clarified how things work in my database.
"BruceM" wrote:

Let me see if I understand so far. Each system consists of parts that
are
provided by an assortment of suppliers (companies). Is there more than
one
company for a particular part in a given system? Let's ignore for the
moment that several systems may be combined on one report. You said that
the main form is based on the Company table. How do you intend to
associate
a system with a company?
Does this describe the relationship between companies and systems? Each
system has many parts, and each company supplies parts for many systems
(it
doesn't matter if some companies supply only a single part for a single
system).
Each company supplies a variety of parts, so you could have a Company
table
linked to a Parts table. For each company you would have a listing of
parts. Company and Parts are related one-to-many, so you have a Company
form and a Parts subform. I would suggest that a reasonable approach may
be
to have a separate main form based on a Systems table, with a subform
based
on SystemDetails table. Assuming that some parts are used in several
systems, SystemDetails could be a junction table between Systems and
Parts,
with fields linked to the PKs from each table. *The assumption that some
parts are used in several systems is an important point.* If this is not
the case, the design will be somewhat different. In any case, the
SystemDetails subform would have a combo box based on the Parts table.
By
selecting the part you are also selecting the company with which it is
associated. A system comprises parts, not companies. If the company is
associated with the part you can bring it all together as needed.
If this basic model sounds like it would work, the report can be devised.
You are starting by thinking about the final report rather than the
database
design. If the structure is sound, you can produce any report you need.
If what I have described is not how things are, please provide an example
of
a system or two, and how companies, parts, and systems are associated. I
don't need to know every part in a system. If you are describing an AC
system it is enough to list the compressor and a hose or something like
that. Then, explain how you want that system to show up on a report.
Describe two systems if it makes your point clearer.

.."JKarchner" wrote in message
...
Sorry that this reply took so long, but i have been rather busy. Yes i
do
have a many-to-many relationship. MCReports are types of reports that
my
company creates. They are reports that deal with certain aspects of
the
car
industry. Each report deals with a different system in the car.
Therefore
each company supplies many different parts for many different systems
in
the
car (i choose to call the table MCReports rather then Systems, because
some
of the systems are combined in our reports). Hopefully this helped
clear
up
some of the confusion.

"BruceM" wrote:

You would have a linking table (also known as a junction table) in a
many-to-many relationship. In the case of Suppliers and Products,
each
supplier can have many products and each product can be associated
with
many
suppliers. Similarly, each supplier can be associated with many
MCReports,
and each MCReport can be associated with many suppliers. Is this the
case?
I can only help if I understand the situation. To do that, I don't
need
to
know about combo boxes and filters and queries, but rather the
real-life
situation. For instance, what is an MCReport?

"JKarchner" wrote in message
...
Well i would guess that a filter isnt exactly used the way you think
that
it
might. It's weird, because the filter button on the toolbar is
disabled.
So
when a MCReport is selected, the filter remains disabled.

For the tables there is a Suppliers table, which has the Supplier_ID
and
the
Supplier (name of the supplier). The Products and MCReport are set
up
in
the
same fashion. Then there is a linking table. In this table there
are
3
foreign keys: Supplier_ID, Product_ID and MCReport_ID. These are
the
only
fields in the table. The other tables are set up in this fashion
also,
so
the structure is uniform throughout the Database.

"BruceM" wrote:

Have you tried removing the filter using *exactly* what I have
suggested?

What are the relationships between the tables?

"JKarchner" wrote in message
...
I am sorry that i have confused you even more. Let's see if i can
explain
this better. I should not have used the term report to describe
what
it
is I
am trying to do, because this is a DB term. I should say that
the
report
i
was refering to is a MCReport (multi-client report). The
MCReport
is
one
of
the main ways all the data is related. Each company appears in
at
least
one
MCReport. And in each MCReport, the company supplies different
Products,
has
different Customers, Affiliates, Strategies, and Processes.
Products,
Customers, Affiliates, Strategies, Processes, Companies, and
MCReports
are
all tables. Hopefully this is starting to clear things up. The
subform
for
Products used to display all products that the company supplied
for
all
MCReports. Now that i have added the combo box, only products
that
the
company supplied for the chosen MCReport are displayed. Like i
said
i
linked
the subforms to the main form using the Company_ID and the
MCReport_ID.
The
MCReport_ID is not field that is associated with the query that
returns
all
of the company names that is tied to the main form. Instead it
is
solely
used on the combo box, and this is where i believe i am having my
problem.
Now there is code linked to After Update on the combo box,
however
it
calls a
requery only for the subform associated with the Products
subform.
Because
all of the other subforms change according to the MCReport
selected
in
the
combo box, i do not think that the code in the After Update is
necessary
let
alone working. There is no code in the filter section. This
question
has
not deviated from my original question, rather it is much much
more
complex
than originally thought. My question is still: how do i go about
displaying
all records for that company in each subform using the combo box?
(i.e.
all
products supplied by said company regardless of MCReport, all
process
used
by
company regardless of MCReport, and so on and so forth.)

For now let's only concentrate on this matter and if we ever come
to
a
solution i can ask about my other question.

I hope that this clarifies things for you and again i thank you
for
any
help
you can provide. I will try to look through the communities
again
to
find
any previous topics about this to see if i can come across any
information
that will be helpful in completing this.

"BruceM" wrote:

I'm still confused. What do you mean by the following:
"Before I added the combo box ... to filter the data, each
subform
displayed
the data across all of the report types." If the subforms
contain
related
records, what are you filtering? And what do you mean by
"report
types"?

"I added an unbound combo box (choices are the names of the
reports)
and
set
up links that link the subforms to the combo box in order to
filter
the
data
by report." Filtering data by report means nothing to me. You
can
filter
by form, and by selection, and maybe something else I've
forgotten,
but
not
by report. Where did the reports come from anyhow? How are
they
connected
to the subform?

"I did not inlude any events tied to After Update for any of the
subforms."
When using a combo box for filtering you would typically use the
combo
box
After Update event to apply the filter. Does the combo box have
an
After
Update event? If so, what is it? If not, how is the filter
being
applied?
What is the filter?

To open the form with the filter off, place the line of code I
have
mentioned several times into the form's Open event.

Have you tried the command button I mentioned? It would remove
the
filter.
The methods in the links I provided would also accomplish that.

You will need to describe in non-database terms just what you
want
to
accomplish. You will also need to provide your table structure
and
relationships. What you are asking now is far, far from your
original
question.

"JKarchner" wrote in
message
...
I'm sorry if I am confusing you about this matter i will try
to
explain
it
better. The main form is a single form that displays one
company
at
a
time.
For each company there this 5 subforms, that each hold
information
regarding
customers, products, processes, strategies, and affiliates.
Before
i
added
the combo box (the combo box is on the main form and not on
the
subforms)
to
filter the data, each subform displayed the data across all of
the
report
types. I added an unbound combo box (choices are the names of
the
reports)
and set up links that link the subforms to the combo box in
order
to
filter
the data by report. When i did this i lost the option to
display
all
of
the
data in subforms, and that is when i asked for your help in
order
to
display
all of the data. I did not inlude any events tied to After
Update
for
any
of
the subforms. It appears that the link fields are solely what
are
filtering
the data in the subforms. I do not know if this was the
correct
way
to
go
about doing this or not. Of all the suggestions i found for
filtering
the
data, this method seemed to be the only one that worked. If
you
have a
better method to go about doing this i would appreciate it.

By default, i mean that when i first open up the form i want
all
data
to
appear, i do not want it to be filtered at all. I would like
the
option
to
be able to "go back" and view all data again after a certain
report
is
selected in the combo box.

Let me know if you need any more explanation than this, as i