If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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? |
#12
|
|||
|
|||
Show all records through combo box
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? |
#13
|
|||
|
|||
Show all records through combo box
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: |
#14
|
|||
|
|||
Show all records through combo box
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: |
#15
|
|||
|
|||
Show all records through combo box
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 |
#16
|
|||
|
|||
Show all records through combo box
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 |
|
Thread Tools | |
Display Modes | |
|
|