Many-to-many implementation problem

Access 2003:
I have a database containing contact information and other attributes for
members of an organization. Some of those members work for other
organizations. In some cases, they work for the same other organization.

In those other organizations, they may have more than one boss. In some
cases they have the same boss(es) in the other organization and in other
cases they have unique boss(es).

The intent of the database design is to view/enter the contact information
for each member and, if they work for another organization, to view/enter
that organizations’s address, etc and to select the member’s boss(es) in that
organization.

I’m getting an error message from Access when I try to select the member’s
boss(es). Because of the relationships between the tables, I’m guessing that
the problem is in my table design and not in the queries, form/subform links
or row sources. The error message is: “ You cannot add or change a record
because a related record is required in table ‘OtherOrg’ �

My table design is:

tblBaseOrgMembers
MemberID (autonumber) (PK)
LastName
FirstName
City
Etc

tblOtherOrg
OtherOrgID (autonumber) (PK)
OtherOrgName
City
Etc

tblOtherOrgLeaders
OtherLdrID (autonumber) (PK)
OtherOrgID (FK)
LastName
FirstName
City
Etc

tblBaseOrg-Other [junction table with all three fields part of the composite
foreign key, which is indexed]
MemberID (long integer, not required, not indexed)
OtherOrgID (“, “, “)
OtherLdrID (“, “, “)

The junction table relationships a
tbleBaseOrgMembers:MemberID - MemberID (1:M)
tblOtherOrg:OtherOrgID - :OtherOrgID (1:M)
tblOtherOrgLeaders:OtherLdrID - OtherLdrID (1:M)
with referential integrity enforced.

Also, there is a relationship between tlbOtherOrg and tblOtherOrgLeaders
which uses OtherOrgID (1:M)

Data entry:
I’ve successfully loaded the OtherOrg and OtherOrgLeaders tables with data
using a form/subform designed just for that purpose.

I have another form for BaseOrg data entry which also contains a combo box
that selects the desired OtherOrg. That combo box is used to filter the
universe of OtherLdr to just those possible leaders for that BaseOrg member.

When I attempt to select one of the possible leaders on a subform (part of
the BaseOrg form), I get the error message from Acess: “ You cannot add or
change a record because a related record is required in table ‘OtherOrg’ �

Does anyone see a problem with this table design?

Thanks.

--
Al Williams

Hi Al

Didn't actually read through all this carefully, but you can get that error
message if your subform is based on a query, and one of the fields from the
other table contains a Default Value. Access misinterprets the default value
and complains that it can't add the record to the lookup table.

For another possible way to model your scenario, see:
People in households and companies
at:
http://allenbrowne.com/AppHuman.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Al Williams" wrote in message
...
Access 2003:
I have a database containing contact information and other attributes for
members of an organization. Some of those members work for other
organizations. In some cases, they work for the same other organization.

In those other organizations, they may have more than one boss. In some
cases they have the same boss(es) in the other organization and in other
cases they have unique boss(es).

The intent of the database design is to view/enter the contact information
for each member and, if they work for another organization, to view/enter
that organizations's address, etc and to select the member's boss(es) in
that
organization.

I'm getting an error message from Access when I try to select the member's
boss(es). Because of the relationships between the tables, I'm guessing
that
the problem is in my table design and not in the queries, form/subform
links
or row sources. The error message is: " You cannot add or change a
record
because a related record is required in table 'OtherOrg' "

My table design is:

tblBaseOrgMembers
MemberID (autonumber) (PK)
LastName
FirstName
City
Etc

tblOtherOrg
OtherOrgID (autonumber) (PK)
OtherOrgName
City
Etc

tblOtherOrgLeaders
OtherLdrID (autonumber) (PK)
OtherOrgID (FK)
LastName
FirstName
City
Etc

tblBaseOrg-Other [junction table with all three fields part of the
composite
foreign key, which is indexed]
MemberID (long integer, not required, not indexed)
OtherOrgID (", ", ")
OtherLdrID (", ", ")

The junction table relationships a
tbleBaseOrgMembers:MemberID - MemberID (1:M)
tblOtherOrg:OtherOrgID - :OtherOrgID (1:M)
tblOtherOrgLeaders:OtherLdrID - OtherLdrID (1:M)
with referential integrity enforced.

Also, there is a relationship between tlbOtherOrg and tblOtherOrgLeaders
which uses OtherOrgID (1:M)

Data entry:
I've successfully loaded the OtherOrg and OtherOrgLeaders tables with data
using a form/subform designed just for that purpose.

I have another form for BaseOrg data entry which also contains a combo box
that selects the desired OtherOrg. That combo box is used to filter the
universe of OtherLdr to just those possible leaders for that BaseOrg
member.

When I attempt to select one of the possible leaders on a subform (part of
the BaseOrg form), I get the error message from Acess: " You cannot add
or
change a record because a related record is required in table 'OtherOrg' "

Does anyone see a problem with this table design?

Thanks.

--
Al Williams

Allen,

1. Yes, the subform that I'm having problems with does use a query that
includes all three fields from the junction table's composite foreign key and
all the fields from tblOtherOrgLeaders. I'm very new at many-to-many
relationships and followed the pattern of a successful many-to-many
implementation that had two M:M tables - but not three like I'm doing.

Since it may be that the composite foreign key in the junction table isn't
being updated properly, let me describe how I'm trying to do the update.

I believe that the first field in the junction table is defined at time of
update by the form/subform Master Field/Child Field linkage between the
BaseOrg and the OtherOrgLeaders subform. The third field is defined by the
combo box selection in the OtherOrgLeaders subform. It's the second field,
OtherOrgID that I'm not certain is being handled correctly. I'm assuming
that since the OtherOrg combo box on the BaseOrg form does cause the proper
subset of OtherOrgLeaders to be available, that that information is available
to the query for the row source. In that query, I'm referencing the BaseOrg
combo box by using a Forms![..]![..] expression. So, to me everything seems
to be there and that everything is in place for a simulataneous update of all
three fields in the CFK - it just doesn't work.

Do you see a problem?

2. Your People in Households and Companies article is very interesting. I'm
going to have to read it carefully. Thanks.

"Allen Browne" wrote:

Hi Al

Didn't actually read through all this carefully, but you can get that error
message if your subform is based on a query, and one of the fields from the
other table contains a Default Value. Access misinterprets the default value
and complains that it can't add the record to the lookup table.

For another possible way to model your scenario, see:
People in households and companies
at:
http://allenbrowne.com/AppHuman.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Al Williams" wrote in message
...
Access 2003:
I have a database containing contact information and other attributes for
members of an organization. Some of those members work for other
organizations. In some cases, they work for the same other organization.

In those other organizations, they may have more than one boss. In some
cases they have the same boss(es) in the other organization and in other
cases they have unique boss(es).

The intent of the database design is to view/enter the contact information
for each member and, if they work for another organization, to view/enter
that organizations's address, etc and to select the member's boss(es) in
that
organization.

I'm getting an error message from Access when I try to select the member's
boss(es). Because of the relationships between the tables, I'm guessing
that
the problem is in my table design and not in the queries, form/subform
links
or row sources. The error message is: " You cannot add or change a
record
because a related record is required in table 'OtherOrg' "

My table design is:

tblBaseOrgMembers
MemberID (autonumber) (PK)
LastName
FirstName
City
Etc

tblOtherOrg
OtherOrgID (autonumber) (PK)
OtherOrgName
City
Etc

tblOtherOrgLeaders
OtherLdrID (autonumber) (PK)
OtherOrgID (FK)
LastName
FirstName
City
Etc

tblBaseOrg-Other [junction table with all three fields part of the
composite
foreign key, which is indexed]
MemberID (long integer, not required, not indexed)
OtherOrgID (", ", ")
OtherLdrID (", ", ")

The junction table relationships a
tbleBaseOrgMembers:MemberID - MemberID (1:M)
tblOtherOrg:OtherOrgID - :OtherOrgID (1:M)
tblOtherOrgLeaders:OtherLdrID - OtherLdrID (1:M)
with referential integrity enforced.

Also, there is a relationship between tlbOtherOrg and tblOtherOrgLeaders
which uses OtherOrgID (1:M)

Data entry:
I've successfully loaded the OtherOrg and OtherOrgLeaders tables with data
using a form/subform designed just for that purpose.

I have another form for BaseOrg data entry which also contains a combo box
that selects the desired OtherOrg. That combo box is used to filter the
universe of OtherLdr to just those possible leaders for that BaseOrg
member.

When I attempt to select one of the possible leaders on a subform (part of
the BaseOrg form), I get the error message from Acess: " You cannot add
or
change a record because a related record is required in table 'OtherOrg' "

Does anyone see a problem with this table design?

Thanks.

--
Al Williams

Hi Al

I think you have:
- a main form bound to [tblBaseOrdMembers], and
- a subform bound to a query based on [tblBaseOrg-Other].
The LinkMasterFields/LinkChild fields will therefore be [MemberID].

In the subform, you have 2 combos:
- one to choose the [OtherOrgId];
- one to choose the [OtherLdrId].
The subform will inherit the [MemberId] from the main form.

To get this working, remove the other tables from the query, so it only has
the 3 fields from [tblBaseOrg-Other].

Once you have that working, you can go back and add tblOtherOrg to the
query, presumably to get OtherOrgName into the query output grid. In query
design view, double-click the line joining the 2 tables. Access pops up a
dialog giving 3 options. Choose:
All records from tblBaseOrg-Other, and any matches from ...

Test the subform. If it fails at this point, open tblOtherOrg in design
view, and remove the Default Value for any fields. Also check that the text
boxes in the form for the fields from tblOtherOrg have not Default Value
set. (You also want to set the Locked property for these text boxes to Yes,
so the user cannot accidentally change them: these fields will be for
information only.)

Once you have that working, you can then open the query in design view, and
add tblOtherOrg to the query. Make it an outer join also, and make sure that
there is no Default Value for any fields in this table, nor for the text
boxes bound to this table.

You should now have it working as you wanted.


On a broader view, your structure is perhaps not ideal. In
tblOtherOrgLeaders, any leader can belong to only one "other org".
Therefore, choosing a value in the [OtherLdrId] combo means you already know
which [OtherOrgId] that leader belongs to. [tblBaseOrg-Other] therefore
should not have a field for [OtherOrgId]. In fact, having both fields in
[tblBaseOrg-Other] leaves the door open for bad data.


Beyond merely dropping the [OtherOrgId] field from [tblBaseOrg-Other], you
might consider a structure where there are just two tables:
- one for tblOrg (OrgId, OrgName, ...)
- one for tblPerson (PersonId, LastName, FirstName, ...)
Put your own organisation in tblOrganisation.
Put your own staff and everyone elses in tblPerson.
Now add a 3rd table that says who works for whom, under which boss:
tblOrgPerson:
OrgPersonId AutoNum p.k.
PersonID Number f.k. to tblPerson.PersonId. Required.
OrgId Number f.k. to tblOrg.OrgId. Required.
RoleId f.k. to tblRole.RoleId.
Optional
BossId Number f.k. to tblPerson.PersonId. Optional

This 3rd table lets you enter who works for which organisation, in what role
(secretary, manager, janitor, ...) and who they report to in that role at
that organisation. This simple structure lets a person work part-time under
different bosses (or the same boss) in different organisations (or the same
organisation), and it's incredibly simple to maintain. Perhaps that would
not achieve what you want, but it certainly seems like the most flexible
approach from what we know of your case.

HTH

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Al Williams" wrote in message
...
Allen,

1. Yes, the subform that I'm having problems with does use a query that
includes all three fields from the junction table's composite foreign key
and
all the fields from tblOtherOrgLeaders. I'm very new at many-to-many
relationships and followed the pattern of a successful many-to-many
implementation that had two M:M tables - but not three like I'm doing.

Since it may be that the composite foreign key in the junction table isn't
being updated properly, let me describe how I'm trying to do the update.

I believe that the first field in the junction table is defined at time of
update by the form/subform Master Field/Child Field linkage between the
BaseOrg and the OtherOrgLeaders subform. The third field is defined by
the
combo box selection in the OtherOrgLeaders subform. It's the second
field,
OtherOrgID that I'm not certain is being handled correctly. I'm assuming
that since the OtherOrg combo box on the BaseOrg form does cause the
proper
subset of OtherOrgLeaders to be available, that that information is
available
to the query for the row source. In that query, I'm referencing the
BaseOrg
combo box by using a Forms![..]![..] expression. So, to me everything
seems
to be there and that everything is in place for a simulataneous update of
all
three fields in the CFK - it just doesn't work.

Do you see a problem?

2. Your People in Households and Companies article is very interesting.
I'm
going to have to read it carefully. Thanks.

"Allen Browne" wrote:

Hi Al

Didn't actually read through all this carefully, but you can get that
error
message if your subform is based on a query, and one of the fields from
the
other table contains a Default Value. Access misinterprets the default
value
and complains that it can't add the record to the lookup table.

For another possible way to model your scenario, see:
People in households and companies
at:
http://allenbrowne.com/AppHuman.html


"Al Williams" wrote in message
...
Access 2003:
I have a database containing contact information and other attributes
for
members of an organization. Some of those members work for other
organizations. In some cases, they work for the same other
organization.

In those other organizations, they may have more than one boss. In
some
cases they have the same boss(es) in the other organization and in
other
cases they have unique boss(es).

The intent of the database design is to view/enter the contact
information
for each member and, if they work for another organization, to
view/enter
that organizations's address, etc and to select the member's boss(es)
in
that
organization.

I'm getting an error message from Access when I try to select the
member's
boss(es). Because of the relationships between the tables, I'm
guessing
that
the problem is in my table design and not in the queries, form/subform
links
or row sources. The error message is: " You cannot add or change a
record
because a related record is required in table 'OtherOrg' "

My table design is:

tblBaseOrgMembers
MemberID (autonumber) (PK)
LastName
FirstName
City
Etc

tblOtherOrg
OtherOrgID (autonumber) (PK)
OtherOrgName
City
Etc

tblOtherOrgLeaders
OtherLdrID (autonumber) (PK)
OtherOrgID (FK)
LastName
FirstName
City
Etc

tblBaseOrg-Other [junction table with all three fields part of the
composite
foreign key, which is indexed]
MemberID (long integer, not required, not indexed)
OtherOrgID (", ", ")
OtherLdrID (", ", ")

The junction table relationships a
tbleBaseOrgMembers:MemberID - MemberID (1:M)
tblOtherOrg:OtherOrgID - :OtherOrgID (1:M)
tblOtherOrgLeaders:OtherLdrID - OtherLdrID (1:M)
with referential integrity enforced.

Also, there is a relationship between tlbOtherOrg and
tblOtherOrgLeaders
which uses OtherOrgID (1:M)

Data entry:
I've successfully loaded the OtherOrg and OtherOrgLeaders tables with
data
using a form/subform designed just for that purpose.

I have another form for BaseOrg data entry which also contains a combo
box
that selects the desired OtherOrg. That combo box is used to filter
the
universe of OtherLdr to just those possible leaders for that BaseOrg
member.

When I attempt to select one of the possible leaders on a subform (part
of
the BaseOrg form), I get the error message from Acess: " You cannot
add
or
change a record because a related record is required in table
'OtherOrg' "

Does anyone see a problem with this table design?

Thanks.

--
Al Williams

Hi Allen,

Thank you very much for responding. I know that working your way through my
info took time and I appreciate it.

It has taken me some time to work through your suggestions because I've
taken a make one change and evaluate the results approach. I haven't gotten
it working yet but I think I can give a better description of what the
problem is. I also realize that I didn't I describe my table design
sufficiently.

There are three tables. The BaseOrg has all the members of the organization
I'm doing the database for. Some of those members work for another
organization (only one organization) which is described in [tblOtherOrg] but
they may have none, one, or many bosses [tblOtherLdr]. The junction table
[tblBaseOrg-Other] has a CFK that contains only foreign keys corresponding to
the three primary keys from the other three tables.

The form for [tblBaseOrg] also has a combo box based on a query the selects
[tblOtherOrg].[OtherOrgID] and [tblOtherOrg].[OtherOrgName]. The result is
bound to a field that I put into [tblBaseOrg] for that purpose.

The query for the subform that selects [tblOtherOrgLeaders] entries is based
on [tblBaseOrg-Other] and [tblOtherOrgLeaders].

That subform has a combo box for selection of the none (which is a N/A entry
which is already in [tblOtherOrgLeaders]), one, or many bosses. That combo
box is based on a query using only [tblOtherOrgLeaders] with
[tblOtherOrgLeaders].[OtherOrgID] having its criteria set to
[Forms]![BaseOrg]![OtherOrgID]. Both fields are a foreign key for
[tblOtherOrg].[OtherOrgID].

Here is what I've done:
1. As suggested, I deleted the field for [OtherOrgId]. You are correct, I
know in advance that there can be only one OtherOrg for a member.
2. I changed all my queries for the combo boxes and and subform to saved
queries (I read in one of the newsgroup postings that that sometimes helps
with update problems).
3. I've tried multiple iterations (all I can think of) including and
excluding fields in the queries - to no avail.
4. Finally, I deleted the 1:M relationship between [tblOtherOrg] and
[tblOtherOrgLeaders] to see what would happen. As a result, the combo box in
the subform does update BUT instead of using existing
[tblOtherOrgLeaders].[OtherOrgLdrID] entries in [tblOtherOrgLeaders] to
populate [tblBaseOrg-Other] the combo box selection is adding entries to
[tblOtherOrgLeaders]. That's why Access is complaining; there is no
simultaneous updates to create new records in [tblOtherOrg].

To try to resolve the problem, I removed all references to any [tblOtherOrg]
fields in the queries and also removed any references to the foreign key,
[tblOtherOrgLeaders].[OtherLdrID]. That didn't resolve it.

I'm now stuck because I don't have any more ideas. I did read your People
in Households and Companies article and think it is a very good. My database
is already deployed with seven people using it. So, if at all possible, I
need to try to figure out a way to live with my existing architecture.

Do you have suggestions? Thanks.

Al


"Allen Browne" wrote:

Hi Al

I think you have:
- a main form bound to [tblBaseOrdMembers], and
- a subform bound to a query based on [tblBaseOrg-Other].
The LinkMasterFields/LinkChild fields will therefore be [MemberID].

In the subform, you have 2 combos:
- one to choose the [OtherOrgId];
- one to choose the [OtherLdrId].
The subform will inherit the [MemberId] from the main form.

To get this working, remove the other tables from the query, so it only has
the 3 fields from [tblBaseOrg-Other].

Once you have that working, you can go back and add tblOtherOrg to the
query, presumably to get OtherOrgName into the query output grid. In query
design view, double-click the line joining the 2 tables. Access pops up a
dialog giving 3 options. Choose:
All records from tblBaseOrg-Other, and any matches from ...

Test the subform. If it fails at this point, open tblOtherOrg in design
view, and remove the Default Value for any fields. Also check that the text
boxes in the form for the fields from tblOtherOrg have not Default Value
set. (You also want to set the Locked property for these text boxes to Yes,
so the user cannot accidentally change them: these fields will be for
information only.)

Once you have that working, you can then open the query in design view, and
add tblOtherOrg to the query. Make it an outer join also, and make sure that
there is no Default Value for any fields in this table, nor for the text
boxes bound to this table.

You should now have it working as you wanted.


On a broader view, your structure is perhaps not ideal. In
tblOtherOrgLeaders, any leader can belong to only one "other org".
Therefore, choosing a value in the [OtherLdrId] combo means you already know
which [OtherOrgId] that leader belongs to. [tblBaseOrg-Other] therefore
should not have a field for [OtherOrgId]. In fact, having both fields in
[tblBaseOrg-Other] leaves the door open for bad data.


Beyond merely dropping the [OtherOrgId] field from [tblBaseOrg-Other], you
might consider a structure where there are just two tables:
- one for tblOrg (OrgId, OrgName, ...)
- one for tblPerson (PersonId, LastName, FirstName, ...)
Put your own organisation in tblOrganisation.
Put your own staff and everyone elses in tblPerson.
Now add a 3rd table that says who works for whom, under which boss:
tblOrgPerson:
OrgPersonId AutoNum p.k.
PersonID Number f.k. to tblPerson.PersonId. Required.
OrgId Number f.k. to tblOrg.OrgId. Required.
RoleId f.k. to tblRole.RoleId.
Optional
BossId Number f.k. to tblPerson.PersonId. Optional

This 3rd table lets you enter who works for which organisation, in what role
(secretary, manager, janitor, ...) and who they report to in that role at
that organisation. This simple structure lets a person work part-time under
different bosses (or the same boss) in different organisations (or the same
organisation), and it's incredibly simple to maintain. Perhaps that would
not achieve what you want, but it certainly seems like the most flexible
approach from what we know of your case.

HTH

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Al Williams" wrote in message
...
Allen,

1. Yes, the subform that I'm having problems with does use a query that
includes all three fields from the junction table's composite foreign key
and
all the fields from tblOtherOrgLeaders. I'm very new at many-to-many
relationships and followed the pattern of a successful many-to-many
implementation that had two M:M tables - but not three like I'm doing.

Since it may be that the composite foreign key in the junction table isn't
being updated properly, let me describe how I'm trying to do the update.

I believe that the first field in the junction table is defined at time of
update by the form/subform Master Field/Child Field linkage between the
BaseOrg and the OtherOrgLeaders subform. The third field is defined by
the
combo box selection in the OtherOrgLeaders subform. It's the second
field,
OtherOrgID that I'm not certain is being handled correctly. I'm assuming
that since the OtherOrg combo box on the BaseOrg form does cause the
proper
subset of OtherOrgLeaders to be available, that that information is
available
to the query for the row source. In that query, I'm referencing the
BaseOrg
combo box by using a Forms![..]![..] expression. So, to me everything
seems
to be there and that everything is in place for a simulataneous update of
all
three fields in the CFK - it just doesn't work.

Do you see a problem?

2. Your People in Households and Companies article is very interesting.
I'm
going to have to read it carefully. Thanks.

"Allen Browne" wrote:

Hi Al

Didn't actually read through all this carefully, but you can get that
error
message if your subform is based on a query, and one of the fields from
the
other table contains a Default Value. Access misinterprets the default
value
and complains that it can't add the record to the lookup table.

For another possible way to model your scenario, see:
People in households and companies
at:
http://allenbrowne.com/AppHuman.html


"Al Williams" wrote in message
...
Access 2003:
I have a database containing contact information and other attributes
for
members of an organization. Some of those members work for other
organizations. In some cases, they work for the same other
organization.

In those other organizations, they may have more than one boss. In
some
cases they have the same boss(es) in the other organization and in
other
cases they have unique boss(es).

The intent of the database design is to view/enter the contact
information
for each member and, if they work for another organization, to
view/enter
that organizations's address, etc and to select the member's boss(es)
in
that
organization.

I'm getting an error message from Access when I try to select the
member's
boss(es). Because of the relationships between the tables, I'm
guessing
that
the problem is in my table design and not in the queries, form/subform
links
or row sources. The error message is: " You cannot add or change a
record
because a related record is required in table 'OtherOrg' "

My table design is:

tblBaseOrgMembers
MemberID (autonumber) (PK)
LastName
FirstName
City
Etc

tblOtherOrg
OtherOrgID (autonumber) (PK)
OtherOrgName
City
Etc

tblOtherOrgLeaders
OtherLdrID (autonumber) (PK)
OtherOrgID (FK)
LastName
FirstName
City
Etc

tblBaseOrg-Other [junction table with all three fields part of the
composite
foreign key, which is indexed]
MemberID (long integer, not required, not indexed)
OtherOrgID (", ", ")
OtherLdrID (", ", ")

The junction table relationships a
tbleBaseOrgMembers:MemberID - MemberID (1:M)
tblOtherOrg:OtherOrgID - :OtherOrgID (1:M)
tblOtherOrgLeaders:OtherLdrID - OtherLdrID (1:M)
with referential integrity enforced.

Also, there is a relationship between tlbOtherOrg and
tblOtherOrgLeaders
which uses OtherOrgID (1:M)

Data entry:
I've successfully loaded the OtherOrg and OtherOrgLeaders tables with
data
using a form/subform designed just for that purpose.

I have another form for BaseOrg data entry which also contains a combo
box
that selects the desired OtherOrg. That combo box is used to filter
the
universe of OtherLdr to just those possible leaders for that BaseOrg
member.

When I attempt to select one of the possible leaders on a subform (part
of
the BaseOrg form), I get the error message from Acess: " You cannot
add
or
change a record because a related record is required in table
'OtherOrg' "

Does anyone see a problem with this table design?

Thanks.

--
Al Williams

Allen,

I should have said that the members may work for only one organization at a
time, but there are several organizations that they might for. Hence, the
1:M between [tblOtherOrg] and [tblOtherOrgLeaders].

Al

"Al Williams" wrote:

Hi Allen,

Thank you very much for responding. I know that working your way through my
info took time and I appreciate it.

It has taken me some time to work through your suggestions because I've
taken a make one change and evaluate the results approach. I haven't gotten
it working yet but I think I can give a better description of what the
problem is. I also realize that I didn't I describe my table design
sufficiently.

There are three tables. The BaseOrg has all the members of the organization
I'm doing the database for. Some of those members work for another
organization (only one organization) which is described in [tblOtherOrg] but
they may have none, one, or many bosses [tblOtherLdr]. The junction table
[tblBaseOrg-Other] has a CFK that contains only foreign keys corresponding to
the three primary keys from the other three tables.

The form for [tblBaseOrg] also has a combo box based on a query the selects
[tblOtherOrg].[OtherOrgID] and [tblOtherOrg].[OtherOrgName]. The result is
bound to a field that I put into [tblBaseOrg] for that purpose.

The query for the subform that selects [tblOtherOrgLeaders] entries is based
on [tblBaseOrg-Other] and [tblOtherOrgLeaders].

That subform has a combo box for selection of the none (which is a N/A entry
which is already in [tblOtherOrgLeaders]), one, or many bosses. That combo
box is based on a query using only [tblOtherOrgLeaders] with
[tblOtherOrgLeaders].[OtherOrgID] having its criteria set to
[Forms]![BaseOrg]![OtherOrgID]. Both fields are a foreign key for
[tblOtherOrg].[OtherOrgID].

Here is what I've done:
1. As suggested, I deleted the field for [OtherOrgId]. You are correct, I
know in advance that there can be only one OtherOrg for a member.
2. I changed all my queries for the combo boxes and and subform to saved
queries (I read in one of the newsgroup postings that that sometimes helps
with update problems).
3. I've tried multiple iterations (all I can think of) including and
excluding fields in the queries - to no avail.
4. Finally, I deleted the 1:M relationship between [tblOtherOrg] and
[tblOtherOrgLeaders] to see what would happen. As a result, the combo box in
the subform does update BUT instead of using existing
[tblOtherOrgLeaders].[OtherOrgLdrID] entries in [tblOtherOrgLeaders] to
populate [tblBaseOrg-Other] the combo box selection is adding entries to
[tblOtherOrgLeaders]. That's why Access is complaining; there is no
simultaneous updates to create new records in [tblOtherOrg].

To try to resolve the problem, I removed all references to any [tblOtherOrg]
fields in the queries and also removed any references to the foreign key,
[tblOtherOrgLeaders].[OtherLdrID]. That didn't resolve it.

I'm now stuck because I don't have any more ideas. I did read your People
in Households and Companies article and think it is a very good. My database
is already deployed with seven people using it. So, if at all possible, I
need to try to figure out a way to live with my existing architecture.

Do you have suggestions? Thanks.

Al


"Allen Browne" wrote:

Hi Al

I think you have:
- a main form bound to [tblBaseOrdMembers], and
- a subform bound to a query based on [tblBaseOrg-Other].
The LinkMasterFields/LinkChild fields will therefore be [MemberID].

In the subform, you have 2 combos:
- one to choose the [OtherOrgId];
- one to choose the [OtherLdrId].
The subform will inherit the [MemberId] from the main form.

To get this working, remove the other tables from the query, so it only has
the 3 fields from [tblBaseOrg-Other].

Once you have that working, you can go back and add tblOtherOrg to the
query, presumably to get OtherOrgName into the query output grid. In query
design view, double-click the line joining the 2 tables. Access pops up a
dialog giving 3 options. Choose:
All records from tblBaseOrg-Other, and any matches from ...

Test the subform. If it fails at this point, open tblOtherOrg in design
view, and remove the Default Value for any fields. Also check that the text
boxes in the form for the fields from tblOtherOrg have not Default Value
set. (You also want to set the Locked property for these text boxes to Yes,
so the user cannot accidentally change them: these fields will be for
information only.)

Once you have that working, you can then open the query in design view, and
add tblOtherOrg to the query. Make it an outer join also, and make sure that
there is no Default Value for any fields in this table, nor for the text
boxes bound to this table.

You should now have it working as you wanted.


On a broader view, your structure is perhaps not ideal. In
tblOtherOrgLeaders, any leader can belong to only one "other org".
Therefore, choosing a value in the [OtherLdrId] combo means you already know
which [OtherOrgId] that leader belongs to. [tblBaseOrg-Other] therefore
should not have a field for [OtherOrgId]. In fact, having both fields in
[tblBaseOrg-Other] leaves the door open for bad data.


Beyond merely dropping the [OtherOrgId] field from [tblBaseOrg-Other], you
might consider a structure where there are just two tables:
- one for tblOrg (OrgId, OrgName, ...)
- one for tblPerson (PersonId, LastName, FirstName, ...)
Put your own organisation in tblOrganisation.
Put your own staff and everyone elses in tblPerson.
Now add a 3rd table that says who works for whom, under which boss:
tblOrgPerson:
OrgPersonId AutoNum p.k.
PersonID Number f.k. to tblPerson.PersonId. Required.
OrgId Number f.k. to tblOrg.OrgId. Required.
RoleId f.k. to tblRole.RoleId.
Optional
BossId Number f.k. to tblPerson.PersonId. Optional

This 3rd table lets you enter who works for which organisation, in what role
(secretary, manager, janitor, ...) and who they report to in that role at
that organisation. This simple structure lets a person work part-time under
different bosses (or the same boss) in different organisations (or the same
organisation), and it's incredibly simple to maintain. Perhaps that would
not achieve what you want, but it certainly seems like the most flexible
approach from what we know of your case.

HTH

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Al Williams" wrote in message
...
Allen,

1. Yes, the subform that I'm having problems with does use a query that
includes all three fields from the junction table's composite foreign key
and
all the fields from tblOtherOrgLeaders. I'm very new at many-to-many
relationships and followed the pattern of a successful many-to-many
implementation that had two M:M tables - but not three like I'm doing.

Since it may be that the composite foreign key in the junction table isn't
being updated properly, let me describe how I'm trying to do the update.

I believe that the first field in the junction table is defined at time of
update by the form/subform Master Field/Child Field linkage between the
BaseOrg and the OtherOrgLeaders subform. The third field is defined by
the
combo box selection in the OtherOrgLeaders subform. It's the second
field,
OtherOrgID that I'm not certain is being handled correctly. I'm assuming
that since the OtherOrg combo box on the BaseOrg form does cause the
proper
subset of OtherOrgLeaders to be available, that that information is
available
to the query for the row source. In that query, I'm referencing the
BaseOrg
combo box by using a Forms![..]![..] expression. So, to me everything
seems
to be there and that everything is in place for a simulataneous update of
all
three fields in the CFK - it just doesn't work.

Do you see a problem?

2. Your People in Households and Companies article is very interesting.
I'm
going to have to read it carefully. Thanks.

"Allen Browne" wrote:

Hi Al

Didn't actually read through all this carefully, but you can get that
error
message if your subform is based on a query, and one of the fields from
the
other table contains a Default Value. Access misinterprets the default
value
and complains that it can't add the record to the lookup table.

For another possible way to model your scenario, see:
People in households and companies
at:
http://allenbrowne.com/AppHuman.html


"Al Williams" wrote in message
...
Access 2003:
I have a database containing contact information and other attributes
for
members of an organization. Some of those members work for other
organizations. In some cases, they work for the same other
organization.

In those other organizations, they may have more than one boss. In
some
cases they have the same boss(es) in the other organization and in
other
cases they have unique boss(es).

The intent of the database design is to view/enter the contact
information
for each member and, if they work for another organization, to
view/enter
that organizations's address, etc and to select the member's boss(es)
in
that
organization.

I'm getting an error message from Access when I try to select the
member's
boss(es). Because of the relationships between the tables, I'm
guessing
that
the problem is in my table design and not in the queries, form/subform
links
or row sources. The error message is: " You cannot add or change a
record
because a related record is required in table 'OtherOrg' "

My table design is:

tblBaseOrgMembers
MemberID (autonumber) (PK)
LastName
FirstName
City
Etc

tblOtherOrg
OtherOrgID (autonumber) (PK)
OtherOrgName
City
Etc

tblOtherOrgLeaders
OtherLdrID (autonumber) (PK)
OtherOrgID (FK)
LastName
FirstName
City
Etc

tblBaseOrg-Other [junction table with all three fields part of the
composite
foreign key, which is indexed]
MemberID (long integer, not required, not indexed)
OtherOrgID (", ", ")
OtherLdrID (", ", ")

The junction table relationships a
tbleBaseOrgMembers:MemberID - MemberID (1:M)
tblOtherOrg:OtherOrgID - :OtherOrgID (1:M)
tblOtherOrgLeaders:OtherLdrID - OtherLdrID (1:M)
with referential integrity enforced.

Also, there is a relationship between tlbOtherOrg and
tblOtherOrgLeaders
which uses OtherOrgID (1:M)

Data entry:
I've successfully loaded the OtherOrg and OtherOrgLeaders tables with
data
using a form/subform designed just for that purpose.

I have another form for BaseOrg data entry which also contains a combo
box
that selects the desired OtherOrg. That combo box is used to filter
the
universe of OtherLdr to just those possible leaders for that BaseOrg
member.

When I attempt to select one of the possible leaders on a subform (part
of
the BaseOrg form), I get the error message from Acess: " You cannot
add
or
change a record because a related record is required in table
'OtherOrg' "

Does anyone see a problem with this table design?

Thanks.

--
Al Williams

The core of the issue seems to be under your #4:
4. Finally, I deleted the 1:M relationship between [tblOtherOrg]
and [tblOtherOrgLeaders] to see what would happen. As a result,
the combo box in the subform does update BUT instead of using
existing [tblOtherOrgLeaders].[OtherOrgLdrID] entries in
[tblOtherOrgLeaders] to populate [tblBaseOrg-Other] the combo
box selection is adding entries to [tblOtherOrgLeaders]. That's
why Access is complaining; there is no simultaneous updates to
create new records in [tblOtherOrg].

You are expecting Access to add new records to or perform updates on
tblOtherOrg as well? It is not going to do that.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Al Williams" wrote in message
...
Hi Allen,

Thank you very much for responding. I know that working your way through
my
info took time and I appreciate it.

It has taken me some time to work through your suggestions because I've
taken a make one change and evaluate the results approach. I haven't
gotten
it working yet but I think I can give a better description of what the
problem is. I also realize that I didn't I describe my table design
sufficiently.

There are three tables. The BaseOrg has all the members of the
organization
I'm doing the database for. Some of those members work for another
organization (only one organization) which is described in [tblOtherOrg]
but
they may have none, one, or many bosses [tblOtherLdr]. The junction table
[tblBaseOrg-Other] has a CFK that contains only foreign keys corresponding
to
the three primary keys from the other three tables.

The form for [tblBaseOrg] also has a combo box based on a query the
selects
[tblOtherOrg].[OtherOrgID] and [tblOtherOrg].[OtherOrgName]. The result
is
bound to a field that I put into [tblBaseOrg] for that purpose.

The query for the subform that selects [tblOtherOrgLeaders] entries is
based
on [tblBaseOrg-Other] and [tblOtherOrgLeaders].

That subform has a combo box for selection of the none (which is a N/A
entry
which is already in [tblOtherOrgLeaders]), one, or many bosses. That
combo
box is based on a query using only [tblOtherOrgLeaders] with
[tblOtherOrgLeaders].[OtherOrgID] having its criteria set to
[Forms]![BaseOrg]![OtherOrgID]. Both fields are a foreign key for
[tblOtherOrg].[OtherOrgID].

Here is what I've done:
1. As suggested, I deleted the field for [OtherOrgId]. You are correct, I
know in advance that there can be only one OtherOrg for a member.
2. I changed all my queries for the combo boxes and and subform to saved
queries (I read in one of the newsgroup postings that that sometimes helps
with update problems).
3. I've tried multiple iterations (all I can think of) including and
excluding fields in the queries - to no avail.
4. Finally, I deleted the 1:M relationship between [tblOtherOrg] and
[tblOtherOrgLeaders] to see what would happen. As a result, the combo box
in
the subform does update BUT instead of using existing
[tblOtherOrgLeaders].[OtherOrgLdrID] entries in [tblOtherOrgLeaders] to
populate [tblBaseOrg-Other] the combo box selection is adding entries to
[tblOtherOrgLeaders]. That's why Access is complaining; there is no
simultaneous updates to create new records in [tblOtherOrg].

To try to resolve the problem, I removed all references to any
[tblOtherOrg]
fields in the queries and also removed any references to the foreign key,
[tblOtherOrgLeaders].[OtherLdrID]. That didn't resolve it.

I'm now stuck because I don't have any more ideas. I did read your People
in Households and Companies article and think it is a very good. My
database
is already deployed with seven people using it. So, if at all possible, I
need to try to figure out a way to live with my existing architecture.

Do you have suggestions? Thanks.

Al


"Allen Browne" wrote:

Hi Al

I think you have:
- a main form bound to [tblBaseOrdMembers], and
- a subform bound to a query based on [tblBaseOrg-Other].
The LinkMasterFields/LinkChild fields will therefore be [MemberID].

In the subform, you have 2 combos:
- one to choose the [OtherOrgId];
- one to choose the [OtherLdrId].
The subform will inherit the [MemberId] from the main form.

To get this working, remove the other tables from the query, so it only
has
the 3 fields from [tblBaseOrg-Other].

Once you have that working, you can go back and add tblOtherOrg to the
query, presumably to get OtherOrgName into the query output grid. In
query
design view, double-click the line joining the 2 tables. Access pops up a
dialog giving 3 options. Choose:
All records from tblBaseOrg-Other, and any matches from ...

Test the subform. If it fails at this point, open tblOtherOrg in design
view, and remove the Default Value for any fields. Also check that the
text
boxes in the form for the fields from tblOtherOrg have not Default Value
set. (You also want to set the Locked property for these text boxes to
Yes,
so the user cannot accidentally change them: these fields will be for
information only.)

Once you have that working, you can then open the query in design view,
and
add tblOtherOrg to the query. Make it an outer join also, and make sure
that
there is no Default Value for any fields in this table, nor for the text
boxes bound to this table.

You should now have it working as you wanted.


On a broader view, your structure is perhaps not ideal. In
tblOtherOrgLeaders, any leader can belong to only one "other org".
Therefore, choosing a value in the [OtherLdrId] combo means you already
know
which [OtherOrgId] that leader belongs to. [tblBaseOrg-Other] therefore
should not have a field for [OtherOrgId]. In fact, having both fields in
[tblBaseOrg-Other] leaves the door open for bad data.


Beyond merely dropping the [OtherOrgId] field from [tblBaseOrg-Other],
you
might consider a structure where there are just two tables:
- one for tblOrg (OrgId, OrgName, ...)
- one for tblPerson (PersonId, LastName, FirstName, ...)
Put your own organisation in tblOrganisation.
Put your own staff and everyone elses in tblPerson.
Now add a 3rd table that says who works for whom, under which boss:
tblOrgPerson:
OrgPersonId AutoNum p.k.
PersonID Number f.k. to tblPerson.PersonId. Required.
OrgId Number f.k. to tblOrg.OrgId. Required.
RoleId f.k. to tblRole.RoleId.
Optional
BossId Number f.k. to tblPerson.PersonId. Optional

This 3rd table lets you enter who works for which organisation, in what
role
(secretary, manager, janitor, ...) and who they report to in that role at
that organisation. This simple structure lets a person work part-time
under
different bosses (or the same boss) in different organisations (or the
same
organisation), and it's incredibly simple to maintain. Perhaps that would
not achieve what you want, but it certainly seems like the most flexible
approach from what we know of your case.

HTH

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Al Williams" wrote in message
...
Allen,

1. Yes, the subform that I'm having problems with does use a query
that
includes all three fields from the junction table's composite foreign
key
and
all the fields from tblOtherOrgLeaders. I'm very new at many-to-many
relationships and followed the pattern of a successful many-to-many
implementation that had two M:M tables - but not three like I'm doing.

Since it may be that the composite foreign key in the junction table
isn't
being updated properly, let me describe how I'm trying to do the
update.

I believe that the first field in the junction table is defined at time
of
update by the form/subform Master Field/Child Field linkage between the
BaseOrg and the OtherOrgLeaders subform. The third field is defined by
the
combo box selection in the OtherOrgLeaders subform. It's the second
field,
OtherOrgID that I'm not certain is being handled correctly. I'm
assuming
that since the OtherOrg combo box on the BaseOrg form does cause the
proper
subset of OtherOrgLeaders to be available, that that information is
available
to the query for the row source. In that query, I'm referencing the
BaseOrg
combo box by using a Forms![..]![..] expression. So, to me everything
seems
to be there and that everything is in place for a simulataneous update
of
all
three fields in the CFK - it just doesn't work.

Do you see a problem?

2. Your People in Households and Companies article is very interesting.
I'm
going to have to read it carefully. Thanks.

"Allen Browne" wrote:

Hi Al

Didn't actually read through all this carefully, but you can get that
error
message if your subform is based on a query, and one of the fields
from
the
other table contains a Default Value. Access misinterprets the default
value
and complains that it can't add the record to the lookup table.

For another possible way to model your scenario, see:
People in households and companies
at:
http://allenbrowne.com/AppHuman.html


"Al Williams" wrote in message
...
Access 2003:
I have a database containing contact information and other
attributes
for
members of an organization. Some of those members work for other
organizations. In some cases, they work for the same other
organization.

In those other organizations, they may have more than one boss. In
some
cases they have the same boss(es) in the other organization and in
other
cases they have unique boss(es).

The intent of the database design is to view/enter the contact
information
for each member and, if they work for another organization, to
view/enter
that organizations's address, etc and to select the member's
boss(es)
in
that
organization.

I'm getting an error message from Access when I try to select the
member's
boss(es). Because of the relationships between the tables, I'm
guessing
that
the problem is in my table design and not in the queries,
form/subform
links
or row sources. The error message is: " You cannot add or change a
record
because a related record is required in table 'OtherOrg' "

My table design is:

tblBaseOrgMembers
MemberID (autonumber) (PK)
LastName
FirstName
City
Etc

tblOtherOrg
OtherOrgID (autonumber) (PK)
OtherOrgName
City
Etc

tblOtherOrgLeaders
OtherLdrID (autonumber) (PK)
OtherOrgID (FK)
LastName
FirstName
City
Etc

tblBaseOrg-Other [junction table with all three fields part of the
composite
foreign key, which is indexed]
MemberID (long integer, not required, not indexed)
OtherOrgID (", ", ")
OtherLdrID (", ", ")

The junction table relationships a
tbleBaseOrgMembers:MemberID - MemberID (1:M)
tblOtherOrg:OtherOrgID - :OtherOrgID (1:M)
tblOtherOrgLeaders:OtherLdrID - OtherLdrID (1:M)
with referential integrity enforced.

Also, there is a relationship between tlbOtherOrg and
tblOtherOrgLeaders
which uses OtherOrgID (1:M)

Data entry:
I've successfully loaded the OtherOrg and OtherOrgLeaders tables
with
data
using a form/subform designed just for that purpose.

I have another form for BaseOrg data entry which also contains a
combo
box
that selects the desired OtherOrg. That combo box is used to filter
the
universe of OtherLdr to just those possible leaders for that BaseOrg
member.

When I attempt to select one of the possible leaders on a subform
(part
of
the BaseOrg form), I get the error message from Acess: " You cannot
add
or
change a record because a related record is required in table
'OtherOrg' "

Does anyone see a problem with this table design?

Thanks.

--
Al Williams

Hi Allen,

No, I don't want Access to add new records to or perform updates on
tblOtherOrg. To the contrary, I wanted to take information from
tblOtherOrgLeaders and use that to update the junction table,
tblBaseOrg-Other, while also updating the junction table with information
from tblBaseOrg. That's what puzzling me. I don't understand why Access
thinks I'm trying to update tblOtherOrg.

Al


"Allen Browne" wrote:

The core of the issue seems to be under your #4:
4. Finally, I deleted the 1:M relationship between [tblOtherOrg]
and [tblOtherOrgLeaders] to see what would happen. As a result,
the combo box in the subform does update BUT instead of using
existing [tblOtherOrgLeaders].[OtherOrgLdrID] entries in
[tblOtherOrgLeaders] to populate [tblBaseOrg-Other] the combo
box selection is adding entries to [tblOtherOrgLeaders]. That's
why Access is complaining; there is no simultaneous updates to
create new records in [tblOtherOrg].

You are expecting Access to add new records to or perform updates on
tblOtherOrg as well? It is not going to do that.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Al Williams" wrote in message
...
Hi Allen,

Thank you very much for responding. I know that working your way through
my
info took time and I appreciate it.

It has taken me some time to work through your suggestions because I've
taken a make one change and evaluate the results approach. I haven't
gotten
it working yet but I think I can give a better description of what the
problem is. I also realize that I didn't I describe my table design
sufficiently.

There are three tables. The BaseOrg has all the members of the
organization
I'm doing the database for. Some of those members work for another
organization (only one organization) which is described in [tblOtherOrg]
but
they may have none, one, or many bosses [tblOtherLdr]. The junction table
[tblBaseOrg-Other] has a CFK that contains only foreign keys corresponding
to
the three primary keys from the other three tables.

The form for [tblBaseOrg] also has a combo box based on a query the
selects
[tblOtherOrg].[OtherOrgID] and [tblOtherOrg].[OtherOrgName]. The result
is
bound to a field that I put into [tblBaseOrg] for that purpose.

The query for the subform that selects [tblOtherOrgLeaders] entries is
based
on [tblBaseOrg-Other] and [tblOtherOrgLeaders].

That subform has a combo box for selection of the none (which is a N/A
entry
which is already in [tblOtherOrgLeaders]), one, or many bosses. That
combo
box is based on a query using only [tblOtherOrgLeaders] with
[tblOtherOrgLeaders].[OtherOrgID] having its criteria set to
[Forms]![BaseOrg]![OtherOrgID]. Both fields are a foreign key for
[tblOtherOrg].[OtherOrgID].

Here is what I've done:
1. As suggested, I deleted the field for [OtherOrgId]. You are correct, I
know in advance that there can be only one OtherOrg for a member.
2. I changed all my queries for the combo boxes and and subform to saved
queries (I read in one of the newsgroup postings that that sometimes helps
with update problems).
3. I've tried multiple iterations (all I can think of) including and
excluding fields in the queries - to no avail.
4. Finally, I deleted the 1:M relationship between [tblOtherOrg] and
[tblOtherOrgLeaders] to see what would happen. As a result, the combo box
in
the subform does update BUT instead of using existing
[tblOtherOrgLeaders].[OtherOrgLdrID] entries in [tblOtherOrgLeaders] to
populate [tblBaseOrg-Other] the combo box selection is adding entries to
[tblOtherOrgLeaders]. That's why Access is complaining; there is no
simultaneous updates to create new records in [tblOtherOrg].

To try to resolve the problem, I removed all references to any
[tblOtherOrg]
fields in the queries and also removed any references to the foreign key,
[tblOtherOrgLeaders].[OtherLdrID]. That didn't resolve it.

I'm now stuck because I don't have any more ideas. I did read your People
in Households and Companies article and think it is a very good. My
database
is already deployed with seven people using it. So, if at all possible, I
need to try to figure out a way to live with my existing architecture.

Do you have suggestions? Thanks.

Al


"Allen Browne" wrote:

Hi Al

I think you have:
- a main form bound to [tblBaseOrdMembers], and
- a subform bound to a query based on [tblBaseOrg-Other].
The LinkMasterFields/LinkChild fields will therefore be [MemberID].

In the subform, you have 2 combos:
- one to choose the [OtherOrgId];
- one to choose the [OtherLdrId].
The subform will inherit the [MemberId] from the main form.

To get this working, remove the other tables from the query, so it only
has
the 3 fields from [tblBaseOrg-Other].

Once you have that working, you can go back and add tblOtherOrg to the
query, presumably to get OtherOrgName into the query output grid. In
query
design view, double-click the line joining the 2 tables. Access pops up a
dialog giving 3 options. Choose:
All records from tblBaseOrg-Other, and any matches from ...

Test the subform. If it fails at this point, open tblOtherOrg in design
view, and remove the Default Value for any fields. Also check that the
text
boxes in the form for the fields from tblOtherOrg have not Default Value
set. (You also want to set the Locked property for these text boxes to
Yes,
so the user cannot accidentally change them: these fields will be for
information only.)

Once you have that working, you can then open the query in design view,
and
add tblOtherOrg to the query. Make it an outer join also, and make sure
that
there is no Default Value for any fields in this table, nor for the text
boxes bound to this table.

You should now have it working as you wanted.


On a broader view, your structure is perhaps not ideal. In
tblOtherOrgLeaders, any leader can belong to only one "other org".
Therefore, choosing a value in the [OtherLdrId] combo means you already
know
which [OtherOrgId] that leader belongs to. [tblBaseOrg-Other] therefore
should not have a field for [OtherOrgId]. In fact, having both fields in
[tblBaseOrg-Other] leaves the door open for bad data.


Beyond merely dropping the [OtherOrgId] field from [tblBaseOrg-Other],
you
might consider a structure where there are just two tables:
- one for tblOrg (OrgId, OrgName, ...)
- one for tblPerson (PersonId, LastName, FirstName, ...)
Put your own organisation in tblOrganisation.
Put your own staff and everyone elses in tblPerson.
Now add a 3rd table that says who works for whom, under which boss:
tblOrgPerson:
OrgPersonId AutoNum p.k.
PersonID Number f.k. to tblPerson.PersonId. Required.
OrgId Number f.k. to tblOrg.OrgId. Required.
RoleId f.k. to tblRole.RoleId.
Optional
BossId Number f.k. to tblPerson.PersonId. Optional

This 3rd table lets you enter who works for which organisation, in what
role
(secretary, manager, janitor, ...) and who they report to in that role at
that organisation. This simple structure lets a person work part-time
under
different bosses (or the same boss) in different organisations (or the
same
organisation), and it's incredibly simple to maintain. Perhaps that would
not achieve what you want, but it certainly seems like the most flexible
approach from what we know of your case.

HTH

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Al Williams" wrote in message
...
Allen,

1. Yes, the subform that I'm having problems with does use a query
that
includes all three fields from the junction table's composite foreign
key
and
all the fields from tblOtherOrgLeaders. I'm very new at many-to-many
relationships and followed the pattern of a successful many-to-many
implementation that had two M:M tables - but not three like I'm doing.

Since it may be that the composite foreign key in the junction table
isn't
being updated properly, let me describe how I'm trying to do the
update.

I believe that the first field in the junction table is defined at time
of
update by the form/subform Master Field/Child Field linkage between the
BaseOrg and the OtherOrgLeaders subform. The third field is defined by
the
combo box selection in the OtherOrgLeaders subform. It's the second
field,
OtherOrgID that I'm not certain is being handled correctly. I'm
assuming
that since the OtherOrg combo box on the BaseOrg form does cause the
proper
subset of OtherOrgLeaders to be available, that that information is
available
to the query for the row source. In that query, I'm referencing the
BaseOrg
combo box by using a Forms![..]![..] expression. So, to me everything
seems
to be there and that everything is in place for a simulataneous update
of
all
three fields in the CFK - it just doesn't work.

Do you see a problem?

2. Your People in Households and Companies article is very interesting.
I'm
going to have to read it carefully. Thanks.

"Allen Browne" wrote:

Hi Al

Didn't actually read through all this carefully, but you can get that
error
message if your subform is based on a query, and one of the fields
from
the
other table contains a Default Value. Access misinterprets the default
value
and complains that it can't add the record to the lookup table.

For another possible way to model your scenario, see:
People in households and companies
at:
http://allenbrowne.com/AppHuman.html


"Al Williams" wrote in message
...
Access 2003:
I have a database containing contact information and other
attributes
for
members of an organization. Some of those members work for other
organizations. In some cases, they work for the same other
organization.

In those other organizations, they may have more than one boss. In
some
cases they have the same boss(es) in the other organization and in
other
cases they have unique boss(es).

The intent of the database design is to view/enter the contact
information
for each member and, if they work for another organization, to
view/enter
that organizations's address, etc and to select the member's
boss(es)
in
that
organization.

I'm getting an error message from Access when I try to select the
member's
boss(es). Because of the relationships between the tables, I'm
guessing
that
the problem is in my table design and not in the queries,
form/subform
links
or row sources. The error message is: " You cannot add or change a
record
because a related record is required in table 'OtherOrg' "

My table design is:

tblBaseOrgMembers
MemberID (autonumber) (PK)
LastName
FirstName
City
Etc

tblOtherOrg
OtherOrgID (autonumber) (PK)
OtherOrgName
City
Etc

tblOtherOrgLeaders
OtherLdrID (autonumber) (PK)
OtherOrgID (FK)
LastName
FirstName
City
Etc

tblBaseOrg-Other [junction table with all three fields part of the
composite
foreign key, which is indexed]
MemberID (long integer, not required, not indexed)
OtherOrgID (", ", ")
OtherLdrID (", ", ")

The junction table relationships a
tbleBaseOrgMembers:MemberID - MemberID (1:M)
tblOtherOrg:OtherOrgID - :OtherOrgID (1:M)
tblOtherOrgLeaders:OtherLdrID - OtherLdrID (1:M)
with referential integrity enforced.

Also, there is a relationship between tlbOtherOrg and
tblOtherOrgLeaders
which uses OtherOrgID (1:M)

Data entry:
I've successfully loaded the OtherOrg and OtherOrgLeaders tables
with
data
using a form/subform designed just for that purpose.

I have another form for BaseOrg data entry which also contains a
combo
box
that selects the desired OtherOrg. That combo box is used to filter
the
universe of OtherLdr to just those possible leaders for that BaseOrg
member.

When I attempt to select one of the possible leaders on a subform
(part
of
the BaseOrg form), I get the error message from Acess: " You cannot
add
or
change a record because a related record is required in table
'OtherOrg' "

Does anyone see a problem with this table design?

Thanks.

--
Al Williams

If Access thinks you are trying to update tblOtherOrg, the only things I can
think of a
a) Make sure the field from the correct table is in the query, and
b) Make sure none of the fields in tblOtherOrg have a Default value set.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Al Williams" wrote in message
...
Hi Allen,

No, I don't want Access to add new records to or perform updates on
tblOtherOrg. To the contrary, I wanted to take information from
tblOtherOrgLeaders and use that to update the junction table,
tblBaseOrg-Other, while also updating the junction table with information
from tblBaseOrg. That's what puzzling me. I don't understand why Access
thinks I'm trying to update tblOtherOrg.

Al


"Allen Browne" wrote:

The core of the issue seems to be under your #4:
4. Finally, I deleted the 1:M relationship between [tblOtherOrg]
and [tblOtherOrgLeaders] to see what would happen. As a result,
the combo box in the subform does update BUT instead of using
existing [tblOtherOrgLeaders].[OtherOrgLdrID] entries in
[tblOtherOrgLeaders] to populate [tblBaseOrg-Other] the combo
box selection is adding entries to [tblOtherOrgLeaders]. That's
why Access is complaining; there is no simultaneous updates to
create new records in [tblOtherOrg].

You are expecting Access to add new records to or perform updates on
tblOtherOrg as well? It is not going to do that.

"Al Williams" wrote in message
...
Hi Allen,

Thank you very much for responding. I know that working your way
through
my
info took time and I appreciate it.

It has taken me some time to work through your suggestions because I've
taken a make one change and evaluate the results approach. I haven't
gotten
it working yet but I think I can give a better description of what the
problem is. I also realize that I didn't I describe my table design
sufficiently.

There are three tables. The BaseOrg has all the members of the
organization
I'm doing the database for. Some of those members work for another
organization (only one organization) which is described in
[tblOtherOrg]
but
they may have none, one, or many bosses [tblOtherLdr]. The junction
table
[tblBaseOrg-Other] has a CFK that contains only foreign keys
corresponding
to
the three primary keys from the other three tables.

The form for [tblBaseOrg] also has a combo box based on a query the
selects
[tblOtherOrg].[OtherOrgID] and [tblOtherOrg].[OtherOrgName]. The
result
is
bound to a field that I put into [tblBaseOrg] for that purpose.

The query for the subform that selects [tblOtherOrgLeaders] entries is
based
on [tblBaseOrg-Other] and [tblOtherOrgLeaders].

That subform has a combo box for selection of the none (which is a N/A
entry
which is already in [tblOtherOrgLeaders]), one, or many bosses. That
combo
box is based on a query using only [tblOtherOrgLeaders] with
[tblOtherOrgLeaders].[OtherOrgID] having its criteria set to
[Forms]![BaseOrg]![OtherOrgID]. Both fields are a foreign key for
[tblOtherOrg].[OtherOrgID].

Here is what I've done:
1. As suggested, I deleted the field for [OtherOrgId]. You are
correct, I
know in advance that there can be only one OtherOrg for a member.
2. I changed all my queries for the combo boxes and and subform to
saved
queries (I read in one of the newsgroup postings that that sometimes
helps
with update problems).
3. I've tried multiple iterations (all I can think of) including and
excluding fields in the queries - to no avail.
4. Finally, I deleted the 1:M relationship between [tblOtherOrg] and
[tblOtherOrgLeaders] to see what would happen. As a result, the combo
box
in
the subform does update BUT instead of using existing
[tblOtherOrgLeaders].[OtherOrgLdrID] entries in [tblOtherOrgLeaders] to
populate [tblBaseOrg-Other] the combo box selection is adding entries
to
[tblOtherOrgLeaders]. That's why Access is complaining; there is no
simultaneous updates to create new records in [tblOtherOrg].

To try to resolve the problem, I removed all references to any
[tblOtherOrg]
fields in the queries and also removed any references to the foreign
key,
[tblOtherOrgLeaders].[OtherLdrID]. That didn't resolve it.

I'm now stuck because I don't have any more ideas. I did read your
People
in Households and Companies article and think it is a very good. My
database
is already deployed with seven people using it. So, if at all
possible, I
need to try to figure out a way to live with my existing architecture.

Do you have suggestions? Thanks.

Al


"Allen Browne" wrote:

Hi Al

I think you have:
- a main form bound to [tblBaseOrdMembers], and
- a subform bound to a query based on [tblBaseOrg-Other].
The LinkMasterFields/LinkChild fields will therefore be [MemberID].

In the subform, you have 2 combos:
- one to choose the [OtherOrgId];
- one to choose the [OtherLdrId].
The subform will inherit the [MemberId] from the main form.

To get this working, remove the other tables from the query, so it
only
has
the 3 fields from [tblBaseOrg-Other].

Once you have that working, you can go back and add tblOtherOrg to the
query, presumably to get OtherOrgName into the query output grid. In
query
design view, double-click the line joining the 2 tables. Access pops
up a
dialog giving 3 options. Choose:
All records from tblBaseOrg-Other, and any matches from ...

Test the subform. If it fails at this point, open tblOtherOrg in
design
view, and remove the Default Value for any fields. Also check that the
text
boxes in the form for the fields from tblOtherOrg have not Default
Value
set. (You also want to set the Locked property for these text boxes to
Yes,
so the user cannot accidentally change them: these fields will be for
information only.)

Once you have that working, you can then open the query in design
view,
and
add tblOtherOrg to the query. Make it an outer join also, and make
sure
that
there is no Default Value for any fields in this table, nor for the
text
boxes bound to this table.

You should now have it working as you wanted.


On a broader view, your structure is perhaps not ideal. In
tblOtherOrgLeaders, any leader can belong to only one "other org".
Therefore, choosing a value in the [OtherLdrId] combo means you
already
know
which [OtherOrgId] that leader belongs to. [tblBaseOrg-Other]
therefore
should not have a field for [OtherOrgId]. In fact, having both fields
in
[tblBaseOrg-Other] leaves the door open for bad data.


Beyond merely dropping the [OtherOrgId] field from [tblBaseOrg-Other],
you
might consider a structure where there are just two tables:
- one for tblOrg (OrgId, OrgName, ...)
- one for tblPerson (PersonId, LastName, FirstName, ...)
Put your own organisation in tblOrganisation.
Put your own staff and everyone elses in tblPerson.
Now add a 3rd table that says who works for whom, under which boss:
tblOrgPerson:
OrgPersonId AutoNum p.k.
PersonID Number f.k. to tblPerson.PersonId. Required.
OrgId Number f.k. to tblOrg.OrgId.
Required.
RoleId f.k. to tblRole.RoleId.
Optional
BossId Number f.k. to tblPerson.PersonId.
Optional

This 3rd table lets you enter who works for which organisation, in
what
role
(secretary, manager, janitor, ...) and who they report to in that role
at
that organisation. This simple structure lets a person work part-time
under
different bosses (or the same boss) in different organisations (or the
same
organisation), and it's incredibly simple to maintain. Perhaps that
would
not achieve what you want, but it certainly seems like the most
flexible
approach from what we know of your case.

HTH

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Al Williams" wrote in message
...
Allen,

1. Yes, the subform that I'm having problems with does use a query
that
includes all three fields from the junction table's composite
foreign
key
and
all the fields from tblOtherOrgLeaders. I'm very new at
many-to-many
relationships and followed the pattern of a successful many-to-many
implementation that had two M:M tables - but not three like I'm
doing.

Since it may be that the composite foreign key in the junction table
isn't
being updated properly, let me describe how I'm trying to do the
update.

I believe that the first field in the junction table is defined at
time
of
update by the form/subform Master Field/Child Field linkage between
the
BaseOrg and the OtherOrgLeaders subform. The third field is defined
by
the
combo box selection in the OtherOrgLeaders subform. It's the second
field,
OtherOrgID that I'm not certain is being handled correctly. I'm
assuming
that since the OtherOrg combo box on the BaseOrg form does cause the
proper
subset of OtherOrgLeaders to be available, that that information is
available
to the query for the row source. In that query, I'm referencing the
BaseOrg
combo box by using a Forms![..]![..] expression. So, to me
everything
seems
to be there and that everything is in place for a simulataneous
update
of
all
three fields in the CFK - it just doesn't work.

Do you see a problem?

2. Your People in Households and Companies article is very
interesting.
I'm
going to have to read it carefully. Thanks.

"Allen Browne" wrote:

Hi Al

Didn't actually read through all this carefully, but you can get
that
error
message if your subform is based on a query, and one of the fields
from
the
other table contains a Default Value. Access misinterprets the
default
value
and complains that it can't add the record to the lookup table.

For another possible way to model your scenario, see:
People in households and companies
at:
http://allenbrowne.com/AppHuman.html


"Al Williams" wrote in
message
...
Access 2003:
I have a database containing contact information and other
attributes
for
members of an organization. Some of those members work for other
organizations. In some cases, they work for the same other
organization.

In those other organizations, they may have more than one boss.
In
some
cases they have the same boss(es) in the other organization and
in
other
cases they have unique boss(es).

The intent of the database design is to view/enter the contact
information
for each member and, if they work for another organization, to
view/enter
that organizations's address, etc and to select the member's
boss(es)
in
that
organization.

I'm getting an error message from Access when I try to select the
member's
boss(es). Because of the relationships between the tables, I'm
guessing
that
the problem is in my table design and not in the queries,
form/subform
links
or row sources. The error message is: " You cannot add or
change a
record
because a related record is required in table 'OtherOrg' "

My table design is:

tblBaseOrgMembers
MemberID (autonumber) (PK)
LastName
FirstName
City
Etc

tblOtherOrg
OtherOrgID (autonumber) (PK)
OtherOrgName
City
Etc

tblOtherOrgLeaders
OtherLdrID (autonumber) (PK)
OtherOrgID (FK)
LastName
FirstName
City
Etc

tblBaseOrg-Other [junction table with all three fields part of
the
composite
foreign key, which is indexed]
MemberID (long integer, not required, not indexed)
OtherOrgID (", ", ")
OtherLdrID (", ", ")

The junction table relationships a
tbleBaseOrgMembers:MemberID - MemberID (1:M)
tblOtherOrg:OtherOrgID - :OtherOrgID (1:M)
tblOtherOrgLeaders:OtherLdrID - OtherLdrID (1:M)
with referential integrity enforced.

Also, there is a relationship between tlbOtherOrg and
tblOtherOrgLeaders
which uses OtherOrgID (1:M)

Data entry:
I've successfully loaded the OtherOrg and OtherOrgLeaders tables
with
data
using a form/subform designed just for that purpose.

I have another form for BaseOrg data entry which also contains a
combo
box
that selects the desired OtherOrg. That combo box is used to
filter
the
universe of OtherLdr to just those possible leaders for that
BaseOrg
member.

When I attempt to select one of the possible leaders on a subform
(part
of
the BaseOrg form), I get the error message from Acess: " You
cannot
add
or
change a record because a related record is required in table
'OtherOrg' "

Does anyone see a problem with this table design?

Thanks.

--
Al Williams

Allen,

a. Except for one, each query is based on only one table. They a
qrycboOtherOrg which is used for the combo box on the BaseOrg form. That
query is based on based on tblOtherOrg and returns fields OtherOrgID and
OtherOrgName. It returns the proper information.

The exception, the query used for the subform, is qryBaseOrgtoOtherOrgLdrs
which contains both FK fields from the junction table, tblBaseOrg-Others, and
attribute fields from tblOtherOrgLeaders. It does not contain the PK for
tblOtherOrgLeaders nor the FK, OtherOrgID.

The query used on the subform for selection of OtherOrg Leaders is
qryRowSrceOtherOrgLeaders which is based on tblOtherOrgLeaders and contains
the fields: LeadershipID, LastName, FirstName, and OtherOrgID. It does use
the FK, OtherOrgID, with the criteria set to [Forms]![BaseOrg]![OtherOrgID]
to filter the universe of OtherOrgLeaders down to matches for OtherOrg as
selected by the combo box on the BaseOrg form. When the combo box on the
subform is clicked, the proper subset of the universe of OtherLeaders
displays.

There may be a problem in there but I don't see it.

b. Both tblOtherOrg and tblOtherOrgLeaders had a Date/Time field labeled
DateUpdated with a default value of =Date(). I deleted the default value but
continued to get the error message that a related record for tblOtherOrg was
required.

I also noticed that in tblOtherOrgLeaders, I had set the default value for
the FK OtherOrgID to 0. When I deleted it, I got an error message that a
null value was not allowed. That must have been why I put the default value
in. When I changed the required property for OtherOrgID from Yes to No, I
could select the proper subset of Leaders from OtherOrgLeaders but
tblOtherOrgLeaders had additional new records recording my selections which
means that my problem is still there.

Al

"Allen Browne" wrote:

If Access thinks you are trying to update tblOtherOrg, the only things I can
think of a
a) Make sure the field from the correct table is in the query, and
b) Make sure none of the fields in tblOtherOrg have a Default value set.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Al Williams" wrote in message
...
Hi Allen,

No, I don't want Access to add new records to or perform updates on
tblOtherOrg. To the contrary, I wanted to take information from
tblOtherOrgLeaders and use that to update the junction table,
tblBaseOrg-Other, while also updating the junction table with information
from tblBaseOrg. That's what puzzling me. I don't understand why Access
thinks I'm trying to update tblOtherOrg.

Al


"Allen Browne" wrote:

The core of the issue seems to be under your #4:
4. Finally, I deleted the 1:M relationship between [tblOtherOrg]
and [tblOtherOrgLeaders] to see what would happen. As a result,
the combo box in the subform does update BUT instead of using
existing [tblOtherOrgLeaders].[OtherOrgLdrID] entries in
[tblOtherOrgLeaders] to populate [tblBaseOrg-Other] the combo
box selection is adding entries to [tblOtherOrgLeaders]. That's
why Access is complaining; there is no simultaneous updates to
create new records in [tblOtherOrg].

You are expecting Access to add new records to or perform updates on
tblOtherOrg as well? It is not going to do that.

"Al Williams" wrote in message
...
Hi Allen,

Thank you very much for responding. I know that working your way
through
my
info took time and I appreciate it.

It has taken me some time to work through your suggestions because I've
taken a make one change and evaluate the results approach. I haven't
gotten
it working yet but I think I can give a better description of what the
problem is. I also realize that I didn't I describe my table design
sufficiently.

There are three tables. The BaseOrg has all the members of the
organization
I'm doing the database for. Some of those members work for another
organization (only one organization) which is described in
[tblOtherOrg]
but
they may have none, one, or many bosses [tblOtherLdr]. The junction
table
[tblBaseOrg-Other] has a CFK that contains only foreign keys
corresponding
to
the three primary keys from the other three tables.

The form for [tblBaseOrg] also has a combo box based on a query the
selects
[tblOtherOrg].[OtherOrgID] and [tblOtherOrg].[OtherOrgName]. The
result
is
bound to a field that I put into [tblBaseOrg] for that purpose.

The query for the subform that selects [tblOtherOrgLeaders] entries is
based
on [tblBaseOrg-Other] and [tblOtherOrgLeaders].

That subform has a combo box for selection of the none (which is a N/A
entry
which is already in [tblOtherOrgLeaders]), one, or many bosses. That
combo
box is based on a query using only [tblOtherOrgLeaders] with
[tblOtherOrgLeaders].[OtherOrgID] having its criteria set to
[Forms]![BaseOrg]![OtherOrgID]. Both fields are a foreign key for
[tblOtherOrg].[OtherOrgID].

Here is what I've done:
1. As suggested, I deleted the field for [OtherOrgId]. You are
correct, I
know in advance that there can be only one OtherOrg for a member.
2. I changed all my queries for the combo boxes and and subform to
saved
queries (I read in one of the newsgroup postings that that sometimes
helps
with update problems).
3. I've tried multiple iterations (all I can think of) including and
excluding fields in the queries - to no avail.
4. Finally, I deleted the 1:M relationship between [tblOtherOrg] and
[tblOtherOrgLeaders] to see what would happen. As a result, the combo
box
in
the subform does update BUT instead of using existing
[tblOtherOrgLeaders].[OtherOrgLdrID] entries in [tblOtherOrgLeaders] to
populate [tblBaseOrg-Other] the combo box selection is adding entries
to
[tblOtherOrgLeaders]. That's why Access is complaining; there is no
simultaneous updates to create new records in [tblOtherOrg].

To try to resolve the problem, I removed all references to any
[tblOtherOrg]
fields in the queries and also removed any references to the foreign
key,
[tblOtherOrgLeaders].[OtherLdrID]. That didn't resolve it.

I'm now stuck because I don't have any more ideas. I did read your
People
in Households and Companies article and think it is a very good. My
database
is already deployed with seven people using it. So, if at all
possible, I
need to try to figure out a way to live with my existing architecture.

Do you have suggestions? Thanks.

Al


"Allen Browne" wrote:

Hi Al

I think you have:
- a main form bound to [tblBaseOrdMembers], and
- a subform bound to a query based on [tblBaseOrg-Other].
The LinkMasterFields/LinkChild fields will therefore be [MemberID].

In the subform, you have 2 combos:
- one to choose the [OtherOrgId];
- one to choose the [OtherLdrId].
The subform will inherit the [MemberId] from the main form.

To get this working, remove the other tables from the query, so it
only
has
the 3 fields from [tblBaseOrg-Other].

Once you have that working, you can go back and add tblOtherOrg to the
query, presumably to get OtherOrgName into the query output grid. In
query
design view, double-click the line joining the 2 tables. Access pops
up a
dialog giving 3 options. Choose:
All records from tblBaseOrg-Other, and any matches from ...

Test the subform. If it fails at this point, open tblOtherOrg in
design
view, and remove the Default Value for any fields. Also check that the
text
boxes in the form for the fields from tblOtherOrg have not Default
Value
set. (You also want to set the Locked property for these text boxes to
Yes,
so the user cannot accidentally change them: these fields will be for
information only.)

Once you have that working, you can then open the query in design
view,
and
add tblOtherOrg to the query. Make it an outer join also, and make
sure
that
there is no Default Value for any fields in this table, nor for the
text
boxes bound to this table.

You should now have it working as you wanted.


On a broader view, your structure is perhaps not ideal. In
tblOtherOrgLeaders, any leader can belong to only one "other org".
Therefore, choosing a value in the [OtherLdrId] combo means you
already
know
which [OtherOrgId] that leader belongs to. [tblBaseOrg-Other]
therefore
should not have a field for [OtherOrgId]. In fact, having both fields
in
[tblBaseOrg-Other] leaves the door open for bad data.


Beyond merely dropping the [OtherOrgId] field from [tblBaseOrg-Other],
you
might consider a structure where there are just two tables:
- one for tblOrg (OrgId, OrgName, ...)
- one for tblPerson (PersonId, LastName, FirstName, ...)
Put your own organisation in tblOrganisation.
Put your own staff and everyone elses in tblPerson.
Now add a 3rd table that says who works for whom, under which boss:
tblOrgPerson:
OrgPersonId AutoNum p.k.
PersonID Number f.k. to tblPerson.PersonId. Required.
OrgId Number f.k. to tblOrg.OrgId.
Required.
RoleId f.k. to tblRole.RoleId.
Optional
BossId Number f.k. to tblPerson.PersonId.
Optional

This 3rd table lets you enter who works for which organisation, in
what
role
(secretary, manager, janitor, ...) and who they report to in that role
at
that organisation. This simple structure lets a person work part-time
under
different bosses (or the same boss) in different organisations (or the
same
organisation), and it's incredibly simple to maintain. Perhaps that
would
not achieve what you want, but it certainly seems like the most
flexible
approach from what we know of your case.

HTH

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Al Williams" wrote in message
...
Allen,

1. Yes, the subform that I'm having problems with does use a query
that
includes all three fields from the junction table's composite
foreign
key
and
all the fields from tblOtherOrgLeaders. I'm very new at
many-to-many
relationships and followed the pattern of a successful many-to-many
implementation that had two M:M tables - but not three like I'm
doing.

Since it may be that the composite foreign key in the junction table
isn't
being updated properly, let me describe how I'm trying to do the
update.

I believe that the first field in the junction table is defined at
time
of
update by the form/subform Master Field/Child Field linkage between
the
BaseOrg and the OtherOrgLeaders subform. The third field is defined
by
the
combo box selection in the OtherOrgLeaders subform. It's the second
field,
OtherOrgID that I'm not certain is being handled correctly. I'm
assuming
that since the OtherOrg combo box on the BaseOrg form does cause the
proper
subset of OtherOrgLeaders to be available, that that information is
available
to the query for the row source. In that query, I'm referencing the
BaseOrg
combo box by using a Forms![..]![..] expression. So, to me
everything
seems
to be there and that everything is in place for a simulataneous
update
of
all
three fields in the CFK - it just doesn't work.

Do you see a problem?

2. Your People in Households and Companies article is very
interesting.
I'm
going to have to read it carefully. Thanks.

"Allen Browne" wrote:

Hi Al

Didn't actually read through all this carefully, but you can get
that
error
message if your subform is based on a query, and one of the fields
from
the
other table contains a Default Value. Access misinterprets the
default
value
and complains that it can't add the record to the lookup table.

For another possible way to model your scenario, see:
People in households and companies
at:
http://allenbrowne.com/AppHuman.html


"Al Williams" wrote in
message
...
Access 2003:
I have a database containing contact information and other
attributes
for
members of an organization. Some of those members work for other
organizations. In some cases, they work for the same other
organization.

In those other organizations, they may have more than one boss.
In
some
cases they have the same boss(es) in the other organization and
in
other
cases they have unique boss(es).

The intent of the database design is to view/enter the contact
information
for each member and, if they work for another organization, to
view/enter
that organizations's address, etc and to select the member's
boss(es)
in
that
organization.

I'm getting an error message from Access when I try to select the
member's
boss(es). Because of the relationships between the tables, I'm
guessing
that
the problem is in my table design and not in the queries,
form/subform
links
or row sources. The error message is: " You cannot add or
change a
record
because a related record is required in table 'OtherOrg' "

My table design is:

tblBaseOrgMembers
MemberID (autonumber) (PK)
LastName
FirstName
City
Etc

tblOtherOrg
OtherOrgID (autonumber) (PK)
OtherOrgName
City
Etc

tblOtherOrgLeaders
OtherLdrID (autonumber) (PK)
OtherOrgID (FK)
LastName
FirstName
City
Etc

tblBaseOrg-Other [junction table with all three fields part of
the
composite
foreign key, which is indexed]
MemberID (long integer, not required, not indexed)
OtherOrgID (", ", ")
OtherLdrID (", ", ")

The junction table relationships a
tbleBaseOrgMembers:MemberID - MemberID (1:M)
tblOtherOrg:OtherOrgID - :OtherOrgID (1:M)
tblOtherOrgLeaders:OtherLdrID - OtherLdrID (1:M)
with referential integrity enforced.

Also, there is a relationship between tlbOtherOrg and
tblOtherOrgLeaders
which uses OtherOrgID (1:M)

Data entry:
I've successfully loaded the OtherOrg and OtherOrgLeaders tables
with
data
using a form/subform designed just for that purpose.

I have another form for BaseOrg data entry which also contains a
combo
box
that selects the desired OtherOrg. That combo box is used to
filter
the
universe of OtherLdr to just those possible leaders for that
BaseOrg
member.

When I attempt to select one of the possible leaders on a subform
(part
of
the BaseOrg form), I get the error message from Acess: " You
cannot
add
or
change a record because a related record is required in table
'OtherOrg' "

Does anyone see a problem with this table design?

Thanks.

--
Al Williams