Link Master and Link Child 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

 

[Joan Wild]

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

You said that the subforms relate to the parent form, but your layout here
suggests otherwise. Also the Tab control isn't relevant to your question,
so lets remove it as it is muddying the waters.

Form
-Subform1
-Subform2
-Subform3
Is this correct? A main form, with a child, grandchild, greatgrandchild
form?

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).

Since your problem is between subform2 and subform3, ignore the others.

So I tried to repair the LinkMaster/LinkChild relationship.

.... 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).

That's rather critical information. In order to set the Link Master/Child
properties you really need to be clear on the relationship between the
recordsources of the two subforms. What are the record sources -
queries/tables/SQL statements? What are their primary/foreign keys and how
are they related?

 

[Amy Blankenship]

You said that the subforms relate to the parent form, but your layout here
suggests otherwise. Also the Tab control isn't relevant to your question,
so lets remove it as it is muddying the waters.

Form
-Subform1
-Subform2
-Subform3
Is this correct? A main form, with a child, grandchild, greatgrandchild
form?


Since your problem is between subform2 and subform3, ignore the others.


... after viewing a

That's rather critical information. In order to set the Link Master/Child
properties you really need to be clear on the relationship between the
recordsources of the two subforms. What are the record sources -
queries/tables/SQL statements? What are their primary/foreign keys and
how are they related?

Any time I've ever used the subform wizard (which I usually do, it's just
easier), subforms always use queries rather than the underlying table. I'm
not sure what the reasoning behind that is. So, the "parent" subform is a
query on the CourseReports table. RTF is used throughout the structure in a
variety of different ways, so every place it is used has a 1:1 relationship
with a record in the RTF table. (Or more accurately 1: 0 or 1.) So a
CourseReport will have a foreign key RTFID that points to an RTF record
whose primary key is that RTFID. The "Child" record sets properties that
all RTF needs such as screen coordinates and the RTF text itself, whereas
the "Parent" record sets other properties, such as (for the CourseReports
table) if this is the print or screen version of the report, and what order
that report will be presented relative to other reports.

In writing this, I realize that the parent/child relationship is the
opposite of normal: the child (form) record has the PK which is a FK in the
parent (form) table. However, this structure is used multiple times in
other places in the structure with another child form (the RTF typically
used in CourseReports is typically longer than the RTF used other places).
So I'm not sure why in this place it's giving a problem.

Thanks!

-Amy

 

[Joan Wild]

So, the "parent" subform is a query on the CourseReports table.

So the recordsource of subform2 is CourseReports (at least a query based on
it)

RTF is used throughout the structure in a variety of different ways, so
every place it is used has a 1:1 relationship with a record in the RTF
table.

Why do you assume this is always a 1:1 relationship? That seems very odd to
me. And how does RTF fit into this particular form? Is it the recordsource
of subform1?

CourseReport will have a foreign key RTFID that points to an RTF record
whose primary key is that RTFID.

So you have a table called RTF whose PK is RTFID. CourseReport as RTFID.
Relationship is 1 RTF to Many CourseReport?

The "Child" record sets properties that all RTF needs such as screen
coordinates and the RTF text itself, whereas the "Parent" record sets
other properties, such as (for the CourseReports table) if this is the
print or screen version of the report, and what order that report will be
presented relative to other reports.

Now you're talking as though RTF is a child of the CourseReports.

In writing this, I realize that the parent/child relationship is the
opposite of normal: the child (form) record has the PK which is a FK in
the parent (form) table. However, this structure is used multiple times
in other places in the structure with another child form (the RTF
typically used in CourseReports is typically longer than the RTF used
other places). So I'm not sure why in this place it's giving a problem.

This is very difficult to follow Amy. Please remember that I can't see your
database. Are you talking about subform2 and subform3 or Form/subform1 now?

A clearer description would help me help you.

 

[Amy Blankenship]

So the recordsource of subform2 is CourseReports (at least a query based
on it)


Why do you assume this is always a 1:1 relationship? That seems very odd
to me.

If you need a new RTF, you create a new "thing" to house the RTF. So far,
that RTF then is only used in that one place. Though I can see some
advantages to reusing at least some of the RTF.

And how does RTF fit into this particular form? Is it the recordsource of
subform1?

No, it is the RecordSource for Subform 3. Subform 1 contains the "object"
that the CourseReports, once defined, are displayed in (Category).

So you have a table called RTF whose PK is RTFID. CourseReport as RTFID.
Relationship is 1 RTF to Many CourseReport?

That is what Access says about it, yes. However, at this point to me it is
a 1:1.

Now you're talking as though RTF is a child of the CourseReports.

By George...yes that is exactly what it is. RTF FORM is a child FORM of the
CourseReports FORM. Since the relationship is 1:1, neither is actually the
"parent." However, since RTF can be "consumed" by multiple tables, having
the consuming table point to the RTF table clarifies the relationship. A FK
in RTF that pointed interchangeably to one of several tables would just be
confusing. I've done that before at need, but it doesn't make sense in this
instance.

This is very difficult to follow Amy. Please remember that I can't see
your database. Are you talking about subform2 and subform3 or
Form/subform1 now?

It's only difficult to follow because you're thinking that the TABLE with
the PK is always represented by the parent FORM and the TABLE with the FK is
always represented by the child FORM. I am talking about the physical
arrangement of the forms only, which has nothing to do with the normal
heirarchical relationship Access usually models. Think of it more like
writing a join from scratch. You have to arrange the tables referenced in
the join in such a way that the outermost is related to the next outermost
and then that whole join is related to the next one in, etc. In many cases,
that order will be the heirarchical one, but in other cases it is somewhat
arbitrary.

Form = Program (ie, Course)
Subform 1 = Category
Subform 2 = CourseReports
Subform 3 = RTF

So, in the real world, when a user finishes a Category in the Course, he
will be taken to a set of Reports, whose order is defined by the
CourseReports table. The actual text of the report and its placement on
screen is drawn from the RTF table. Many other things in a course (as I
hope you can imagine) ALSO have need of RTF text and coordinates, so that
information is all stored in one place, regardless of how it is to be used.

Just to provide an example of how this works elsewhere, on another tab in
Subform 1, the structure is:
Subform 2a = Topic
Subform 3a = Page
Subform 4a = RTF

There's a button on the "Page" form that pops up a "Question" form. On a
tab on THAT form, there's a "Review Pages" subform that contains Subform 4a
again.

Subform 4a works fine.

A clearer description would help me help you.

Clear as mud?

Thanks;

Amy

 

[Joan Wild]

If you need a new RTF, you create a new "thing" to house the RTF. So far,
that RTF then is only used in that one place. Though I can see some
advantages to reusing at least some of the RTF.

Ok, so you have a table of RTFs and it contains a record for each RTF. Any
table may be related to this one RTF (what's an RTF?). I'm trying to
understand the 'things' you are modelling here, and it isn't clear. If I
have it, 1 RTF applies to many CourseReports. So of course the RTFID will
need to be in the CourseReport table - other RTF information wouldn't need
to be.

By George...yes that is exactly what it is. RTF FORM is a child FORM of
the CourseReports FORM. Since the relationship is 1:1, neither is
actually the "parent."

Even a 1:1 has a parent/child relationship.

It's only difficult to follow because you're thinking that the TABLE with
the PK is always represented by the parent FORM and the TABLE with the FK
is always represented by the child FORM.

And that is the norm. I have never seen the reverse.

I am talking about the physical arrangement of the forms only, which has
nothing to do with the normal heirarchical relationship Access usually
models.

It would be better if you forgot the forms for now and explained the table
structures/relationships

Form = Program (ie, Course)
Subform 1 = Category
Subform 2 = CourseReports
Subform 3 = RTF

What is an RTF?

So, in the real world, when a user finishes a Category in the Course, he
will be taken to a set of Reports, whose order is defined by the
CourseReports table. The actual text of the report and its placement on
screen is drawn from the RTF table. Many other things in a course (as I
hope you can imagine) ALSO have need of RTF text and coordinates, so that
information is all stored in one place, regardless of how it is to be
used.

OK so your RTF table is related 1 - M with many different tables - there's
nothing wrong with that. Instead of trying to explain the relationships via
form description, could you post the table structures/relationships?

 

[Amy Blankenship]

Ok, so you have a table of RTFs and it contains a record for each RTF.
Any table may be related to this one RTF (what's an RTF?). I'm trying to
understand the 'things' you are modelling here, and it isn't clear. If I
have it, 1 RTF applies to many CourseReports. So of course the RTFID will
need to be in the CourseReport table - other RTF information wouldn't need
to be.


Even a 1:1 has a parent/child relationship.


And that is the norm. I have never seen the reverse.


It would be better if you forgot the forms for now and explained the table
structures/relationships


What is an RTF?

That is the table referenced in Subform 3, just as all the others are. That
is, the main form reflects the Program table (which is the overall "Course"
container). Subform 1 reflects the Category table, etc. Or are you
unfamiliar with the acronym "Rich Text format" and you're asking me to
clarify the term RTF text I keep using.

OK so your RTF table is related 1 - M with many different tables - there's
nothing wrong with that. Instead of trying to explain the relationships
via form description, could you post the table structures/relationships?

I'll try again.

Program
=======
PK ProgramID

Category
=======
PK CategoryID
FK ProgramID

CourseReports
===========
PK ReportID
FK CategoryID
FK RTFID

So the heirarchical structure is pretty normal

Program
Category
CourseReports

Until you get to RTF. To enter the RTF for a given report, you need to
embed the form for the RTF that the course will display in the CourseReport
form. There is no logical place to put it other than that, from a UI
perspective and from a perspective of being able to design a "set" of
reports for a Category.

I'm not sure ultimately whether any of this directly relates to my problem.
As I said, I have roughly the same structure elsewhere and it works fine.
Regardless of WHAT the table relationship is, the LinkMaster/LinkChild
dialogue should not fail to open.

Is there a place at Microsoft.com to report a bug?

Thanks;

Amy

 

[Joan Wild]

That is the table referenced in Subform 3, just as all the others are.
That is, the main form reflects the Program table (which is the overall
"Course" container). Subform 1 reflects the Category table, etc. Or are
you unfamiliar with the acronym "Rich Text format" and you're asking me to
clarify the term RTF text I keep using.

Yes I am. I realize that RTF can mean Rich Text Format, but it didn't occur
to me that one would have a table of records of rich text formats. Tables
usually contain information (fields) that describe things. I still having
trouble picturing what is in this table. Can you provide sample data from
this RTF table - just two or three records.

I'll try again.

Actually this is your first try, but thank you.

Program
=======
PK ProgramID

Category
=======
PK CategoryID
FK ProgramID

CourseReports
===========
PK ReportID
FK CategoryID
FK RTFID

So the heirarchical structure is pretty normal

So far, so good.

Until you get to RTF. To enter the RTF for a given report, you need to
embed the form for the RTF that the course will display in the
CourseReport form. There is no logical place to put it other than that,
from a UI perspective and from a perspective of being able to design a
"set" of reports for a Category.

You indicate that RTF is 1 - M with the CourseReports table, but I have a
hunch that it's actually M-M. I won't know for sure until you post with
some explanation/sample data from the RTF table.

I think that 1 record from the RTF can be used in many records in the
CourseReports table, and 1 CourseReports record can use many records in the
RTF table. If so then you need an additional table
RTFCourseReports
ReportID (FK)
FK RTFID (FK)

CourseReports 1-M RTFCourseReports and
RTFID 1-M RTFCourseReports

Your final subform would be based on this RTFCourseReports table, where you
could place a combobox on the form to lookup the right RTFID.

I'm not sure ultimately whether any of this directly relates to my
problem. As I said, I have roughly the same structure elsewhere and it
works fine. Regardless of WHAT the table relationship is, the
LinkMaster/LinkChild dialogue should not fail to open.

You are wrong about that. There are circumstances where that dialog will
not open.

We are very close Amy. Please clarify about the RTF information.

 

[Amy Blankenship]

Yes I am. I realize that RTF can mean Rich Text Format, but it didn't
occur to me that one would have a table of records of rich text formats.
Tables usually contain information (fields) that describe things. I still
having trouble picturing what is in this table. Can you provide sample
data from this RTF table - just two or three records.

As I said earlier, the RTF table contains the screen coordinates for the RTF
to be displayed, as well as the RTF itself. So, 4 number fields (top, left,
length, width) and a memo field , which contains a bunch of rtf-encoded
text. WHAT is so difficult to grasp in that?

Actually this is your first try, but thank you.

No, the table structure has been described before. It was *in the context*
of the forms, but it was described.

So far, so good.


You indicate that RTF is 1 - M with the CourseReports table, but I have a
hunch that it's actually M-M. I won't know for sure until you post with
some explanation/sample data from the RTF table.

How on EARTH could it be M-M when the FK is in the CourseReports table?
There is never any possibility to associate more than one RTF record to one
CourseReport, nor would that ever be desirable. You only ever want to
display ONE piece of RTF per report. I can think of times you might
potentially want to re-use a piece of RTF in more than one CourseReport, but
right now that is not a goal.

I think that 1 record from the RTF can be used in many records in the
CourseReports table, and 1 CourseReports record can use many records in
the RTF table.

You can think that all day long (not sure WHY you think that, despite my
many attempts to explain what is going on here), but that does not make it
true.

If so then you need an additional table
RTFCourseReports
ReportID (FK)
FK RTFID (FK)

CourseReports 1-M RTFCourseReports and
RTFID 1-M RTFCourseReports
Nope.

Your final subform would be based on this RTFCourseReports table, where
you could place a combobox on the form to lookup the right RTFID.

That would not work for what I am trying to do. I want the user to select a
category, create a new report, and enter the RTF for that report. That's
it.

You are wrong about that. There are circumstances where that dialog will
not open.

OK, well what are they? Are you working toward a theory here, or just
running around in circles for entertainment?

Thanks;

Amy

 

[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

<stuff snipped>

Update: I found that the LinkMaster/LinkChild dialogue had stopped opening
on the other form that referenced the RTF table that was nested on a
different form on the tabset. I found that when I opened the forms
independently in design mode (not contained in the parent form/tabset), that
dialogue works fine on both forms. So I think that my hunch that having two
subforms that point to the same underlying table is correct. I'm going to
look at disassociating the form controls with the underlying table when they
are not displayed.

Thanks all for your attempts at help.

-Amy

 

[Joan Wild]

As I said earlier, the RTF table contains the screen coordinates for the
RTF to be displayed, as well as the RTF itself. So, 4 number fields (top,
left, length, width) and a memo field , which contains a bunch of
rtf-encoded text. WHAT is so difficult to grasp in that?

Nothing, now that you've explained it!

How on EARTH could it be M-M when the FK is in the CourseReports table?

If it were M-M then the FK wouldn't be in the CourseReports table.

There is never any possibility to associate more than one RTF record to
one CourseReport, nor would that ever be desirable. You only ever want to
display ONE piece of RTF per report. I can think of times you might
potentially want to re-use a piece of RTF in more than one CourseReport,
but right now that is not a goal.

Fine. I was taking a stab since I didn't have complete information (doing
my best to read your mind).

Are you working toward a theory here, or just running around in circles
for entertainment?

I was trying to help you as best I could. You have to remember that none of
us can see your database, nor are we mind readers.

Good Luck.

 

[Amy Blankenship]

Nothing, now that you've explained it!

I explained it before. You just didn't assimilate it. Go back and look.

If it were M-M then the FK wouldn't be in the CourseReports table.


Fine. I was taking a stab since I didn't have complete information (doing
my best to read your mind).


I was trying to help you as best I could. You have to remember that none
of us can see your database, nor are we mind readers.

I noticed that you didn't answer the question about what the circumstances
are where the dialogue wouldn't open, which might have been a *useful*
response to my questions.

It appears that that failure of the dialogue to launch *was* actually
related to the fact that the same underlying table is being referenced by
two separate forms that are displayed at the same time (though only one is
visible because of the Tab control).

The RTF subform trying to reuse Autonumbers that had already been used was
caused by corruption of the underlying table in the backend. Restoring that
from backup fixed that problem.

The failure of records in the "Child" form was caused by the fact that a PK
in a child form cannot be propagated "upwards" to a FK in a containing form
by use of the LinkMaster/LinkChild connections.

I tell you all of this, not for your information, but so someone else won't
spend days and days being asked questions by people who
(1) don't actually know why things are failing and
(2) are just fishing around hoping something will jump out at them

If that is your style when you are helping someone, great. I often withhold
what I think the problem is and ask for more details before offering a
potential solution. However, I would NEVER do that for five DAYS,
especially if the primary reason I was asking so many questions was actually
that I didn't read the poster's explanations very carefully. After the
first couple of back-and-forths, I'd offer whatever insight I had so that
they could try whatever seemed appropriate based on that insight. OR, I'd
say "I don't actually know what is causing the problem, but I hope that by
further understanding your situation, something might jump out at me." That
would have allowed me to decide whether to cut my losses and just try things
till it was fixed, which I ultimately did, or stick with you and hope you
would figure it out.

I say the above as a longtime Team Macromedia/Adobe Community Expert to a
Microsoft MVP, hoping that it might lead you to frustrate future posters a
bit less than what happened this time .

-Amy

 

[Joan Wild]

The failure of records in the "Child" form was caused by the fact that a
PK in a child form cannot be propagated "upwards" to a FK in a containing
form by use of the LinkMaster/LinkChild connections.

I hesitate to suggest this, since you seem so 'set' on the form layout you
have. I don't believe you need a subform for the RTF table. Since the
CourseReport has the RTFID, and only one RTFID is used per report, you can
use a combobox on the CourseReport subform; bind it to the RTFID in
CourseReports. Set the rowsource of the combobox to the RTF table and
lookup the necessary RTF for the CourseReport. If a new RTF needs to be
added, you can use the NotInList event of the combobox to add it.

If that is your style when you are helping someone, great. I often
withhold what I think the problem is and ask for more details before
offering a potential solution. However, I would NEVER do that for five
DAYS,

I first responded on the 23rd and today is the 24th - five days?

After the first couple of back-and-forths, I'd offer whatever insight I
had so that they could try whatever seemed appropriate based on that
insight.

Which is exactly what I did.

I think I'm done here. Best of luck.

 

[strive4peace]

Hi Amy,

I'm coming in a little late in the game, so I will make comments on some
of the things previously posted.

Joan has given you some very good advice -- which is hard to do when you
began speaking in such general terms. I actually did read your post
before Joan responded (and I'm glad she did, I have watching this thread
to see how she would handle the information given).

I did not initally respond because I felt that not enough information
was given about the actual data (and I like to know exactly what you are
doing to help the best). Like Joan, I volunteer time because I enjoy
helping others; and it is challenging to step into a new arena and
actually do some good Okay, I am also a nerd -- my hand flew up
whenever the teacher asked a question in school -- I loved knowing
everything. Well, now I realize that, of course, I don't know it all
(far from it actually)... but I do try Wanna make me happy? Give me a
6-pack and a problem to solve

The most important part to get right with Access is data structure --
not forms. You are the expert with your data, and we know about Access
.... it helps us help you to first know the structure -- specific names
for tables, primary keys, and the fields that are used to link data --
and then what you have built.

~~~

"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."

that is because, if you will look at the design view that the table is
based on, the DefaultValue of all numbers is set to 0 (zero) -- this is
done by Access. Unless you change it, that is what will happen if
another value is not filled out to replace it (or it is deleted) ...
OR... you REMOVE the DefaultValue (which is what I usually do for
foreign keys so referential integrity can be enforced).

~~~

" but then the Autonumber value is replaced by an ID that I know to be
in use"

could that be because the autonumber field is in LinkChildFields? Just
a wild guess...

this could not happen, by the way, if the Autonumber field in your table
was also the primary key ...

~~~

"I realize that the parent/child relationship is the opposite of normal:
the child (form) record has the PK which is a FK in the parent (form)
table. "

"It's only difficult to follow because you're thinking that the TABLE
with the PK is always represented by the parent FORM and the TABLE with
the FK is always represented by the child FORM. I am talking about the
physical arrangement of the forms only, which has nothing to do with the
normal heirarchical relationship Access usually models. Think of it
more like writing a join from scratch. You have to arrange the tables
referenced in the join in such a way that the outermost is related to
the next outermost and then that whole join is related to the next one
in, etc."

Amy, please let me say something here... your forms should be designed
according to your data relationships... you cannot enter a child
record first and then it's parent ...Now, if the forms are for display
only, that is a different matter.

It seems to me -- and please forgive me if I just don't see it as
clearly as you -- I have tried for the past few hours (in between doing
other things too) <g> -- that you have not really specified how the RTF
table relates -- is it always to the CourseReports table? Or can that
change?

If you did specify, please forgive me -- there has been a lot to read
<smile> -- so perhaps you could re-iterate?

I am summarizing things for myself, for you -- and also for others who
are trying to follow along.

~~~

Your forms:

Form = Program (ie, Course)
Subform 1 = Category
Subform 2 = CourseReports
Subform 3 = RTF

~~~
then the tables ...

Program
=======
PK ProgramID

Category
=======
PK CategoryID
FK ProgramID

CourseReports
===========
PK ReportID
FK CategoryID
FK RTFID


ahaa! NOW we are geting somewhere -- the table structure is what comes
first. So to help you the best, it is what we must know first. Forms
are related to each other only because the tables they are based on are
related.

Now I have a real question:
why put RTFID in the CourseReports table? Why not put ReportID in the
RTF table?

Data should *flow* ... smoothly -- you should be able to actually
visualize it. When you can do this, you are ready to build objects on
top of it.

Too many folks want to jump to forms (since that is ultimately what they
will work with the most) and ignore the tables and how THEY relate... or
build the tables as an afterthought ... I need to collect this or that
on the form so I'll put another field in the table. No. first, you
build tables from the data you have to work with and THEN come the forms...

Drawing an analogy to a building:

Data structure is the foundation.

Forms are walls.

Reports are paint.

Organize your data so the structures are strong. You want what you
build on that foundaton to be stable and flexible.

Structuring data is an iterative process. Set up tables, print
relationship diagram, change tables, print relationship diagram, put
data in, change tables, print relationship diagram, …

The best solution is simple... but it is the hardest to find.

It takes thought and foresight to design your structures well. And the
simplest solution is rarely easy to see. Get the structure right before
building anything else. If you have already built forms, queries, or
other objects, fix the structure before building any more.

"the RTF table contains the screen coordinates for the RTF to be
displayed, as well as the RTF itself. So, 4 number fields (top, left,
length, width) and a memo field , which contains a bunch of rtf-encoded
text. "

.... and, as I mentioned earlier, this is where you should have a foreign
ID ... I believe what you are calling ReportID

~~~~

when you say that the "LinkMaster/LinkChild dialogue had stopped
opening" ... I am a bit confused...

Please don't feel insulted if I start to cover ground you already know;
this newsgroup is read by thousands who simply study -- so I will
explain things you may know for their benefit too...

there is something very important to understand about subform controls
and subforms...

~~~
The first click on a subform control puts handles* around the subform object
*black squares in the corners and the middle of each size -- resizing
handles

The subform object has properties such as

Name
SourceObject
LinkMasterFields
LinkChildFields
Visible
Locked
Left
Top
Width
Height

the subform control is just a container for the subform.

the subform itself is an independent form -- you can open it directly
from the database window and it has the same properties of the main
form. It is only called a subform because of the way it is being used.

To summarize, when you are in the design view of the main form

(1) the first click on the subform is the subform control -- you will
see the handles around the edges

(2) the second click gets you INTO it -- you will see a black square
where the rulers intersect in the upper left (and this is the same as if
you went to the design directly)

so,,, *if you are a double-clicker, you will only accidentally see the
properties for the subform control*...

this is such a common thing not to realize -- I remember when I was
there too... waiting for the light to come on...

~~~

"...having two subforms that point to the same underlying table... "

if your hunch is not to do it, then it is right! It is best to base
each form/subform on just one table ... and to have just one
form/subform populate a particular table at one time.

There are so many other ways to display data from a related table
without using it in a Recordsource. Here is my favorite -- and I will
just use a generic example:

~~~~~~~~~~~~~~~~
Combobox Example -- using information about People

Create an autonumber field in the People table -->

PID, autonumber

then, in the other tables...
PID, long, DefaultValue = Null

Then, when you want to put data in (which should be done from a form),
you can set it up to pick names from a list but store the PID.

~~~~~

from the design view of a form, add a combobox control and give it a
logical name

Name --> PID

ControlSource --> PID

RowSource -->
SELECT
PID,
LastName & ", " & Firstname AS Fullname,
BirthDate
FROM People
ORDER BY LastName, Firstname
(you can click in the RowSource property, click on the Builder Button
(...) to the right and use a tool that works like the one you are
probably familar with for queries if SQL is not your strong suit)

BoundColumn --> 1

ColumnCount --> 3

columnWidths --> 0;2;1
(etc for however many columns you have -- the ID column will be hidden)

ListWidth --> 3
(should add up to the sum of the column widths)

if you have a listbox, sometimes you need to make the width .01 more
than the sum of the columns to prevent the horizontal scrollbar from
appearing.

PID will be stored in the form RecordSource while showing you names from
another table... a MUCH better and more reliable method.

If you want to show other information from your combobox in other
controls, you can use calculated fields. For instance

textbox:
Name --> BirthDate
ControlSource --> = PID.column(2)

The reason that column 2 is referenced instead of column 3 is that
column indexes start with 0, not 1, in Access.

~~~~

Now, here is one trick that I REALLY make use of ... make a HIDDEN
combobox bound to the primary key of the table you are entering data
into -- voila! all of a sudden, you can show information from just
about anyplace you can relate to! Well, I think it is pretty cool anyway

~~~~

Amy, I hope I have been able to give you a different perspective --
quite often, you already have the answer yourself... it just takes
something or someone to make (help) you look at it differently to see
what you already know.



Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*