master/child link nested subfrm

[mark r]

I seem to be creating new records in my maintable when I
donot intend to when I click on a nested subform which
contains a field from my mainform/maintable.

I think the problem is with master field links and child
links that are out of sync

I know I can eliminate the problem one of two ways.
1) by removing the nested subform.
2) remove the field that is causing the problem and put it
into table4.
But for some reasons, I would like not to do either.

I have a mainform for maintable.
It has a command form which opens a subform for table4.
They both have ID as autonumber primary key. One to one
relationship, referential integrity /cascade delete.
subform for table4 is cycle:current record.
I have no problem moving to different records and creating
or updating the correct corresponding records between the
two tables.

The problem arises when I try to enter data in a subform
that is nested in the subofrmtable4.

That nestedsubform has only two fields and they from the
original maintable. I had originally placed these fields
in my maintable. much much later on, I created another
table, since my maintable was getting too big, for more
similar data to these two fields. one to one relationship,
done for table size reasons.... but never placed the first
two fields into the new table. If I move the two fields
now, I am thinking that many queries and reports, etc will
get messed up.

So when I click commandbutton to open subfomrtable4 and
then click on the nested subform, I accidentally create a
new record in maintable instead of updating the data in
the established record.

Mainform displays data for..........Maintable record 25
subformtable4 displays data for.....table4 record 25
if I click a field in nestedsubform
it tries to create a record for.....maintable record 38

Is there a way to make the nested subform "cycle" and
correspond to the
same record ID as the other two forms?

 

[Bill]

Logic for this problem should be this:
MainTable.MainTableID(AuotNumber) Relates to SecondTable.MainTableID not
SecondTable.SecondTableID(AutoNumber) If this is a subform containing
records (more than one) it should be a one to many relationship, otherwise
relate the tables one to one by duplicating in the second table the primary
key and making it unique in value, then only use one form to hold all the
fields.
Repeat this for other subforms and tables. Note: A relationship must exist
between the records in subform 3 and subform 4 by means of a common field
with a relationship of one to one.
Upon more thought the third subforms recordset must relate to the second and
the fourth by means of the same field SecondTable.MainTableID =
ThirdTable.MainTableID and ThirdTable.MainTableID = Maintable.MainTableID,
else a new record will always be created upon entering any data in subform
4.
Does this make sense?
HTH