473,326 Members | 2,133 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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

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
3 3145
MMcCarthy
14,534 Expert Mod 8TB
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
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
14,534 Expert Mod 8TB
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

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

Similar topics

0
by: Arnold | last post by:
Hi there, I have a form to organize bottles in mind, but am unsure if it will work. Here's some background info: Mainform = frmProduct, which contains fields for pricing, status, etc. of...
1
by: scousineau | last post by:
help!! I have a form which contains 6 subforms, with check boxes which run events that then update data on all the forms. the data is all on 1 table in a backend data base and the user works the...
2
by: Terry | last post by:
Hello, I wonder if anyone can shed light on this problem for me. I have an Access 97 front end with an SQL 2000 database. There is a Business main form with an Owner subform and corresponding...
8
by: Zlatko Matiæ | last post by:
Hello. How can I synchronize subforms content with current record in master form, if both form and subform are based on DAO code ? I assigned DAO recordset to forms by using QueryDef, on Load...
6
by: F-13 | last post by:
I'm working on a BOM in Access 200 from an example downloaded from from the web. The sample database contains three tables, Assemblies (the list of items needed to assemble any assembly),...
1
by: santociabattari | last post by:
Hi Folks, I have 3 forms I want to link, so that when I advance 1 record in one form it will update the other two with the same record in the first form. I have tried using the form wizard (I...
4
by: danthrom | last post by:
Hi, I have a database with four tables. tbl_Client client_alias (PK, text) tbl_Matters matterID (PK, autonumber) matter (text) client_alias (foreign key, tbl_Client)
4
by: rdsandy | last post by:
Hi, I am going to start off and apologise for the length of this post! I have a form with 9 subforms on. Currently, each subform has its own table. Each of the tables have exactly the same...
3
by: 6afraidbecause789 | last post by:
Think school - students - discipline interventions - misbehaviors - staff for this one....On a mainform frmStudentInterventions, I have linked a subform (sfrmMisbehaviors) with another subform...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.