How to show three tiers on a form

[David Portwood]

I have Main form ->>Subform1->>Subform2. The primary key in each table is an
autonumber ID field which is the foreign key in the child (the usual 1-M
relation). What is the best way to show these forms on one form?

I was thinking I'd like to display fields from the Main form in single form
manner and maybe display the subforms as datasheets. I tried to do this but
failed in several attempts.

Of course, if you have a better idea for how to display the forms I'd like
to hear it.

 

[Scott McDaniel]

I have Main form ->>Subform1->>Subform2. The primary key in each table is an
autonumber ID field which is the foreign key in the child (the usual 1-M
relation). What is the best way to show these forms on one form?

I was thinking I'd like to display fields from the Main form in single form
manner and maybe display the subforms as datasheets. I tried to do this but
failed in several attempts.

This would be the typical manner of showing a Parent record with many Child records. What do you mean by "failed"? Were
you able to build the forms, or did you have trouble with that?

Assuming you got the forms built, did you set the Master/Child links correctly? This can cause some trouble, since
Access will try to "help" with this, and sometimes gets those values wrong. Open the parent form in design view, select
the Subform control and review the settings for Master/Child links ...

Is Subform2 embedded in Subform1, or are both subforms embedded on the Main form?

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

 

[David Portwood]

I was able to build the forms.

I want both subforms are embedded on the Main form. However, when I tried to
do this (A2000) Acess wanted me to link Subform2 to the Main form rather
than to Subform1.

I tried first embedding Subform2 on Subform1 with then intention of dropping
Subform1 (with embedded Subform2) onto Main. However, when embedding
Subform2, Access required me to change Subform1 to single form rather than
continuous. I wanted Subform1 and Subform2 both tabular.

 

[GeoffG]

David:

The solution is not to have two nested subforms - because you can't do what
you want by making SubForm2 a subform of SubForm1 and then making SubForm1 a
subform of the Main Form.

Instead, you need to make SubForm1 a nested subform of the Main Form and you
need to synchronise SubForm2 to SubForm1.

Design SubForms 1 and 2 so they will be in datasheet view. As usual, in form
design view, drag SubForm1 to the Main Form so it becomes a nested subform
of the Main Form. Drag SubForm2 to the Main Form but don't make it a nested
subform - ie don't link master and child fields (yet).

On the main form, you need a (usually) hidden unbound Textbox. SubForm1's
current event will fill in the Textbox with the primary key of SubForm1's
recordsource - using code like the following (you will need to enter this
code when designing SubForm1):

Private Sub Form_Current()
On Error Resume Next
Me.Parent("txtKey") = [Name of PrimaryKey field of SubForm1's
Recordsource]
End Sub

In the property sheet for the subform control holding SubForm2, the
LinkMasterFields' property needs to be the "txtKey" Textbox and the
LinkChildFields' property needs to be the name of the field in SubForm2's
recordsource that's acting as the foreign key to SubForm1's recordsource.

Regards
Geoff

 

[John W. Vinson]

I have Main form ->>Subform1->>Subform2. The primary key in each table is an
autonumber ID field which is the foreign key in the child (the usual 1-M
relation). What is the best way to show these forms on one form?

I was thinking I'd like to display fields from the Main form in single form
manner and maybe display the subforms as datasheets. I tried to do this but
failed in several attempts.

This can be done, with a little bit of difficulty. I prefer to use Continuous
forms for subforms rather than datasheets, but that's just personal taste;
either will work.

The trick is to use a nonstandard Master Link Field property for the
sub-subform: it needs to be something like

[Forms]![mainform]![firstsubform].Form![keyfieldname]

You do need the full syntax, in my experience.

It may or may not be necessary to Requery the second subform in the first
subform's Current and/or AfterUpdate events - it's been a while and I don't
recall, so try it without and add the requeries if the second subform doesn't
follow navigation events on the first.

John W. Vinson [MVP]

 

[David Portwood]

As Geoff mentioned, I embedded subform2 into subform1 and then embedded
subform1 into the main form. It works, but I don't like the look and I don't
think it will be as easy for the user.

Thanks very much, guys. I'll try your suggestions.