-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
It seems you're doing more work than is necessary.
Why do you have a "joining table" BusinessRel? That is usually a
solution to a Many-to-Many relationship problem. I.e., many business
have many owners and many owners have many business. Is that your
situation? If it isn't remove the table BusinessRel and the trigger on
the Owners table.
Usually, when I have a many-to-many suitation I have a main form that
represents the "left" side of the relationship and a subform that
represents the "right" side of the relationship. The subform feeds the
joining table. The Link Child Field holds the right side's ID and the
Link Master Field holds the left side's ID. I put a ComboBox on the
subform that selects the "right" side ID.
In your case the main form would be the Business side (the left side)
and the subform would be the Owners (the right side). Every time you
select an Owner from the subform's ComboBox both IDs would be saved to
the joining table.
You have to create the entities before linking them in the joining
table. IOW, there would be an Owners form w/ a Business subform and a
Business form w/ an Owners subform. Before adding an owner to the
Business-Owners join there has to be both a Business entity & and Owner
entity. Ditto for adding a business to an owner.
You can set up the subforms to allow the user to enter a new entity if
they find it isn't one of the entities on the ComboBox's selection list.
I usually use a button labeled "Add <entity type>." E.g.: "Add
Owner." The button click would bring up the data entry form for the new
entity.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQmhb94echKqOuFEgEQIWpwCeOaxSitQiLQdty77SgZ7TGs qFpWYAn2lK
8tIPsazqTqzUrwNtx6uEgJgI
=yuPi
-----END PGP SIGNATURE-----
Terry wrote:
Hello,
I wonder if anyone can shed light on this problem for me. I have an
Access 97 front end with an SQL 2000 database. There is a Business main
form with an Owner subform and corresponding tables of the same names.
A third table BusinessRel records the BusinessID (linked to Business
table) and OwnerID (linked to Owner table).
This is what happens when the Business main form is loaded. I enter the
BusinessID and other stuff, then as soon as I click on the Owner
subform the BusinessID is hightlighted in a BusinessID field on the
subform. In the background I have a trigger on the Business table to
create the Owner record and a trigger on the Owner table to create the
BusinessRel record. (These all get created perfectly becuase if I close
down the main form and load it up I can edit the Owner subform fields.
However, if I click on the Owner fields to enter more details an error
comes up basically stating that there is no such Owner record. Although
there is, because the triggers work.
I have a feeling that this is only [Bad] news to me. And is due to the
main form and subform and SQL trigger timing.
I would appreciate any advice or help you can give to resolving this.
Kind regards
Terry
