Hi,

I have the following Forms:

[Form A] - Main form - Single form type
[Form B] - Continuous form
[Form C] - Continuous form
[Form D] - Continuous form
[Form E] - Continuous form

I want to add [Form B], [Form C], [Form D], [Form E] as sub-form on the mainform [Form A].

The Forms have the following relationship based on the underlying table:

[Form A] 1:M [Form B] 1:M [Form C] M:M [Form D] M:1 [Form E]

I would also like that the forms are linked with each other ie, if I select a record on one of the parent Form, the corresponding child records should come up in the sub-forms.

I have tried doing this by placing:
[Form E] in [Form D]
[Form D] in [Form C]
[Form C] in [Form B]
[Form B] in [Form A]

However, I face two problems with this approach:

1. The continous form change to single form. I get this error - "A form with a subform object cannot have its DefaultView set to Continuous forms. You tried to add a subform to a form in design view. MSAcess will reset the property to Single Form."

2. The final product (Form A with all the sub-form) is not very eye pleasing. All the sub-forms are nested within each other and looks very complicated.

I am attaching the screen shots.
- The second screen shot shows my final product.
- The first screen shot shows the way I would like to see the Form and the Subform.

Could you please advise re my problems?

put first your continuous form to a new form. each subform to each new form.
add these new form to the main form. and set child/ parent link

Hi sg2808

Just to clarify, you have 1 main form (Form A) and 4 subforms (Forms B - E)

Are the sub forms ALL subforms to Form A (or could be)?

If so you could add a tab control on your form A, with 4 separate tabs and add one subform to each tab on the tab control.

hth

I agree with pere... I have a main form with about 8 subforms each on a tab and it is working out beautiful. I can't imagine any other way to do it.

Thanks Pere and Hassantb1.

@Hassantb1- Are your subforms linked with each other (as parent child). I can create tabs for each form but I do not know how to link the forms with each other (as Master - Child). Is it possible for you to let me know how to create the parent child relation? It will be very helpful.

Thanks in advance.

HI pere... form B is subform to A, C to B, D to C, E to D. Does this help?

The OP is not talking about a Main Form with four Subforms, which could be easily done using the aforementioned Tabbed Controls,he's talking about a Main Form and four 'nested' or cascading, if you will, Subforms.

As you've found, Access will not let you add a Subform to a Form and then save the Form as a Continuous View Form.

The workaround to this requires that you put the Subform either in the Header or the Footer of the Parent Form. To do this

Create your Main/Parent Form in Single Form View
Place the Subform in the Header or the Footer section, whichever you like
Save the Form
Now go into Properties and change the Form's Default View to Continuous

Having said that, I cannot imagine this, or any other configuration, that would both meet these requirements and be aesthetically pleasing! Especially with the [Form C] and [Form D] having a Many-to-Many relationship! This kind of thing is simply going to be way too busy no matter how you present it, if you have to have it all within one Main Form. And to be honest, in almost 20 years of developing databases, I've never seen a scenario such as this!

Perhaps if you can tell us, in plain English, your business requirements we can come up with some alternatives. One possibility that comes to mind is having Popup Forms in place of some of the Subforms, but that depends, as I said, on your business requirements.

Linq ;0)>

Hi sg2808

Yes, you can link separate subforms on different tabs.

The way to do this is as follows:

1. Create a hidden text box control, on the top level (master) form, as the Master ID for your (second) subform
Name it (say) MstrID1
2. On the first subform's on current event add code to add the record ID to the hidden control
Me.Parent.MstrID1 = NZ(Me.FieldName,0)
3. In the control for the second subform enter the Link Master Fields / Link Child Fields so that the link is between control MstrID1 and the appropriate field on the sub form.

Repeat this for each sub form
NB. I've only used this technique for a single cascaded subform (Main Form : Sub Form : Sub Sub Form) and the Sub and sub sub form were on the same tab of a tab control.
If they are on different tabs you will need to enure that selection on the 'master' sub form is clear when viewing the 'slave' sub sub form. Also where you have a multi cascaded scenario such as you describe, it may be worth hiding the 'lower' level tabs if an appopriate selection has not been made on the 'higher' level tabs.

hth

This post has been edited by pere_de_chipstick: Apr 1 2012, 04:51 AM

Since we're on the subject, it should be noted that Access has limits on the number of 'nested' forms you can have:

ACC2000 3 levels
ACC2002 7 levels
ACC2003 7 levels

Can't find the specs on this for 2007/2010.

Linq ;0)>

Thanks all of you for sharing your thoughts. I am attaching the relationship diagram for your reference.

What I need is I would like to create a form so that user can see choose the Jurisdiction, RulesMap, RuleDetail and then could see the linked Process, Risks, and Controls.

Could you please advise a better way to design the form?


Thanks,
SG

Hi Linq,

I tried what you suggested. I attached the form on the Footer. However, only the main form is being shown as Continuous form (multiple records in tabular form) and the sub-form in the footer appears as single form (only one record).

Is this the normal behaviour of the form?

Thanks,
SG

Hi SG

From your relationships diagram, it is difficult to see your actual business requirement. My initial impression is that you are trying to show too much data at any one time - this leads to an overcomplex GUI which is difficult for a user to understand and / or use. Hence my initial post asking for clarification of your sub form structure,

As a rule of thumb, keep your forms simple and only display information that is needed at any one time. You can then (as suggested by linq) open further forms or pop up forms to display additional details only when required by the user.

hth

PS:

Have you set the sub form property default view to continuous forms?


This post has been edited by pere_de_chipstick: Apr 2 2012, 08:38 AM

Thanks pere..

I changed the default view to cont and I was able to see a cont table.

In terms of the requirement, I am trying to generate a form which will show ....which Rule affects the process and associated risk and control. This will give a "single end to end view" to the user. but this looks complicated and not very user friendly.
I have another form which is primarily based on each table.

PS: Aircraft enthusiast? If yes, then you have a company here :-). And have a very similary shot I took at Fairford Airshow.

Hi sg2808

Glad I could help.

If you are only trying to display the single end to end view of the rule(s) that affect the process and associated risk and control, then could you consider using a single control that simply shows the rule(s) for the selected process? This can be based on a query or a VBA function but then does not require the flexibility of multiple nested forms/subforms.

If the resultant detail is too much for a single control but does not need to be constantly 'on view' could a pop up form or even a previewed report suffice?.

You would still need to provide a mechanism for setting up the rules for each procedure, but this could be via a form purely designed for the addition and design of the procedures.

hth

PS.
I am quite interested in aircraft (and did once take some flying lessons!) but photography is the real interest. The photo of the Red Arrows was taken at the Dawlish (South Devon) Carnival display in August 2007.