Subform within a subform

[ajak]

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.

 

[John W. Vinson]

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.

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?

 

[ajak]

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.

 

[John W. Vinson]

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.

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

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?