By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,561 Members | 872 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,561 IT Pros & Developers. It's quick & easy.

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

P: 4
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
May 30 '07 #1
Share this Question
Share on Google+
3 Replies

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Jun 1 '07 #2

P: 4
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!
Jun 1 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Hi Tony

Try Main Form based on Table A and subform based on
Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT TableC.VariableA_ID, TableB.*
  3. FROM Table A INNER JOIN Table B 
  4. ON TableC.VariableB_ID=TableB.VariableB_ID
  5.  
See if that works.

Mary
Jun 1 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.