2 Subforms (parent-child) on a Main Form

Kenln

Registered User.
Local time
Today, 05:25
Joined
Oct 11, 2006
Messages
551
I have a main form with two sub forms.
Main Form - Single Form
Subform 1 - Datasheet
Subform 2 - Datasheet

I have the Main Form parent to Subform 1 child.

Subform 2 is also on the Main Form.

Can I, and how, make subform 2 a child of subform 1?

I appreciate your help,

Thanks,

Ken
 
You can by making subform1 a continuous form and putting subform2 in the Form Footer section of subform 1. But do you really want to do that?

There might be a better way to achieve your goal. Why do you want to nest it that way?
 
I have done the other way before.

On this form it is much easier if both subforms are datasheet.
 
What have you done the other way?

Brings us back to my question, what is the idea behind nesting it that way? So you can have it filtered perhaps?
 
I have, in other applications, a Main form with subform1 (as a single form) and subform2 (as a datasheet). Having subform2 as a subform on subform1. It works, no problem.

In this application I have a main form with fields that a user completes.

On subform1 (datasheet) which is links by a Main_ID. the user adds a number of records based on a single field with a drop down.

Each record on subform1 can in turn also have a number of records (two fields).

i.e. Table1_ID (and Main Form) is pk to Table2; Table2_ID (subform1) is pk to Table3 (subform2).

It displays better with two datasheets.
 
I have, in other applications, a Main form with subform1 (as a single form) and subform2 (as a datasheet). Having subform2 as a subform on subform1. It works, no problem.
Fantastic! Don't you think you should use the ideas from your other applications then?

i.e. Table1_ID (and Main Form) is pk to Table2; Table2_ID (subform1) is pk to Table3 (subform2).

It displays better with two datasheets.
You can have subform 1 and subform 2 as subforms of the Main Form and still have subform 2 linked to subform 1. Lookup the Filter and FilterOn properties of a form.
 
Is Filter and FilterOn the best way?

I have used those before but not as a Parent - Child.

I can't link (Master-Child) the two subforms?
 
It would do what you want it to do.

You can even go upwards (Child-Parent).

You can't have a Master-Child if one is not the subform of the other. If you follow what I've advised you can have a Master-Child between the Main form and the two subforms exclusively.
 
If all other methods are failed, then try this to get as many child forms on a single main form:

  1. create an unbound textbox control (hidden) on your main form
  2. name it txtLink
  3. on your second child form: do this:
Code:
Private Sub Form_Current()
On Error GoTo Err
        If Not IsNull(Me![yourRecID]) Then
            Forms![Main_Form]![txtLink] = Me![yourRecID]
        End If

Exit_Err:
Exit Sub
Err:
        MsgBox Err.Description
        Resume Exit_Err
End Sub

This will give you the current ID of your second child form on runtime:
  • you can now refere 'txtLink' field/control in your second child form as:
    • LinkMasterFields: Team_ID;txtlink;dtelink
    • LinkChildFields: F_Team_ID;F_Task_ID;F_Date_ID
Note:
Change the feildnames according to your database field-names.

Hope methode will solve your problem.

Khalid
 
Hi Kenln

I recently created a db that has multiple Subforms & as you described, I had a 'between' subforms that I used to control the sub-subform(s) for numbered questions.

That way I could use this 'Mid' form to change the sub-sub forms data ie; display new sub-sub form fields for a new question number.

Unfortunately, any form containing a subform can't keep the datasheet format itself as far as I'm aware.

Although, you could have Subform 1 containing 2 more subforms that will update according to Subform 1s MAIN_ID & both be in Datasheet format as they don't contain subforms themselves.

If this is what you're after, open the design view of Subform 1 & get the tools to draw an area for a Subform as you did originally for Subform 1.

You'll get a prompt to select a form to add, select subform 2 & the wizard will take you through the prompts for Parent/Child links as you had when you did your original Sub forms.

Check your MAIN_ID autonumber as you add records as it should also update in both subforms.

HTH
 
If all other methods are failed, then try this to get as many child forms on a single main form:

  1. create an unbound textbox control (hidden) on your main form
  2. name it txtLink
  3. on your second child form: do this:
Code:
Private Sub Form_Current()
On Error GoTo Err
        If Not IsNull(Me![yourRecID]) Then
            Forms![Main_Form]![txtLink] = Me![yourRecID]
        End If

Exit_Err:
Exit Sub
Err:
        MsgBox Err.Description
        Resume Exit_Err
End Sub
This will give you the current ID of your second child form on runtime:
  • you can now refere 'txtLink' field/control in your second child form as:
    • LinkMasterFields: Team_ID;txtlink;dtelink
    • LinkChildFields: F_Team_ID;F_Task_ID;F_Date_ID
Note:
Change the feildnames according to your database field-names.

Hope methode will solve your problem.

Khalid
Good input but I doubt if the method advised would fail Khalid. Also, the Parent should be filtering the subform/child. That is, the On Current event should be handled by the Parent form and not the subform. Why the need of a hidden textbox? Redundant.

Hi Kenln

I recently created a db that has multiple Subforms & as you described, I had a 'between' subforms that I used to control the sub-subform(s) for numbered questions.

That way I could use this 'Mid' form to change the sub-sub forms data ie; display new sub-sub form fields for a new question number.

Unfortunately, any form containing a subform can't keep the datasheet format itself as far as I'm aware.

Although, you could have Subform 1 containing 2 more subforms that will update according to Subform 1s MAIN_ID & both be in Datasheet format as they don't contain subforms themselves.

If this is what you're after, open the design view of Subform 1 & get the tools to draw an area for a Subform as you did originally for Subform 1.

You'll get a prompt to select a form to add, select subform 2 & the wizard will take you through the prompts for Parent/Child links as you had when you did your original Sub forms.

Check your MAIN_ID autonumber as you add records as it should also update in both subforms.

HTH
The OP should avoid having too many subforms. He/she will encounter problems referencing and also performance will be affected. It was already advised that he/she can still have 2 subforms in datasheet view and ways to synch.
 
I'm not sure about the filter and filteron method. If there were no matching records in the subform it should be completely blank as all forms with no records available. This would not work since I could not add records to subform2.

I tried the method of created a text box to link to. Looks good so far. I will need to add a couple of lines to requery the subform and clear when a new recorded is being added to subform1.

I'll let you know if I have any problems,

But it looks like it should work fine.

Thanks Khalid
 
I'm not sure about the filter and filteron method. If there were no matching records in the subform it should be completely blank as all forms with no records available. This would not work since I could not add records to subform2.
Tried and tested method. You just need to know how to apply it.

Glad you found a solution that works for you.
 
Good input but I doubt if the method advised would fail Khalid. Also, the Parent should be filtering the subform/child. That is, the On Current event should be handled by the Parent form and not the subform. Why the need of a hidden textbox? Redundant.

The OP should avoid having too many subforms. He/she will encounter problems referencing and also performance will be affected. It was already advised that he/she can still have 2 subforms in datasheet view and ways to synch.

This method I have tested myself and prefer to use on such cases when i need many child forms on a single main form; it works fine :)

The purpose of the hidden textbox control is to suppress the recordID from user to show on the main form.

moreover it is not necessary that On Current should be handled by the Parent form. sub form can also handle the on current method, here we need it on subform; and want to get the current record id on the main form 'txtLink' control textbox. Next we will use this value in our Child subform on the main form as I said:

you can now refere 'txtLink' field/control in your second child form as:
LinkMasterFields: Team_ID;txtlink;dtelink
LinkChildFields: F_Team_ID;F_Task_ID;F_Date_ID

Hope this make sense.

Khalid
 
I tried the method of created a text box to link to. Looks good so far. I will need to add a couple of lines to requery the subform and clear when a new recorded is being added to subform1.

I'll let you know if I have any problems,

But it looks like it should work fine.

Thanks Khalid

Your are welcome.
It works fine for me and I have tested it. just try and I hope that it would work.

Glad to listen if your problem would solved:

Khalid
 
Acutlly as it turns out the User has identified another table (subform) that needs to be added. i.e. MainForm linked to subform1 linked to subform2 linked to subform3.

All subforms NEED to be datasheet have have the ability to add/edit/delete their own records (as well as cascade).

So far it looks good in testing.

I did, however, add a couple of lines, but I'm not sure if I need them.

Code:
    If Not IsNull(Me![Main_ID]) Then
        [COLOR="Red"]Me.Parent.sfrm_2.Visible = True[/COLOR]
        Forms![frm_Main]![txt_sfrm_1_Link] = Me![Main_ID]
        [COLOR="red"]Me.Parent.sfrm_2.Requery[/COLOR]
    [COLOR="red"]Else
        Me.Parent.sfrm_2.Visible = False[/COLOR]
    End If

This requeries subform2 on every Form_Current event.

Also it makes subform2 not visible if a new record is selected. I did this because it is possible (however rare) that a user could try to enter data into subform2 before a record was entered/added into subform1. The data won't take but this way is cleaner.

Your thoughts?


Now I repeat the proces for subforms 2 and 3.

Looks good in tests.

Thanks,
 
Code:
    If Not IsNull(Me![Main_ID]) Then
        [COLOR="Red"]Me.Parent.sfrm_2.Visible = True[/COLOR]
        Forms![frm_Main]![txt_sfrm_1_Link] = Me![Main_ID]
        [COLOR="red"]Me.Parent.sfrm_2.Requery[/COLOR]
    [COLOR="red"]Else
        Me.Parent.sfrm_2.Visible = False[/COLOR]
    End If

This requeries subform2 on every Form_Current event.

Also it makes subform2 not visible if a new record is selected. I did this because it is possible (however rare) that a user could try to enter data into subform2 before a record was entered/added into subform1. The data won't take but this way is cleaner.

Your thoughts?

Good idea to to hide the empty form, but re-querying the sub-form on each on current event is not a good idea, this will make your network and data process slow if many users will use your application on same time. every time your form will go for re-query the data and your application will be very slow and might hang.
 

Users who are viewing this thread

Back
Top Bottom