Preventing Orphan Records

[Elaine]

I have a Main Form and two subforms. All three are based on tables with both
subforms being related to the main table but not to each other. The two
subforms are: subfrm1Books and subfrm2Copies.

Both the subforms have a calculated field in common called CopiesEd. This is
a concatenation of the fields COPIES and EDITION. Neither of these fields is
a primary key in either table that is the Source for these forms.

I don't want anyone to add a record to subfrm2Copies if that item is not in
subfrm1. In other words if the same CopiesEd is not in subfrm2, it should not
be in subfrm1.

If the above does happen, I would like to make sure that the record is not
saved and to generate a friendly message saying that the Edition is not
available. I have tried to put some code in the BeforeUpdate property of
EDITION in subfrm2 but it does not work correctly.

Could someone please show me how this is done. Thank you!

 

[jahoobob via AccessMonster.com]

Sounds to me as though you need subfrm2Copies to be a subfrom of subfrm1Books
with a one-to-many relationship on the primary key of the table backing
subfrm1 since you could have one book and more than one copy.

 

[Elaine]

Thanks for your answer. I did think about this but the problem is that the
field(s) in subfrm1Books cannot be made into Primary Keys as they duplicate
over and again. So the fields in question Copies and Edition appear multiple
times in both forms (tables).

It is a mess but is there any way out?