LinkMaster>LinkChild problem

[Amy Blankenship]

Hi, all;

Note: This is a repost of an earlier post that did not achieve resolution.

I have a rather complex form with a subform that has a tabset in it. Each
tab has a different form relating to the parent subform. And each of those
subforms has more subforms doing different things. In one of the tabs on
the tabset, its subform has a form that has problems with its
LinkMaster>LinkChild relationship. For reference:

Form
-Subform 1
-Tabset
-Subform 2
-Subform 3

In Subform 3 there seems to be a problem with the LinkMaster>Child
relationship. At first, when I added content, I got a message that either
the content was too long, the form was read only, or something else I don't
recall at the moment. When I looked in the related tables, the record was
created in the child table, but its ID had not been inserted in the parent
table (the record source for Subform 2).

So I tried to repair the LinkMaster/LinkChild relationship. Clicking on the
.... next to the LinkMaster gave me a dialogue called "Subform Field Linker"
with the message "Object variable or With block variable not defined." I
tried reinserting the form and manually setting up the Master/Child
relationship and recreating Subform 2 from scratch. In the recreated
version, if you change to a new, blank record in Subform 2 after viewing a
record with a linked record in Subform 3, the blank record will still show
the record in Subform 3 even though no data has ever been created for that
new record in Subform 2 (yes, it's a 1:1 relationship, sort of).

I have verified that the LinkMaster ... button works on other subforms in
the same main form. I have also verified that the relationship is set up
properly between the tables on Subform 2 and Subform 3.

If you look at the values in the LinkMaster and LinkChild fields in the form
properties, they appear to be correct.

Update:

I found that somehow the form had managed to create a record in the table
used as the data source for "Subform 3" with a PK of 0 in a field of type
Autonumber. This explains why new records displayed that text.

I then made the linked fields visible in Subform 2 & 3. When I deleted the 0
record and navigated to the parent record in Subform 2, the parent record
showed 0 and the child record showed {Autonumber}. When I tried to paste
into the text box bound to the memo field in Subform 3, I got this message:

"You can't assign a value to this object.
*The object may be on a control on a read-only form
*The object may be on a form open in Design view.
*The value may be too large for this field."

On clicking OK, the value actually DOES paste, but then the Autonumber value
is replaced by an ID that I know to be in use. Closing the form results in
an error message that says that the changes could not be made to the table
because they would create a duplicate in the primary key (duh).

If you reopen and close the form and try the above again, the exact same
thing happens, except that the record number it is trying to write to goes
up by one each time.

Anyone have ANY idea what the heck is going on here?

Thanks;

Amy

 

[tina]

When I looked in the related tables, the record was

created in the child table, but its ID had not been inserted in the parent
table (the record source for Subform 2).

umm, what? child records have a foreign key field that contains the primary
key value of the related parent record - but parent records do not contain
the PK values of their child record(s).

for that new record in Subform 2 (yes, it's a 1:1 relationship, sort of).

correctly established table relationships are not "sort of" anything; they
are a specific type. in the Relationships window, is the link between the
two tables defined, or does it show as "Indeterminate" (or maybe that's
"Undetermined", i don't remember)?

and btw, the Tab control on any form has nothing to do with subforms, it's
never included in a control reference and does not affect the form/subform
hierarchy in any way.

hth

 

[Amy Blankenship]

umm, what? child records have a foreign key field that contains the
primary
key value of the related parent record - but parent records do not contain
the PK values of their child record(s).

In this case, the Parent/Child structure is reversed, because Subform 2
actually is a child of Subform 1, but Subform 3 has no direct relationship
with Subform 2's parent. It's a necessity of nesting that Subform 2 must
act *structurally* as Subform 3's parent, even though in a typical
parent/child secenario the reverse would be true. Since it is (roughly) a
1:1 relationship, there's no *real* parent or child. However, since the
"child" table maintains 1:1 relationships with many other tables, you can't
really put the FK on that side, because then you'd need some other
information to tell you which table that ID actually was referring to. And
that just gets messy.

correctly established table relationships are not "sort of" anything; they
are a specific type. in the Relationships window, is the link between the
two tables defined, or does it show as "Indeterminate" (or maybe that's
"Undetermined", i don't remember)?

Since *all* relationships seem to default to one to many and there does not
seem to be any way to edit that, any 1:1 relationship will be in the head of
the developer rather than something you can actually force Access to define.
So while Access believes that the child record could potentially be used in
many parent records (and I refer to the FORM structure not the TABLE
structure, since this isn't a typical heirarchical thing), at this moment it
is being treated as 1:1. Actually, I may wind up reusing the RTF defined in
the child record in other parent records, but I think that's even more
hoops.

Clearly, I've stepped outside the mental model that Access is best suited to
handle. However, the exact same structire works in other places, where
different parent forms show and edit a similar child form with no problem.
So I'm at a loss as to what is different here.

and btw, the Tab control on any form has nothing to do with subforms, it's
never included in a control reference and does not affect the form/subform
hierarchy in any way.

I didn't think so, but I felt it was worth mentioning, because it could
actually be relevant. For instance, on a different tab, another subform
acts as a parent to a slightly different subform which references the same
table as the problem subform, but with no problems. Could it be that having
two different subforms pointing to the same place on different tabs could
break things?

-Amy

 

[Joan Wild]

Since *all* relationships seem to default to one to many and there does
not seem to be any way to edit that, any 1:1 relationship will be in the
head of the developer rather than something you can actually force Access
to define.

Definitely not so. Access will recognize a 1:1 relationship if you define
the foreign key field as being unique.

Clearly, I've stepped outside the mental model that Access is best suited
to handle. However, the exact same structire works in other places, where
different parent forms show and edit a similar child form with no problem.
So I'm at a loss as to what is different here.

You should describe your table structure for us.

 

[Amy Blankenship]

Definitely not so. Access will recognize a 1:1 relationship if you define
the foreign key field as being unique.

Interesting. I didn't know that. Learn something new every week

 

[tina]

comments inline.

In this case, the Parent/Child structure is reversed, because Subform 2
actually is a child of Subform 1, but Subform 3 has no direct relationship
with Subform 2's parent.

if Subform3 is "sitting" inside Subform2, then it doesn't need to have a
relationship with Subform2's *parent* - it needs to have a relationship with
Subform2.

It's a necessity of nesting that Subform 2 must
act *structurally* as Subform 3's parent, even though in a typical
parent/child secenario the reverse would be true.
Since it is (roughly) a
1:1 relationship, there's no *real* parent or child.

well, first of all, and as i said before, a correctly linked pair of tables
have a *specific* relationship - not "roughly" anything. second, even in a
one-to-one relationship, there is a specific parent table and a specific
child table - as long as the tables are linked correctly, and unless you're
*not* enforcing referential integrity, which in general is a BAD IDEA.

However, since the
"child" table maintains 1:1 relationships with many other tables, you can't
really put the FK on that side, because then you'd need some other
information to tell you which table that ID actually was referring to. And
that just gets messy.

don't get hung up on field names (assuming that's what you're referring to).
a foreign key field does NOT have to have the same name as its'
corresponding primary key field in the related table. personally, i *never*
give any two fields in a database identical names, no matter how many tables
and relationships there are.

Since *all* relationships seem to default to one to many and there does not
seem to be any way to edit that, any 1:1 relationship will be in the head of
the developer rather than something you can actually force Access to define.
So while Access believes that the child record could potentially be used in
many parent records (and I refer to the FORM structure not the TABLE
structure, since this isn't a typical heirarchical thing),

you can't separate the two in that manner. regardless of how you set up
forms, they must adhere to the relational design of their underlying
tables - the data is being stored in the table(s), not the form(s). by
definition, a child record is related to a single record in its' parent
table, you can't alter that data structure no matter what you do with forms.

at this moment it
is being treated as 1:1. Actually, I may wind up reusing the RTF defined in
the child record in other parent records, but I think that's even more
hoops.

Clearly, I've stepped outside the mental model that Access is best suited to
handle. However, the exact same structire works in other places, where
different parent forms show and edit a similar child form with no problem.
So I'm at a loss as to what is different here.

i agree with Joan, we need to know what your tables structure is, before we
can hope to offer specific suggestions.

I didn't think so, but I felt it was worth mentioning, because it could
actually be relevant. For instance, on a different tab, another subform
acts as a parent to a slightly different subform which references the same
table as the problem subform, but with no problems. Could it be that having
two different subforms pointing to the same place on different tabs could
break things?

i don't see how. Access won't permit duplicate control names in a form (or a
report, to digress), so from a programming standpoint, there is no such
thing as "the same place on two different tabs" - only two different places
on two different tabs.

 

[Amy Blankenship]

comments inline.



if Subform3 is "sitting" inside Subform2, then it doesn't need to have a
relationship with Subform2's *parent* - it needs to have a relationship
with
Subform2.

Well, yes. But the reason that the form that has the FK is the parent
*form* and the form that has the PK is the child *form* is because it
doesn't make any sense to have it the other way around, for the reasons
stated.

well, first of all, and as i said before, a correctly linked pair of
tables
have a *specific* relationship - not "roughly" anything. second, even in a
one-to-one relationship, there is a specific parent table and a specific
child table - as long as the tables are linked correctly, and unless
you're
*not* enforcing referential integrity, which in general is a BAD IDEA.

I don't enforce referential integrity, ever. Let's agree to disagree with
each other on this, K?

don't get hung up on field names (assuming that's what you're referring
to).
a foreign key field does NOT have to have the same name as its'
corresponding primary key field in the related table. personally, i
*never*
give any two fields in a database identical names, no matter how many
tables
and relationships there are.

That's nice. You probably always "live" in Access. I don't, so having
identical field names makes it far easier to reuse logic.

you can't separate the two in that manner. regardless of how you set up
forms, they must adhere to the relational design of their underlying
tables - the data is being stored in the table(s), not the form(s). by
definition, a child record is related to a single record in its' parent
table, you can't alter that data structure no matter what you do with
forms.

Funny you say that, because I've done exactly that in other places, and it
works fine.

i agree with Joan, we need to know what your tables structure is, before
we
can hope to offer specific suggestions.

I don't know, it doesn't actually feel like we're making any progress toward
a solution. It seems to me that you don't have a clue as to what would have
caused the errors I've seen, or you would have said.

i don't see how. Access won't permit duplicate control names in a form (or
a
report, to digress), so from a programming standpoint, there is no such
thing as "the same place on two different tabs" - only two different
places
on two different tabs.

They both point to the same table (the same place). They live on different
tabs. Now it feels like you're just being argumentative, since I made it
clear that they were DIFFERENT subforms.

-Amy

 

[tina]

well, i agree with you that we aren't making any progress in this thread.
good luck with your project.