Glynn:
It sounds to me like the table on which the subform is based needs
decomposing into two tables. If you have multiple columns in the subform's
table with the same set of values in different rows then the table exhibits
redundancy and consequently leaves the door open to update anomalies.
To normalize the table you have two options:
1. Keep the table as it is and create a new table with the same 'static'
columns. Define this set of columns in the new table as its primary key and
create a relationship with the existing table on these columns, enforcing
referential integrity and cascade updates. You can easily populate the new
table from the existing table with an append query. The duplicated rows will
be rejected because of the key violations so the new table will have one row
per distinct set of data. Your main form can then be a bound form with the
new table as its RecordSource, linking it and the subform on the set of key
columns.
2. The second option would be to create the new table as above, but instead
of the multiple key add an autonumber column as its primary key. The set of
columns reflecting those in the existing table should be indexed uniquely,
however, as these still constitute a candidate key. Then add a new long
integer number column to the existing table as a foreign key referencing the
primary key of the new table. Populate the new table the same way as before
with an append query, then join the new table to the existing table on the
set of common columns (excluding the two new key columns) in an update query
and update the foreign key in the existing table with the values of the
primary key in the new table. Create a relationship on the two new numeric
columns, enforcing referential integrity but not cascade updates as an
autonumber column's value can't be changed, so this would be pointless. You
can then delete the original set of common columns from the existing table as
the two tables are new related on the new 'surrogate' numeric key columns.
Again your main form can then be a bound form with the new table as its
RecordSource, linking it and the subform on the single key columns in this
case.
Both of the above are perfectly valid models, but I would recommend option 2.
BEFORE YOU MAKE THESE SORT OF AMENDMENTS ITS IMPERATIVE THAT YOU BACK UP
YOUR TABLES OF COURSE.
If you do use multiple column keys (option 1) there is no need to show the
set of foreign key columns in the subform as these will be the same as the
main form's current record.
Ken Sheridan
Stafford, England
