SubForm - data entry



I have an Unbound Main Form and a SubForm - using the subform for data entry.
The SuBForm Default view is 'Datasheet view'.

The MainForm contains all the static data for each record created in the
subform (ie., Date, Ref.No. etc).

Works fine - EXCEPT when the user goes back to the Main form and makes an
alteration to the original Main Form information (e.g. corrects the Date

As soon as the correction is made in the main Form, the SubForm Closes
without effecting the change in the SubForm.

How can I carry the editing of the Main Form through to all the records in
the SubForm.




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

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

Both of the above are perfectly valid models, but I would recommend option 2.


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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question