Linking Sub-forms to Forms

[Tom]

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.

 

[Jeff Boyce]

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

 

[John W. Vinson]

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.

 

[Bob Waggoner]

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]

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.

 

[Bob Waggoner]

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.