Creating Checkboxes from a List

[SLW612]

Hi, I am mostly a beginner at Access so bear with me. I wasn't sure which
group to which this question applies most.

I am working on a database that has one table for company information -
name, address, contact info, etc., and another table with a list that I want
to essentially cross reference with a field in the contact table. Say the
list contains the following items:

Apple
Orange
Banana
Pear
Grapes

I want to create a form that has one tab with the company info (this part is
already done), and another tab shows checkboxes next to each of the list
items so that the user can enable the checkbox next to each applicable item
for that specific company. So if Company A buys Oranges and Pears, those two
items would have a checkmark and the other three wouldn't. However, I'm
having trouble designing the table that will pull the Company information AND
the list information together correctly for my checkboxes.

The list of items will not change, but users will be adding new companies.

I hope this was enough information - I apologize if it wasn't and will try
to clarify as best I can if needed. I appreciate any help in advance!

 

[Ken Sheridan]

Firstly you need a third table. At present you have two tables modelling the
Companies and Products (or whatever) entity types. There is a many-to-many
relationship type between them in that each company might buy one or more
products, and each product might be bought by one or more company. The way
to model a many-to-many relationship type is by a third table with two
foreign key columns which reference the primary keys of the other two tables,
so in this case you'd have a table such as Requirements with columns such as
CompanyID and ProductID. The two columns in combination would be the table's
composite primary key, and should be defined as such in table design view.

Note that if the CompanyID and ProductID primary key columns of the
Companies and Products tables are autonumbers, the corresponding columns in
Requirements must not also be autonumbers, but straightforward long integer
data type columns.

When it comes to the user interface you could do it the way you want, but it
would require unbound check boxes and you'd have to write code to read the
values from the Requirements table for existing data, to write values to the
Requirements table when new data is added, and to delete rows from the table
when data is amended.

The more usual, and far simpler set up for this type of data, and one which
requires no VBA code at all, would be to have a form in single form view
bound to a query on the Companies table which sorts the companies by name,
e.g.

SELECT *
FROM Companies
ORDER BY CompanyName;

and within it a continuous view subform bound to the Requirements table.
The subform would be linked to the parent form on CompanyID and would contain
a combo box bound to the ProductID column and set up as follows:

RowSource: SELECT ProductID, ProductName FROM Products ORDER BY
ProductName;

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

To select a product for the company currently showing in the parent form
would simply be a case of selecting an item from the combo box's list. As
many products can be added per company simply by inserting new rows in the
subform, selecting the product fro each. If an attempt is made to add the
same product twice for the same company this will violate the primary key, so
the user would be presented from doing so.

Ken Sheridan
Stafford, England

 

[SLW612]

Thanks for the quick response, Ken, but I'm still having trouble.

I created the third table ("Requirements") and bound both CompanyID and
ProductID to their respective tables. I set up the form with the subform
control and created the combobox bound to the ProductID table. So far
everything is going swimmingly.

However, I am unable to select the items listed in the combobox. The drop
down menu appears just fine, but when I attempt to click one and keep it
showing, the menu goes away and the value appearing is still blank. Is there
a property I'm missing?

Thanks in advance (again!)

 

[Ken Sheridan]

I'm not sure what you mean by "bound both CompanyID and ProductID to their
respective tables". Forms are 'bound to' tables and controls 'bound to'
columns (aka fields).

Lets take use some hypothetical values and see how the tables should look.
Firstly the Companies table. This might have rows:

CompanyID CompanyName

1 Ford
2 Chrysler

Next the Products table:

ProductID ProductName

1 Apple
2 Orange
3 Banana
4 Pear
5 Grapes

Lets assume Ford buys apples and bananas, and Chrysler buys bananas and
grapes, the Requirements table will contain rows:

CompanyID ProductID
1 1
1 3
2 3
2 5

If you've set up a companies form with a requirements subform in the way I
described, linking the form and subform on CompanyID by setting this as the
LinkMasterFields and LinkChildFields properties of the subform control, and
including in the subform a combo box with ProductID as its ControlSource and
with its other properties set up as I described, then you should be able to
select products for the current company in the subform. Check firstly that
the table designs are as above, then that the combo box's properties are set
up correctly as I've described, that the subform is based on the Requirements
table and is linked to the parent form on CompanyID.

For an example of a subform used in this way take a look at the Orders form
in the sample Northwind database which comes with Access. You'll see that
this contains an Orders Subform in which products ordered by the current
customer in the main parent form are selected from a combo box. The subform
is more complex than yours as it also contains price and quantity data, but
fundamentally its the same, being based, via a query as its RecordSource, on
the Order Details table, which models a many-to-many relationship between
Customers and Orders. The combo box in the subform has three columns, of
which two are visible, rather than the two columns, of which one is visible,
which yours has, but that's not a significant difference.

Ken Sheridan
Stafford, England