Subform within Subform Relationship is Indeterminate

[Red Rider]

I created a Main customer form, with a one-to-many subform. Then I
created another subform within the first subform but when I try to
create a relationship, in the relationships window, it says
“Indeterminate.” The main form to first subform work correctly, when
the record changes in the main form, all the records in first subform
change. If I create a new record in Main, the first subform goes
blank, which it should. The problem is with the second subform. If I
create a new record in the first subform I expect the second subform
to go blank but it doesn’t. The second subform only changes when the
Main form changes. I know I am well within the bounds of the first
subform but something isn’t right.

The relationships are:
Main – MainID is Autonumber and set as Primary Key
First Subform – SubID is Number (Primary Key not set)
Second Subform – SubSubID is Number (Primary Key not set)

Any help will be appreciated.

 

[TonyT]

Hi,

The link Master and Child values should represent the PK (main) and FK of
the subform's datasource, In other words, the main form and the subform
should be linked on the same fields that link the 2 underlying tables (or
queries) in their relationship to each other.

hth,

TonyT..

 

[RedRider]

Hi,

The link Master and Child values should represent the PK (main) and FK of
the subform's datasource, In other words, the main form and the subform
should be linked on the same fields that link the 2 underlying tables (or
queries) in their relationship to each other.

hth,

TonyT..







- Show quoted text -

Are you saying the 2nd subform (placed within the 1st subform) should
be linked to the Main form?

Here's how I originally linked them:
MainID (Main form) linked to SubID(1st subform)
SubID linked to SubSubID(2nd subform)

Sounds like you're saying it should link like this:
MainID (Main form) linked to SubID(1st subform)
MainID linked to SubSubID(2nd subform)
If I link it this way I no longer get Indeterminate in the
Relationships window. It shows one-to-many.

Is that right?

The three forms breakdown like this:
Main - Customer Data
1st Subform - Sales Data
2nd Subform - Details of Sales Data in 1st Subform

 

[RedRider]

Are you saying the 2nd subform (placed within the 1st subform) should
be linked to the Main form?

Here's how I originally linked them:
MainID (Main form) linked to SubID(1st subform)
SubID linked to SubSubID(2nd subform)

Sounds like you're saying it should link like this:
MainID (Main form) linked to SubID(1st subform)
MainID linked to SubSubID(2nd subform)
If I link it this way I no longer get Indeterminate in the
Relationships window.  It shows one-to-many.

Is that right?

The three forms breakdown like this:
Main - Customer Data
1st Subform - Sales Data
2nd Subform - Details of Sales Data in 1st Subform- Hide quoted text -

- Show quoted text -

OK I got it working with this relationship:

MainID linked to SubID (1 to many)
ToSubSub (field in 1st subform) linked to 2nd subform field SubSubID
(1 to many)

If new record is created in Main, subform 1 and 2 go blank
If a new record is created in 1st subform, 2nd subform goes blank.

This is working the way I want it to now.

Thanks for the help Tony.

 

[TonyT]

Hi again,

I didn't realise from your original post that the 2nd subform was a subform
of the 1st subform, I read it as *another* subform of the main form, hence my
recommendation. Glad to hear it sounds like you have it nailed now.

good luck,

TonyT..