Subform within a subform

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ajak - 12 Feb 2008 01:39 GMT

I have a subform (Person) which is nested within another subform
(PersonalAssistant). Everything works fine except the foreign key in the
PersonalAssistant subform. I have used the Link Child and Master Fields
property to establish this link. In this case the primary key for the Person
table is PersonID. Its an autonumber field which begins from 4000. When the
foreign key is added by access, it takes the last digit or last two digits.
So say the PersonID is 4001. What I see in the PersonalAssistant subform and
table on the PersonID foreign key field is 1.

Reply to this Message

John W. Vinson - 12 Feb 2008 06:09 GMT

>I have a subform (Person) which is nested within another subform
>(PersonalAssistant). Everything works fine except the foreign key in the
[quoted text clipped - 4 lines]
>So say the PersonID is 4001. What I see in the PersonalAssistant subform and
>table on the PersonID foreign key field is 1.

You can see your form. We cannot.

What are the Primary Keys of the parent table? the first child table? What are
the foreign keys in the two subform tables? What are the actual Master and
Child Link Field properties of both subforms?

Signature

John W. Vinson [MVP]

Reply to this Message

ajak - 13 Feb 2008 00:33 GMT

Thanks for the reply John. The parent table (Grant) has 1 primary key. It is
an autonumber field. The primary key is GrantID.

I'm assuming that by child table you mean the table that is linked to the
Grant table. In this case it is the Person table, which is the first subform
table. The primary key for the person table is an autonumber field called
PersonID. It is linked to the Grant's table by a GrantID foreign key.

The second subform table is PersonalAssistant. It's primary is an autonumber
field called PersonalAssistantID. Its is linked to the Person table by a
PersonID foreign key.

The Link Master field for the Person subform is GrantID. The Link Child
field is GrantID.

The Link Master field for the PersonalAssistant subform is PersonID. The
Link Child field is PersonID.

I hope someone can help. I actually ran a query with a join on these two
tables (Person and PersonalAssistant) and it worked, though I am worried
about the future implications. For example, suppose I have a Person with a
PersonID of 4001. Then I also have a Person with a PersonID of 5001. Assuming
that the PersonalAssistant table only stores part of the PersonID, in this
case the 1, then this can cause problems. I will test this theory out.

> >I have a subform (Person) which is nested within another subform
> >(PersonalAssistant). Everything works fine except the foreign key in the
[quoted text clipped - 10 lines]
> the foreign keys in the two subform tables? What are the actual Master and
> Child Link Field properties of both subforms?

Reply to this Message

John W. Vinson - 15 Feb 2008 01:39 GMT

>Thanks for the reply John. The parent table (Grant) has 1 primary key. It is
>an autonumber field. The primary key is GrantID.
[quoted text clipped - 3 lines]
>table. The primary key for the person table is an autonumber field called
>PersonID. It is linked to the Grant's table by a GrantID foreign key.

Does each person get one and only one grant, then?

>The second subform table is PersonalAssistant. It's primary is an autonumber
>field called PersonalAssistantID. Its is linked to the Person table by a
[quoted text clipped - 12 lines]
>that the PersonalAssistant table only stores part of the PersonID, in this
>case the 1, then this can cause problems. I will test this theory out.

It doesn't make ANY sense that the personID is getting 1 stored. It's
certainly NOT truncating the master link field. I'm perplexed by the
PersonalAssistant table - it sounds like the personal assistant has a PersonID
for him or herself, and a different one for the person they assist. What is
the actual structure of this table?

Signature

John W. Vinson [MVP]

Reply to this Message