Subforms Inside Subforms

[LordPanzer]

I'm trying to create a multiple nested form that gives the user the ability to add multiple submissions to fields inside of the form and or subform before it. First off if I was in control of how the program structure was I would not be making what looks like a mess, but I was asked to create it in this specific way. The struture should be holding 'ID' which is the main key in the main form and all three subforms.

Correct Structure Picture
The 'ID' are simply a increment and 'ID' Function shows what should happen when a second 'ID' is created and how they branch off.

Relationship Picture
The current setup uses 'Main Table ID' then ties it to 'Function ID' which is a number not an autoNumber. Table 'Function FunctionID' is an autoNumber and the cycle continues tell 'Phase & Effects'. I would simply put ID as the Main key and autoNumber in each teable but it gives me an error each time.



Current Structure Picture
The current structure I have is placing sub form information in each main form submittion creating duplicate values, this picture contains what is stored in the 'Main Table'.

Form Picture
This is just to show you the setup I currently have and if you have thoughts on possible restructuring, but it is not a main concern.

Access Pictures.zip

[ssanfu]

My comments:

You should only use letters, numbers and possibly the underscore (_) in names. No punctuation! No spaces.
http://access.mvps.org/access/tencommandments.htm

Do not use reserved words for object names. "System" and "Function" are reserved words.
See http://office.microsoft.com/en-us/ac...010030643.aspx


You should find a naming convention you like and use it consistently.
Here is one http://access.mvps.org/access/general/gen0012.htm
Or you can make up your own...
Or Google "Naming conventions".

I add "_PK" to the primary key and "_FK" to foreign keys.
Example:
Main table: SystemID_PK
Function table: SystemID_FK


Another convention is that the table PK is always the first field in the table.

"ID" is a poor field name. "SystemID" or "FunctionID" are better names.

You shouldn't use multi-value fields. (It looks like "Phase(s)" might be a MVF)