How do I access a second table from a form?

[Guest]

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

 

[Jeff Boyce]

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


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Guest]

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]

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


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Guest]

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]

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


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Guest]

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?