Adding records to a SubSubForm without adding a record to the SubForm

Question

Hi all,

I have a situation that is caused entirely by my interface design, but know there has to be a way around what I am doing.

Essentially, I have a primary table and (due to the volume of fields) a series of other tables that contain topic based field sets.  Each of the "topic" tables has a one to one relationship with the primary.  Some of the "topic" tables have one-many relationships with sub tables.

In the interface, because of my design (which is probably inefficient) I have the following scenario.

Primary form (based on primary table) contains a subform object attached to which is one of the "topic" forms based on one of the topic tables.  The topic form contains a subform object that has a form based on one of the tables that has a one to many relationship with the topic table.

Anyhoo ... The natural sequence is that the Prmary form needs a completed record before the topic form can have data added and the topic form needs a completed record before the SubSubForm can be filled out due to PK violations (ie. there needs to be a primary key set in the topic table before the subsubform can be filled out).

What I need is a way to fill out the subsubform based on the PK of the primary form regardless of ehether there is data in the topic form.

I have tried redirecting the one to many relationships from the primary table to the sub tables rather than through the topic tables to no avail.

Any suggestions welcome.

Below is a diagram of the form layout.

---

Stephen RedGalaxy Darwin, Australia

Answer 1

If Table1 and Table2 have a one-to-one relationship, you don't need Table2 and the subform based on it. You can incorporate the fields from Table2 into Table1.

(If this would lead to more than 255 fields, there are probably repeated fields that ought to be in a separate table with a one-to-many relationship instead of a one-to-one relationship)

Table3 would then be related directly to Table1, and the related subform would be placed directly on the primary form.

If you prefer to keep the current table design, also place the form based on Table3 directly on the primary form. That would allow you to create records in this subform without the need of creating a record in the subform based on Table2.

---

Regards, Hans Vogelaar

Answer 2

Thanks Hans. There are in fact 20 tables that sit in the position of Table 2 because there are in excess of 1500 fields.  This is a research database, so a significant amount of data is being stored per subject.  There are also over 100 lookup tables that populate combo boxes and another 45 or so tables in the position of Table 3 in the example.

The tables are broken up the way they are for a couple of reasons. 

1. The fields in the primary table are compulsary.
2. The fields in the secondary tables with one to one relationships are all optional depending ont he data that can be collected
3. The records in the tertiary tables with one to many relationships are records where many instances of the same type of data are collected on a single subject such as prevailing health conditions
4. The data collected comes from over 50 sources where only one source prvides primary data and all sources provide the data in varying formats.

The are no repeated fields anywhere in the database.  Despite the desire of the client to do so.

So, I need to keep the data structured as it is but find a solution to the above problem.

In terms of including the subform based on table 3 in the primary form rather than as a subform of the topic it belongs to, there are already 24 subforms representing the different topics of data that the user needs to go through.  Moving the subsubform onto the main form would not only mean that there are more pages/screens of data for the user to go through but it would also mean that topic related information is no longer together on the same screen.

I can resolve this by forcing the user to fill out the fields on the subform before the subsubform becomes active but I prefer not to do that.  I could also resolve it by making the those problematic subforms based on the primary table and then moving the information on the subform to a subsubform of its own.  That would make the information in each of the subsubforms for a particular topic completely independant.  Each subsubform would have a relationship directly with the primary table then rather than through the topic data.

However, I would really like to know if there is a better way to resolve this.  I want to avoid having 62 or more "popup" forms.  At the moment the interface look really slick and is simple for users. It just has this issue on a couple of the topic subforms.

Cheers

---

Stephen RedGalaxy Darwin, Australia

Answer 3

Hi Stephen,
Thank you for posting. 
I will help you involve others to see whether they can give you some suggestions about your problem. There might be some delay about the response. Appreciate your patience.
Best Regards,

---

Bruce Song [MSFT]
MSDN Community Support | Feedback to us

Answer 4

Hi Stephen,

It sounds like you just need to uncheck "enforce referential integrity" for the relationship between Table2 and Table3.  This should allow you to add a record to the subsubform without having a related record in the subform.

I hope this helps!

---

Sharon M, Microsoft Online Community Support