Nested Subforms in 97

[Tom via AccessMonster.com]

Hi,
My company uses Access 97. I need to make a database that uses three Many to
One relationships on four tables. I tried to make a form that had a main form
with a subform, with a nested subform, and a nested subform, and a nested
subform. Main form, Subform 1, Subform 2, Subform 3. The problem is that 97
only allows 3 levels of nesting and will not let me add the last subform
(subform 3) I have read other posts, and it seems that many other people
don't use subforms, or at least very few. Is there a work around to my
problem. I need to have these forms update just like subforms.

Thanks
Tom

 

[Guest]

You might try using queries for the nesting and place layer four beside
number two/three.

 

[Tom via AccessMonster.com]

Thanks,
Im not sure Im aware of how to use queries for this. If I used a query to
query the records of the table used to populate the last subform, will I be
able to update it as the record in the parent form changes. I know that there
are ways to not use subforms for making my forms. Is this what most people do,
use queries to get the records on the forms. Sorry, I know there is a better
way, I just have yet to know what it is.

Tom

You might try using queries for the nesting and place layer four beside
number two/three.

Hi,
My company uses Access 97. I need to make a database that uses three Many to

[quoted text clipped - 8 lines]

Thanks
Tom

 

[Van T. Dinh]

Actually, I avoid using more than *one* level of subforms. Most users don't
know the 1-to-M relationships so nested Subforms are rally confusing for
them. In addition, nested Subforms will have multiple levels of Auto-Save
enforced and this wikk complicate thing up.

An alternative is to use Subforms on the *same* level and use code to
synchronise them.

 

[Joan Wild]

Instead of nesting the subforms, make them adjacent to each other; I've done
this with '97 and had 4 levels work.

It was not my preferred approach, however it was for only one user of the
database, and they very clearly understood how to use it. It can be
confusing for users to deal with that many levels of subforms.

Anyway you can make the 3 subforms continuous (or datasheet) style; and
place them all on the main form.

Add a textbox named txtLink1 to the main form. Set its control source to
=[Subform1].[Form]![PrimaryID]
Set the Link Master/Child properties to link Subform1 to the Main Form

Set the Link Master/Child properties for Subform2 to
Master = txtLink
Child = whatever field matches/links to Subform1

Repeat this for each subform.

 

[Tom via AccessMonster.com]

Thanks for the help,
One to Many relationships is about all I use. So using nested subforms seemed
like the only way to go. I try not to use code if I can help it (My visual
basic skills are poor at best). If nobody uses subforms, what do they use? Do
they have several separate forms that they use one at a time? What is the
other way to enter the data into several tables?

Tom

Instead of nesting the subforms, make them adjacent to each other; I've done
this with '97 and had 4 levels work.

It was not my preferred approach, however it was for only one user of the
database, and they very clearly understood how to use it. It can be
confusing for users to deal with that many levels of subforms.

Anyway you can make the 3 subforms continuous (or datasheet) style; and
place them all on the main form.

Add a textbox named txtLink1 to the main form. Set its control source to
=[Subform1].[Form]![PrimaryID]
Set the Link Master/Child properties to link Subform1 to the Main Form

Set the Link Master/Child properties for Subform2 to
Master = txtLink
Child = whatever field matches/links to Subform1

Repeat this for each subform.

Hi,
My company uses Access 97. I need to make a database that uses three

[quoted text clipped - 9 lines]

Thanks
Tom

 

[Joan Wild]

They may have separate forms, with a command button to open the child form.

Or they may use a multi-table query as a recordsource for a form.

The method I outlined below requires no coding whatsoever.


--
Joan Wild
Microsoft Access MVP

Thanks for the help,
One to Many relationships is about all I use. So using nested
subforms seemed like the only way to go. I try not to use code if I
can help it (My visual basic skills are poor at best). If nobody uses
subforms, what do they use? Do they have several separate forms that
they use one at a time? What is the other way to enter the data into
several tables?

Tom

Instead of nesting the subforms, make them adjacent to each other;
I've done this with '97 and had 4 levels work.

It was not my preferred approach, however it was for only one user
of the database, and they very clearly understood how to use it. It
can be confusing for users to deal with that many levels of subforms.

Anyway you can make the 3 subforms continuous (or datasheet) style;
and place them all on the main form.

Add a textbox named txtLink1 to the main form. Set its control
source to =[Subform1].[Form]![PrimaryID]
Set the Link Master/Child properties to link Subform1 to the Main
Form

Set the Link Master/Child properties for Subform2 to
Master = txtLink
Child = whatever field matches/links to Subform1

Repeat this for each subform.

Hi,
My company uses Access 97. I need to make a database that uses three

[quoted text clipped - 9 lines]

Thanks
Tom

 

[tomanddani via AccessMonster.com]

Hi,
If I were to use a multi-table query as the recordsource for a form, and I
included fields from two tables, would the primary key fields update to
reflect the new records in both tables? Im thinking that they would but I
never make my forms this way. My forms allways reflect one table, and the
subform reflects another. If I made a form to include every field from every
table would it work? Would it update the keys?

Tom

They may have separate forms, with a command button to open the child form.

Or they may use a multi-table query as a recordsource for a form.

The method I outlined below requires no coding whatsoever.

Thanks for the help,
One to Many relationships is about all I use. So using nested

[quoted text clipped - 35 lines]

 

[Joan Wild]

Why don't you try it and see? Yes it will work. There are circumstances
that will make the query non-updateable. If you look in Help for 'updatable
query', you'll get the details on when it isn't updateable and how to fix
it.

--
Joan Wild
Microsoft Access MVP

Hi,
If I were to use a multi-table query as the recordsource for a form,
and I included fields from two tables, would the primary key fields
update to reflect the new records in both tables? Im thinking that
they would but I never make my forms this way. My forms allways
reflect one table, and the subform reflects another. If I made a form
to include every field from every table would it work? Would it
update the keys?

Tom

They may have separate forms, with a command button to open the
child form.

Or they may use a multi-table query as a recordsource for a form.

The method I outlined below requires no coding whatsoever.

Thanks for the help,
One to Many relationships is about all I use. So using nested

[quoted text clipped - 35 lines]

 

[Tom via AccessMonster.com]

Thanks,

Tom

Why don't you try it and see? Yes it will work. There are circumstances
that will make the query non-updateable. If you look in Help for 'updatable
query', you'll get the details on when it isn't updateable and how to fix
it.

Hi,
If I were to use a multi-table query as the recordsource for a form,

[quoted text clipped - 17 lines]

One to Many relationships is about all I use. So using nested
[quoted text clipped - 35 lines]