form for data with many to many relationship

[SandyR]

I have a DB with 3 files:
TRADE NAMES
id - autonumber
fields with Trade Name Info
OWNERS
ID- autonumber
fields with Owner Info
LINK_TN_OWN
ID -autonumber
ID from Trade Name table
ID from owner table
fields pertaining to the Trade Name/Owner combination

Whenever a new trade name is created, at least one owner must be entered.
The users must be able to do the following:
enter new trade names and corresponding owner(s)
add new owners to existing trade names
modify either trade names, owners, or the info specific to the combination.

I have set up a form on the trade name table with a subform to show the
owners. The record source for the owners is a query like this:
Select [link_tn_own].[file_date], [link_tn_own].[note] ,
[owners].owner_name], ...
FROM [OWNERS] INNER JOIN [Link_tn_own] on [owners].[id] =
[link_tn_own].O_ID

This works perfectly for displaying existing records, but it doesn't allow
me to add owner records. I don't know how to set up the screen so that I can
add new owners and link records.

Can anyone give me some pointers?
Thanks!

 

[John W. Vinson]

I have a DB with 3 files:
TRADE NAMES
id - autonumber
fields with Trade Name Info
OWNERS
ID- autonumber
fields with Owner Info
LINK_TN_OWN
ID -autonumber
ID from Trade Name table
ID from owner table
fields pertaining to the Trade Name/Owner combination

Whenever a new trade name is created, at least one owner must be entered.

Why? Any reason not to just enter all available tradenames in advance?

The users must be able to do the following:
enter new trade names and corresponding owner(s)
add new owners to existing trade names
modify either trade names, owners, or the info specific to the combination.

I have set up a form on the trade name table with a subform to show the
owners. The record source for the owners is a query like this:
Select [link_tn_own].[file_date], [link_tn_own].[note] ,
[owners].owner_name], ...
FROM [OWNERS] INNER JOIN [Link_tn_own] on [owners].[id] =
[link_tn_own].O_ID

This works perfectly for displaying existing records, but it doesn't allow
me to add owner records. I don't know how to set up the screen so that I can
add new owners and link records.

Normally one would base the subform on LINK_TN_OWN by itself, without joining
the subform; the subform would have a combo box based on Owners (displaying
the owner name but bound to the owner ID). You could have the combo's Not In
List event set to open the (separate) Owners form to enter information about
the owner.

It's a chicken or egg problem - if you insist that a new LINK_TN_OWN record
cannot exist until an Owner exists, and that a new Owner record cannot exist
until a LINK_TN_OWN record exists, you've painted yourself into a corner!

 

[SandyR]

Thanks for your reply.

The reason that you can't put all the trade names in in advance is that
every day people come in to register new ones.

I will try to make the subform on the link record as you suggested and
display the other owner fields (for existing records) in unbound text boxes
that are populated in the on current event and use a supplementary screen for
the not-in-list event.

I have a DB with 3 files:
TRADE NAMES
id - autonumber
fields with Trade Name Info
OWNERS
ID- autonumber
fields with Owner Info
LINK_TN_OWN
ID -autonumber
ID from Trade Name table
ID from owner table
fields pertaining to the Trade Name/Owner combination

Whenever a new trade name is created, at least one owner must be entered.

Why? Any reason not to just enter all available tradenames in advance?

The users must be able to do the following:
enter new trade names and corresponding owner(s)
add new owners to existing trade names
modify either trade names, owners, or the info specific to the combination.

I have set up a form on the trade name table with a subform to show the
owners. The record source for the owners is a query like this:
Select [link_tn_own].[file_date], [link_tn_own].[note] ,
[owners].owner_name], ...
FROM [OWNERS] INNER JOIN [Link_tn_own] on [owners].[id] =
[link_tn_own].O_ID

This works perfectly for displaying existing records, but it doesn't allow
me to add owner records. I don't know how to set up the screen so that I can
add new owners and link records.

Normally one would base the subform on LINK_TN_OWN by itself, without joining
the subform; the subform would have a combo box based on Owners (displaying
the owner name but bound to the owner ID). You could have the combo's Not In
List event set to open the (separate) Owners form to enter information about
the owner.

It's a chicken or egg problem - if you insist that a new LINK_TN_OWN record
cannot exist until an Owner exists, and that a new Owner record cannot exist
until a LINK_TN_OWN record exists, you've painted yourself into a corner!