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 |
#1
|
|||
|
|||
How do I access a second table from a form?
Hi,
I have two tables, one that stores company information, and a second that stores the company products. I set up a search form that gets data from tblCompanyInformation (I use combo boxes and a command button to filter the form), but I want to add another combo box that gets data from tblProducts to be able to filter by which companies have certain products. Is there a way to do this? Thanks |
#2
|
|||
|
|||
How do I access a second table from a form?
Daniel
One approach might be to use "cascading comboboxes" (you can search on-line and Access HELP for more on this topic). The general idea is that the first combobox lets you pick one of something (say, a company). The second combobox is refreshed to list only those items (say, Products) that are associated with the first combobox's selection. Is that what you're after? -- Regards Jeff Boyce Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ Microsoft Registered Partner https://partner.microsoft.com/ "Daniel" wrote in message ... Hi, I have two tables, one that stores company information, and a second that stores the company products. I set up a search form that gets data from tblCompanyInformation (I use combo boxes and a command button to filter the form), but I want to add another combo box that gets data from tblProducts to be able to filter by which companies have certain products. Is there a way to do this? Thanks |
#3
|
|||
|
|||
How do I access a second table from a form?
Well, heres the background: I set up a table, and then a form associated
with that table, to store company information (name, address, phone numbers, etc...). Later, I decided I wanted to store which companies made certain products, so I set up a second table that was related to the first that stored only product information. Now I have another form that is a search/filter form. It works nicely when all you want to do is filter by company information, but I am trying to figure out how to also filter by product information. My question comes from that fact that product information and company information are in two seperate forms. Is there a way to filter two related forms? Here are two examples of what I want to do: 1) Show all companies in my database who have Product X. 2) Show all companies in the USA who have Product Z. Hope this makes sense. "Jeff Boyce" wrote: Daniel One approach might be to use "cascading comboboxes" (you can search on-line and Access HELP for more on this topic). The general idea is that the first combobox lets you pick one of something (say, a company). The second combobox is refreshed to list only those items (say, Products) that are associated with the first combobox's selection. Is that what you're after? -- Regards Jeff Boyce Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ Microsoft Registered Partner https://partner.microsoft.com/ "Daniel" wrote in message ... Hi, I have two tables, one that stores company information, and a second that stores the company products. I set up a search form that gets data from tblCompanyInformation (I use combo boxes and a command button to filter the form), but I want to add another combo box that gets data from tblProducts to be able to filter by which companies have certain products. Is there a way to do this? Thanks |
#4
|
|||
|
|||
How do I access a second table from a form?
Daniel
If I wanted to show all Products for a Company, and all Companies for a Product, and if I had to maintain those lists, I would use (two) main form/subform setups. The main form would allow me to select, say, a Company, and the subform would list all (related) Products (and vice versa for the second setup). If I only wished to see the data (not edit it), I'd use (two) forms, with a selection of Company and a listbox with related Products (and vice versa). -- Regards Jeff Boyce Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ Microsoft Registered Partner https://partner.microsoft.com/ "Daniel" wrote in message ... Well, heres the background: I set up a table, and then a form associated with that table, to store company information (name, address, phone numbers, etc...). Later, I decided I wanted to store which companies made certain products, so I set up a second table that was related to the first that stored only product information. Now I have another form that is a search/filter form. It works nicely when all you want to do is filter by company information, but I am trying to figure out how to also filter by product information. My question comes from that fact that product information and company information are in two seperate forms. Is there a way to filter two related forms? Here are two examples of what I want to do: 1) Show all companies in my database who have Product X. 2) Show all companies in the USA who have Product Z. Hope this makes sense. "Jeff Boyce" wrote: Daniel One approach might be to use "cascading comboboxes" (you can search on-line and Access HELP for more on this topic). The general idea is that the first combobox lets you pick one of something (say, a company). The second combobox is refreshed to list only those items (say, Products) that are associated with the first combobox's selection. Is that what you're after? -- Regards Jeff Boyce Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ Microsoft Registered Partner https://partner.microsoft.com/ "Daniel" wrote in message ... Hi, I have two tables, one that stores company information, and a second that stores the company products. I set up a search form that gets data from tblCompanyInformation (I use combo boxes and a command button to filter the form), but I want to add another combo box that gets data from tblProducts to be able to filter by which companies have certain products. Is there a way to do this? Thanks |
#5
|
|||
|
|||
How do I access a second table from a form?
OK, so I have the first part nicely setup (Select a company and view all of
their products in the subform), but I'm having a problem with the reverse setup. My products table has 28 different products plus the company ID (the foreign key). I only want one combo box that lists all 28 products, so the user can select one product and see which companies have that product. I created a combo box and typed in all 28 products, but I'm not sure how to take the value selected and display the associated companies. Thanks "Jeff Boyce" wrote: Daniel If I wanted to show all Products for a Company, and all Companies for a Product, and if I had to maintain those lists, I would use (two) main form/subform setups. The main form would allow me to select, say, a Company, and the subform would list all (related) Products (and vice versa for the second setup). If I only wished to see the data (not edit it), I'd use (two) forms, with a selection of Company and a listbox with related Products (and vice versa). -- Regards Jeff Boyce Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ Microsoft Registered Partner https://partner.microsoft.com/ "Daniel" wrote in message ... Well, heres the background: I set up a table, and then a form associated with that table, to store company information (name, address, phone numbers, etc...). Later, I decided I wanted to store which companies made certain products, so I set up a second table that was related to the first that stored only product information. Now I have another form that is a search/filter form. It works nicely when all you want to do is filter by company information, but I am trying to figure out how to also filter by product information. My question comes from that fact that product information and company information are in two seperate forms. Is there a way to filter two related forms? Here are two examples of what I want to do: 1) Show all companies in my database who have Product X. 2) Show all companies in the USA who have Product Z. Hope this makes sense. "Jeff Boyce" wrote: Daniel One approach might be to use "cascading comboboxes" (you can search on-line and Access HELP for more on this topic). The general idea is that the first combobox lets you pick one of something (say, a company). The second combobox is refreshed to list only those items (say, Products) that are associated with the first combobox's selection. Is that what you're after? -- Regards Jeff Boyce Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ Microsoft Registered Partner https://partner.microsoft.com/ "Daniel" wrote in message ... Hi, I have two tables, one that stores company information, and a second that stores the company products. I set up a search form that gets data from tblCompanyInformation (I use combo boxes and a command button to filter the form), but I want to add another combo box that gets data from tblProducts to be able to filter by which companies have certain products. Is there a way to do this? Thanks |
#6
|
|||
|
|||
How do I access a second table from a form?
Daniel
"... and typed in all 28 products..."?! I misunderstood. I thought you had a table that lists the products. Why would you type them in when you could use a query to get them for the combo box? If you already have Company (view Products) set up, couldn't you just reverse the process that you used? Do you have another source of data for the Company (view Products) than you do for the Products (view Companies)? Let me make sure of something... since you have Companies, and Products, and valid Company X Product combinations, you have three tables, right? -- Regards Jeff Boyce Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ Microsoft Registered Partner https://partner.microsoft.com/ "Daniel" wrote in message ... OK, so I have the first part nicely setup (Select a company and view all of their products in the subform), but I'm having a problem with the reverse setup. My products table has 28 different products plus the company ID (the foreign key). I only want one combo box that lists all 28 products, so the user can select one product and see which companies have that product. I created a combo box and typed in all 28 products, but I'm not sure how to take the value selected and display the associated companies. Thanks "Jeff Boyce" wrote: Daniel If I wanted to show all Products for a Company, and all Companies for a Product, and if I had to maintain those lists, I would use (two) main form/subform setups. The main form would allow me to select, say, a Company, and the subform would list all (related) Products (and vice versa for the second setup). If I only wished to see the data (not edit it), I'd use (two) forms, with a selection of Company and a listbox with related Products (and vice versa). -- Regards Jeff Boyce Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ Microsoft Registered Partner https://partner.microsoft.com/ "Daniel" wrote in message ... Well, heres the background: I set up a table, and then a form associated with that table, to store company information (name, address, phone numbers, etc...). Later, I decided I wanted to store which companies made certain products, so I set up a second table that was related to the first that stored only product information. Now I have another form that is a search/filter form. It works nicely when all you want to do is filter by company information, but I am trying to figure out how to also filter by product information. My question comes from that fact that product information and company information are in two seperate forms. Is there a way to filter two related forms? Here are two examples of what I want to do: 1) Show all companies in my database who have Product X. 2) Show all companies in the USA who have Product Z. Hope this makes sense. "Jeff Boyce" wrote: Daniel One approach might be to use "cascading comboboxes" (you can search on-line and Access HELP for more on this topic). The general idea is that the first combobox lets you pick one of something (say, a company). The second combobox is refreshed to list only those items (say, Products) that are associated with the first combobox's selection. Is that what you're after? -- Regards Jeff Boyce Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ Microsoft Registered Partner https://partner.microsoft.com/ "Daniel" wrote in message ... Hi, I have two tables, one that stores company information, and a second that stores the company products. I set up a search form that gets data from tblCompanyInformation (I use combo boxes and a command button to filter the form), but I want to add another combo box that gets data from tblProducts to be able to filter by which companies have certain products. Is there a way to do this? Thanks |
#7
|
|||
|
|||
How do I access a second table from a form?
No, I only have two tables. tblCompanyInfo, and tblProducts.
There are 28 unique products (column headings/fields) in tblProducts. I don't know how to setup a query that gets information from the headings as opposed to the actual fields under the headings. Then, take the value chosen by the user (which is a heading in my table) and look down the records to see which records have a "yes" in that field. Does that make sense? "Jeff Boyce" wrote: Daniel "... and typed in all 28 products..."?! I misunderstood. I thought you had a table that lists the products. Why would you type them in when you could use a query to get them for the combo box? If you already have Company (view Products) set up, couldn't you just reverse the process that you used? Do you have another source of data for the Company (view Products) than you do for the Products (view Companies)? Let me make sure of something... since you have Companies, and Products, and valid Company X Product combinations, you have three tables, right? -- Regards Jeff Boyce Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ Microsoft Registered Partner https://partner.microsoft.com/ "Daniel" wrote in message ... OK, so I have the first part nicely setup (Select a company and view all of their products in the subform), but I'm having a problem with the reverse setup. My products table has 28 different products plus the company ID (the foreign key). I only want one combo box that lists all 28 products, so the user can select one product and see which companies have that product. I created a combo box and typed in all 28 products, but I'm not sure how to take the value selected and display the associated companies. Thanks "Jeff Boyce" wrote: Daniel If I wanted to show all Products for a Company, and all Companies for a Product, and if I had to maintain those lists, I would use (two) main form/subform setups. The main form would allow me to select, say, a Company, and the subform would list all (related) Products (and vice versa for the second setup). If I only wished to see the data (not edit it), I'd use (two) forms, with a selection of Company and a listbox with related Products (and vice versa). -- Regards Jeff Boyce Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ Microsoft Registered Partner https://partner.microsoft.com/ "Daniel" wrote in message ... Well, heres the background: I set up a table, and then a form associated with that table, to store company information (name, address, phone numbers, etc...). Later, I decided I wanted to store which companies made certain products, so I set up a second table that was related to the first that stored only product information. Now I have another form that is a search/filter form. It works nicely when all you want to do is filter by company information, but I am trying to figure out how to also filter by product information. My question comes from that fact that product information and company information are in two seperate forms. Is there a way to filter two related forms? Here are two examples of what I want to do: 1) Show all companies in my database who have Product X. 2) Show all companies in the USA who have Product Z. Hope this makes sense. "Jeff Boyce" wrote: Daniel One approach might be to use "cascading comboboxes" (you can search on-line and Access HELP for more on this topic). The general idea is that the first combobox lets you pick one of something (say, a company). The second combobox is refreshed to list only those items (say, Products) that are associated with the first combobox's selection. Is that what you're after? -- Regards Jeff Boyce Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ Microsoft Registered Partner https://partner.microsoft.com/ "Daniel" wrote in message ... Hi, I have two tables, one that stores company information, and a second that stores the company products. I set up a search form that gets data from tblCompanyInformation (I use combo boxes and a command button to filter the form), but I want to add another combo box that gets data from tblProducts to be able to filter by which companies have certain products. Is there a way to do this? Thanks |
Thread Tools | |
Display Modes | |
|
|