List Box - Variable Row Source

[bhipwell via AccessMonster.com]

Hello,

I have a table which contains data for employers. The table then has a field
for each type of product they could offer to indicate if they offer the
product or not...

CompanyID
PeachesOffered (Yes or No)
PlumsOffered (Yes or No)
OrangesOffered (Yes or No)
BananasOffered (Yes or No)
PearsOffered (Yes or No)

I am creating a list box on a form which needs to only display those products
offered by the filtered Company. When I open the form, I have already
filtered down to the specific company we want to work with.

As an example, I may have a row of data that says:

Company ABC; Yes; No; Yes; Yes; No

The "Yes" and "No" correspond to the their product offerings in the table.
Based on this, I want the list box to show...

Peaches
Oranges
Bananas

Plums is left out because the data showed "No".

How do I get the list box to do this?

Thanks!

BH

 

[Beetle]

1) Step on the brakes

2) Put it in reverse

3) Back up a bit

You need to rethink your table structure before you worry about your listbox.
What you're doing now is going to cause you nothing but problems. You
would appear to have a many-to-many relationship between Companies
and the Products (fruit) that they offer, so you need at least three tables.
One for Company specific data, one for the Product specific data and a
third (junction table) to define the relationship. For example;

tblCompanies
**********
CompanyID (Primary Key)
CompanyName
Address
City
State
(other data related specifically to each Company)


tblProducts
********
ProductID (PK)
ProductDescription
(other Product specific data)


tblCompanyProducts (the junction table)
***************
CompanyID (Foreign Key to tblCompanies)
ProductID (FK to tblProucts)

CompanyID and ProductID could be a combined Primary Key in
tblCompanyProducts. You would create a one-to-many relationship between
tblCompanies and tblCompanyProducts base on CompanyID, an another
one-to-many relationship between tblProducts and tblCompanyProducts
based on ProductID

Depending on how many different Products there are, you may also want
to incorporate a table for Product Categories. You only mentioned a few
types of fruit, so it's hard to say for sure.

For further discussion on why *not* to use a collection of Yes/No fields
(like you're doing now) to store data see;

http://www.allenbrowne.com/casu-23.html

 

[ghetto_banjo]

If you dont have one already, have a table setup with a column that
holds peaches, plums, etc.

Then have the listbox source be based on a query that uses the
checkboxes on the form as criteria for the above table. In the VB
code for the after update on the checkboxes , put me.listbox1.requery
which will refresh the listbox source after a checkbox is changed.

 

[bhipwell via AccessMonster.com]

Thanks Beetle,

I am actually working now to normalize the database. Perhaps you can help me
further...

1. Lets say there is a finite, pre-determined number of products that can
every be offered. Just Plums, Peaches, Bananas and Oranges. There will
never be any other products. Since all I need to know is whether the company
offers each product, would l still need and additional table?

2. If the answer is yes (which I am sure it is), how would I be indicating
which products are being offered by each company? If every product is listed
in a single table and the company table references the Products table,
wouldn't I be restricted to only selecting one product?

Thanks again,

BH

 

[bhipwell via AccessMonster.com]

Beetle,

Scratch my follow up inquiry. The link you provided it great. Thanks!

BH