Allen,
a. Except for one, each query is based on only one table. They are:
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
:
If Access thinks you are trying to update tblOtherOrg, the only things
I
can
think of are:
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.
Reply to group, rather than allenbrowne at mvps dot org.
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
:
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.
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
:
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.
Reply to group, rather than allenbrowne at mvps dot org.
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.
:
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
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 are:
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.
