As Ken says, a subform is a far easier solution, though its not a difficult
task to execute an append query in code for each area to which an
organization is assigned. However, unless each area has only one
organization operating in it at any one time, you'll need a third table to
model the many-to-many relationship between Orgnizations and Areas. The Area
table would have only one row per area and the new table, AreaOrganizations
say, would have one row per organization/area combination. It would do this
by having two foreign key columns (fields) Organization and Area each
referencing the primary keys of the other two tables. The primary key of
this table would be a composite one made up of the Organization and Area
columns. It might well also have other columns representing other attributes
of each organization's involvement with an area, e.g. the date when they
become involved.
Again a subform would be the easiest solution for data entry, but would now
be based on the AreaOrganizations table and be linked to the parent form on
the Organization columns. The subform would have a combo box bound to the
Area column, with its RowSource listing the areas from the Area table, so
that a user can simply pick an area from a list, along with other controls
bound to any other columns. It would be unnecessary to include a column
bound to the Organization column in the subform, however, as the value is
entered automatically into this column behind the scenes by means of the
linking mechanism.
Ken Sheridan
Stafford, England
