This is a 'road to Dublin' question as the real issue here is, as Rick points
out, that the table design is badly flawed. You are in effect using the
column to which the text box is bound to hold a data structure, which the
relational model forbids, the table not being in First Normal Form (1NF)
whose basis is:
First Normal Form: A relvar is in 1NF if and only if, in every legal value
of that relvar, every tuple contains exactly one value for each attribute.
Loosely speaking, in the formal language of the relational model, a relvar
(relation variable) equates to a table, a tuple to a row (record) and an
attribute to a column (field).
Lets say the table is called Foos and its primary key column is Foo. The
values shown in the multi-select list box are from a column Bar in a table
Bars. What you have is a many-to-many relationship between Foos and Bars. A
many-to-many relationship is modelled by a separate table which references
the primary keys of the two tables, so you should have a table FooBars with
foreign key columns Foo and Bar. Together these two column constitute the
composite primary key of the table.
For data entry you'd use a form, in single form view, based on Foos and
within it a subform, in continuous form (preferred) or datasheet view, based
on FooBars. The subform and parent form would be linked on the Foo columns
and the subform would contain a combo box bound to the Bar column, with a
RowSource property of:
SELECT Bar FROM Bars ORDER BY Bar;
You can now add as many records as you wish related to the parent form's
current record simply by adding new records in the subform, selecting a Bar
value from the combo box in each case.
Ken Sheridan
Stafford, England
