Show all records through combo box

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

Guest

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?
 
If you mean to select an All button from the combo box, you could try the
method here:
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.
 
In the first line of the previous post I meant to say:
"If you mean to have an All choice in the combo box..." rather than "If you
mean to select an All button from the combo box"
 
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.
 
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.
 
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.
 
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.
 
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.
 
Have you tried removing the filter using *exactly* what I have suggested?

What are the relationships between the tables?
 
I should mention that I am leaving for the day soon, and will not be back at
my desk until Tuesday. If nobody picks up the thread you may need to start
a new one. If you do, explain the structure of your database, including
table relationships.
 
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.
 
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?
 
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.
 
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.
 
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.
 
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.
 
Back
Top