If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Linking Sub-forms to Forms
I made a database with 17 tables. It is an inspection with 16
sub-catagories. The main table has Location, Date, Time ect... and the other 16 tables are sub- catagories. I made a form with 16 subforms and set autonumber as the primary key for all of the tables. When I create a new record in the main form, how do I get all of the 16 sub-forms to start a new record as well? (Some of the catagories are not applicable for every inspection and would be left blank or set have a default value of N/A.) Any help would be very much appreciated. -- tr |
#2
|
|||
|
|||
Linking Sub-forms to Forms
Tom
Why 16? Why not 15 or 17? Could that number change? What you mean by "sub-categories" and what I mean may not be the same. Please describe/define the data you are working with. One advantage to a well-normalized relational database design is that you do NOT need to "start a new record" in every "sub-form". If you're looking for more specific suggestions, offer more specific descriptions... Good luck Regards Jeff Boyce Microsoft Office/Access MVP "Tom" wrote in message ... I made a database with 17 tables. It is an inspection with 16 sub-catagories. The main table has Location, Date, Time ect... and the other 16 tables are sub- catagories. I made a form with 16 subforms and set autonumber as the primary key for all of the tables. When I create a new record in the main form, how do I get all of the 16 sub-forms to start a new record as well? (Some of the catagories are not applicable for every inspection and would be left blank or set have a default value of N/A.) Any help would be very much appreciated. -- tr |
#3
|
|||
|
|||
Linking Sub-forms to Forms
On Wed, 7 Jan 2009 08:06:26 -0800, Tom wrote:
I made a database with 17 tables. It is an inspection with 16 sub-catagories. The main table has Location, Date, Time ect... and the other 16 tables are sub- catagories. I made a form with 16 subforms and set autonumber as the primary key for all of the tables. You CANNOT link tables from autonumber to autonumber! The child tables should have a Long Integer (*not* an autonumber) as their primary key, if you indeed are Subclassing with one-to-one relationships (if the term Subclassing is new, you may need to reconsider your design). You could use the autonumber on the main form as the Master Link Field and the long integer foreign key as the Child Link Field on the subforms, in order to maintain the link. When I create a new record in the main form, how do I get all of the 16 sub-forms to start a new record as well? (Some of the catagories are not applicable for every inspection and would be left blank or set have a default value of N/A.) Any help would be very much appreciated. I'd avoid adding empty "placeholder" records; it's easy to do a query which will show "n/a" if no record exists. -- John W. Vinson [MVP] |
#4
|
|||
|
|||
Linking Sub-forms to Forms
I was where you are a short while ago, so the words I use may be more helpful
to you than the volumes of Access Bibles I've got on my desk. I hope this points you in the right direction. Your main table has a primary key - usually an autonumber field - that the other sub tables need to know in order to "link" to the main table. The autonumber field is a long integer, so the foreign key in each sub table needs to be a long integer field too. Table 1 - Primary Key - auto number = Table1ID Tables 2-16 (sub tables) - MainTableID (also a long integer, but not auto. Just a number field. When you create your relationships, make a one to many relationship from the Table1ID to the MainTableID of each sub table. Enforce referential integrity. Create forms for the main table and sub tables. "Nest" the subtables inside the main table and include the MainTableID field in the report header of the subtables. Enter data into the main table and When you enter data into any one of the subtables, it automatically updates the table1ID to the subtables MaintableID field. Regards "Tom" wrote: I made a database with 17 tables. It is an inspection with 16 sub-catagories. The main table has Location, Date, Time ect... and the other 16 tables are sub- catagories. I made a form with 16 subforms and set autonumber as the primary key for all of the tables. When I create a new record in the main form, how do I get all of the 16 sub-forms to start a new record as well? (Some of the catagories are not applicable for every inspection and would be left blank or set have a default value of N/A.) Any help would be very much appreciated. -- tr |
#5
|
|||
|
|||
Linking Sub-forms to Forms
It sounds like you know exactly what I am doing! I will try that. Thank you
very much. The database I am working on is an inspection sheet. The inspection has all these parts that may or may not apply. My goal is to electronically enter the inspections so the data can be sorted or queried in a meaningful way. (by project, date or supervisior ect..) Thanks again. -- tr "Bob Waggoner" wrote: I was where you are a short while ago, so the words I use may be more helpful to you than the volumes of Access Bibles I've got on my desk. I hope this points you in the right direction. Your main table has a primary key - usually an autonumber field - that the other sub tables need to know in order to "link" to the main table. The autonumber field is a long integer, so the foreign key in each sub table needs to be a long integer field too. Table 1 - Primary Key - auto number = Table1ID Tables 2-16 (sub tables) - MainTableID (also a long integer, but not auto. Just a number field. When you create your relationships, make a one to many relationship from the Table1ID to the MainTableID of each sub table. Enforce referential integrity. Create forms for the main table and sub tables. "Nest" the subtables inside the main table and include the MainTableID field in the report header of the subtables. Enter data into the main table and When you enter data into any one of the subtables, it automatically updates the table1ID to the subtables MaintableID field. Regards "Tom" wrote: I made a database with 17 tables. It is an inspection with 16 sub-catagories. The main table has Location, Date, Time ect... and the other 16 tables are sub- catagories. I made a form with 16 subforms and set autonumber as the primary key for all of the tables. When I create a new record in the main form, how do I get all of the 16 sub-forms to start a new record as well? (Some of the catagories are not applicable for every inspection and would be left blank or set have a default value of N/A.) Any help would be very much appreciated. -- tr |
#6
|
|||
|
|||
Linking Sub-forms to Forms
I think I do know what you are doing. Its been a seven year learning by the
seat of the pants process for me. I wrote a quality systems database for ISO 9001-2000 that has been upgraded constantly as I learn new things. One of the areas is maintenance - calibrations - pms and projects. The entire quality system is integrated into the one database now - so duplication of work is greatly diminished. The people who regulary respond to my cries for help in this forum are fantastic. I just wanted to give back a little. Good luck. "Tom" wrote: It sounds like you know exactly what I am doing! I will try that. Thank you very much. The database I am working on is an inspection sheet. The inspection has all these parts that may or may not apply. My goal is to electronically enter the inspections so the data can be sorted or queried in a meaningful way. (by project, date or supervisior ect..) Thanks again. -- tr "Bob Waggoner" wrote: I was where you are a short while ago, so the words I use may be more helpful to you than the volumes of Access Bibles I've got on my desk. I hope this points you in the right direction. Your main table has a primary key - usually an autonumber field - that the other sub tables need to know in order to "link" to the main table. The autonumber field is a long integer, so the foreign key in each sub table needs to be a long integer field too. Table 1 - Primary Key - auto number = Table1ID Tables 2-16 (sub tables) - MainTableID (also a long integer, but not auto. Just a number field. When you create your relationships, make a one to many relationship from the Table1ID to the MainTableID of each sub table. Enforce referential integrity. Create forms for the main table and sub tables. "Nest" the subtables inside the main table and include the MainTableID field in the report header of the subtables. Enter data into the main table and When you enter data into any one of the subtables, it automatically updates the table1ID to the subtables MaintableID field. Regards "Tom" wrote: I made a database with 17 tables. It is an inspection with 16 sub-catagories. The main table has Location, Date, Time ect... and the other 16 tables are sub- catagories. I made a form with 16 subforms and set autonumber as the primary key for all of the tables. When I create a new record in the main form, how do I get all of the 16 sub-forms to start a new record as well? (Some of the catagories are not applicable for every inspection and would be left blank or set have a default value of N/A.) Any help would be very much appreciated. -- tr |
Thread Tools | |
Display Modes | |
|
|