Search records based on multiple check boxes

[maytricks]

Hello! I'm not very familiar with access, but I know it can do what I want - just need to figure out how.



Okay, here's the question:

I have a table of data regarding companies (contact info, etc). The company I work for provides these companies with up to 10 different products. On my input form, I have created 10 unbound check boxes (and thus, 10 columns of Yes/No data in the corresponding table).

Each company has at least 1 product checked off, and up to all 10.

I would like to create a combo box that lists all 10 products, and upon selecting one, a list box then populates with the names of the companies (primary key) that use that specific product.

So, for example,
Company A buys CATS and DOGS from us
Company B buys DOGS
Company C buys CATS and ELEPHANTS

Combo box options: CATS, DOGS, ELEPHANTS

If I choose DOGS, then a list box gets populated with:
Company A
Company B
and when I click either of these, the record should be brought up for the respective company.

All of this should take place in the header of the form, while the form itself can be updated based on search selections.

It doesn't seem like an overly complicated thing to do, it's just that I don't have much experience in Access and haven't had a ton of luck doing the usual searching.

Thanks for your help!

[June7]

Don't understand why checkboxes are unbound.

What you want will not be easy with the Yes/No field structure. Really need a junction table that associates companies with products, like:

CompanyID (foreign key)
ProductID (foreign key)

Then sounds like you need dependent (cascading) combo or list boxes that use the junction table as RowSource. Review: http://datapigtechnologies.com/flash...combobox2.html

[maytricks]

The form I've created is used for inputting new company information. The best way I could think of to list products that the company would use was to create a check box field (around 10 of them). The result is 10 fields of True/False data in my Table.

I was thinking it should work fine if I could query the table based on the Field that corresponds to the selected combobox value, so something like:

SELECT frmCom.[Company Name]
FROM tblCom
WHERE (((tblCom.[Forms]![frmCom]![comboProduct])=True));

Obviously this is incorrect due to lack of knowledge of how to form this argument, but is this idea possible?

Don't understand why checkboxes are unbound.

What you want will not be easy with the Yes/No field structure. Really need a junction table that associates companies with products, like:

CompanyID (foreign key)
ProductID (foreign key)

Then sounds like you need dependent (cascading) combo or list boxes that use the junction table as RowSource. Review: http://datapigtechnologies.com/flash...combobox2.html

[maytricks]

Okay, so I'm trying to junction table route and running into roadblocks in my mind.

I have my tblCompanies and tblProducts, where tblProducts has a one-to-many relationship with CompanyID from tblCompanies (connecting the names).

So now I have tblCompanies with a nested tblProducts within it.

From this point, I can manually edit the nested Products and the result is tblProducts will have a row stating Company Name in column one and Product Name in column two.

I'm really confused as to how to handle this from this point forward. The end goal is to be able to select a product from a dropdown box and a list of companies which use that product would be displayed, and when one is selected the current record would go to that companyID and show it's information.

I'm not sure how to go about creating a system to mark which products a company uses in Forms.

[June7]

You have tblCompanies and tblProducts? The junction table would be tblCompanyProducts. Refer back to post #2. Each company can have many products and each product can be associated with many companies. This is a many-to-many relationship and that is why junction table is needed.

A form to enter/edit company and its associated products could be form/subform arrangement. Main form bound to tblCompanies and subform bound to tblCompanyProducts. Combobox in subform to select products.

A form to enter/edit product and its associated companies could also be form/subform arrangement. Main form bound to tblProducts and subform bound to tblCompanyProducts. Combobox in subform to select companies.


At this point, not sure where you would use dependent (cascading) comboboxes. Build a query that joins tblCompanies and tblProducts to tblCompaniesProducts. The Jointype for each join would be "Include all records from tblCompanyProducts ...". This will make the related info of company and product names available to the comboboxes.

RowSource for comboProducts: SELECT DISTINCT tblCompaniesProducts.ProductID, ProductName FROM QueryName ORDER BY ProductName;
ColumnCount: 2
ColumnWidths: 0";1"
BoundColumn: 1
ControlSource: ProductID
Code in AfterUpdate event of comboProducts: Me.comboCompanies.Requery

RowSource for comboCompanies: SELECT DISTINCT tblCompaniesProducts.CompanyID, CompanyName FROM QueryName WHERE ProductID=Forms!formname!comboProducts ORDER BY CompanyName;
ColumnCount: 2
ColumnWidths: 0";1"
BoundColumn: 1
ControlSource: CompanyID
Code in AfterUpdate event of comboCompanies: you mentioned going to record of the selected CompanyID