You'll need to go further than adding columns to the table and binding
the check boxes to them. Having a column in the table for each choice
is a very bad design; its what's known as 'encoding data as column
headings'. A fundamental principle of the database relational model,
'the information principle' is that data is stored as explicit values
at column positions in rows in tables, and in no other way.
What you almost certainly have here is many-to-many relationship
between the table on which the form is based (lets call it People) and
the 'choices'. The correct way to model this is with three tables,
People, Choices and PeopleChoices. The People table has one row per
person, the Choices table one row per choice and the PeopleChoices
table has columns such as PersonID and ChoiceID, each being a foreign
key referencing the primary key of the relevant other table. The
primary key of PeopleChoices is a composite one made up of its two
columns. The PeopleChoices table models the many-to-many relationship
between the other two tables, which is how its invariably done in a
relational database.
For data entry, instead of the check boxes on the People form you'd
have a subform based on the PeopleChoices table, and linked to the
parent form on PersonID. The subform would be in continuous form view
and have just one control, a combo box set up as follows:
ControlSource: ChoiceID
RowSource: SELECT ChoiceID, Choice FROM Choices ORDER BY Choice;
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.
For every choice by the current person (i.e. where they'd have checked
a check box in your current form) one row would be inserted in the
subform by selecting the relevant choice from the combo box.
Ken Sheridan
Stafford, England
