3 subforms in a main form problem

I'm using Access 2003.

I have 4 tables related to each other through one to many relationships:

YearCategoryMonthTransaction

When I use the form wizard to build my master form, it appears to only
configure 2 subforms no matter how or what tables I select.

I need the Year to lead as a drop down, with 3 subforms populating according
to the relationship structure.

The table relationships work fine as i have added test data and its all
appearing as it should when viewed as tables.

I have attempted to add the 3rd subform manually but the only link options i
have are to the main form. I need the 3rd form (transactions) to be linked to
the 2nd subform (month).
[Subform3 changes according to the Month selected in Subform2.]

The only way i can achieve the functionality is to nest the 3rd subform
within the 2nd subform. But data entry is messy and not intuitive.

Any ideas / comments welcome. Thank you.

Paul.

Paul,

First of all, Year and Month are Reserved Words (i.e. have a special
meaning) in Access, and as such should not be used as the name of a
field or control or object.

You can put Subform2 and Subform3 both as subforms on the same parent
form. Then, on the parent form, put an unbound textbox (hidden,
probably) to reference the value of the linking field from Subform2 - in
other words you set its Control SOurce property to the equivalent of:
=[Subform2]![NameOfLinkingField]
Then, you set the Link Master Fields property of Subform3 to the name of
this textbox.

--
Steve Schapel, Microsoft Access MVP

Paulc wrote:
I'm using Access 2003.

I have 4 tables related to each other through one to many relationships:

YearCategoryMonthTransaction

When I use the form wizard to build my master form, it appears to only
configure 2 subforms no matter how or what tables I select.

I need the Year to lead as a drop down, with 3 subforms populating according
to the relationship structure.

The table relationships work fine as i have added test data and its all
appearing as it should when viewed as tables.

I have attempted to add the 3rd subform manually but the only link options i
have are to the main form. I need the 3rd form (transactions) to be linked to
the 2nd subform (month).
[Subform3 changes according to the Month selected in Subform2.]

The only way i can achieve the functionality is to nest the 3rd subform
within the 2nd subform. But data entry is messy and not intuitive.

Any ideas / comments welcome. Thank you.

Paul.

Steve, thanks for the tip.
I will try it shortly.

happy new years.

Paul.

"Steve Schapel" wrote:

Paul,

First of all, Year and Month are Reserved Words (i.e. have a special
meaning) in Access, and as such should not be used as the name of a
field or control or object.

You can put Subform2 and Subform3 both as subforms on the same parent
form. Then, on the parent form, put an unbound textbox (hidden,
probably) to reference the value of the linking field from Subform2 - in
other words you set its Control SOurce property to the equivalent of:
=[Subform2]![NameOfLinkingField]
Then, you set the Link Master Fields property of Subform3 to the name of
this textbox.

--
Steve Schapel, Microsoft Access MVP

Paulc wrote:
I'm using Access 2003.

I have 4 tables related to each other through one to many relationships:

YearCategoryMonthTransaction

When I use the form wizard to build my master form, it appears to only
configure 2 subforms no matter how or what tables I select.

I need the Year to lead as a drop down, with 3 subforms populating according
to the relationship structure.

The table relationships work fine as i have added test data and its all
appearing as it should when viewed as tables.

I have attempted to add the 3rd subform manually but the only link options i
have are to the main form. I need the 3rd form (transactions) to be linked to
the 2nd subform (month).
[Subform3 changes according to the Month selected in Subform2.]

The only way i can achieve the functionality is to nest the 3rd subform
within the 2nd subform. But data entry is messy and not intuitive.

Any ideas / comments welcome. Thank you.

Paul.