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
3 3145
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.
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!
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
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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...
|
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),...
|
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...
|
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)
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
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....
|
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
|
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...
| | |