Forms, Subforms: Linking PKs of a bridge table in same form

[TonyJH226]

I’m using Access 2003 and am a novice to Access.

I am creating a form to facilitate data entry to seven tables. The main table (A) has four sub-tables that are related to the main table by one-to-one relationships. A fifth sub-table (B) has a one-to-many relationship to Table A and serves as a bridge to another table (C) also with a one-to-many relationship to that table. So far, I have used the sub-form wizard to create the main form and four sub-forms that directly relate to Table A through the linked primary key.

The problem I am having is linking Tables A, B, & C in the same form. I have used the sub-form wizard to place a sub-form for Table C with no links to Table A. Then I nested the sub-form for Table B within that for Table C with its primary keys linked. How do I link the primary keys of the sub-sub-form to the main form (Table A to Table C)?

Thanks.
Tony

[MMcCarthy]

I’m using Access 2003 and am a novice to Access.

I am creating a form to facilitate data entry to seven tables. The main table (A) has four sub-tables that are related to the main table by one-to-one relationships. A fifth sub-table (B) has a one-to-many relationship to Table A and serves as a bridge to another table (C) also with a one-to-many relationship to that table. So far, I have used the sub-form wizard to create the main form and four sub-forms that directly relate to Table A through the linked primary key.

The problem I am having is linking Tables A, B, & C in the same form. I have used the sub-form wizard to place a sub-form for Table C with no links to Table A. Then I nested the sub-form for Table B within that for Table C with its primary keys linked. How do I link the primary keys of the sub-sub-form to the main form (Table A to Table C)?

Thanks.
Tony

Hi Tony

As I understand your structure. Table A is related to Table B (1 to many) and Table Bis related Table C (1 to many)

In this case ...

Form A will have a subform Form B which will itself have a subForm Form C.

For any further help I will need to know the Primary key and Foreign key in each table.

[TonyJH226]

Hi, Thanks for the response. I think I am slowly figuring out that what I want Access to do is read my mind and that technology is not quite in this release. I'm not sure if Access 2007 has this capability yet, maybe the next version will.

Clarification: Table A has PK VariableA_ID, Table C has PK VariableC_ID, Table B is a bridge table for the two and has a double PK which are foreign keys VariableA_ID and VariableC_ID. With that setup, I can nest a form for data for Table C inside the form for Table A and then nest a form for Table B inside the form for Table C which links VariableC_ID to the same in the form for Table C. The VariableA_ID will have to be a pull down combo box which will allow the input person to chose which VariableA_ID to match to the VariableC_ID.

What I am actually going to do and what makes more sense for this case is to have a separete data entry form for Tables B and C and uses the pull down choice for VariableA_ID.

Thanks very much for your help. Sometimes just posing the question on the forum and waiting for a response forces a different type of thinking and reassessment.

Tony

PS Do let me know when that "new" Access version comes out!

[MMcCarthy]

Hi Tony

Try Main Form based on Table A and subform based on

 
SELECT TableC.VariableA_ID, TableB.*
FROM Table A INNER JOIN Table B 
ON TableC.VariableB_ID=TableB.VariableB_ID
 

See if that works.

Mary