How do i go about doing a subform within a subform?

[xwnoob]

I want to do a form that has a main-form,sub-form and a subsub-form.

Im using 3 tables: BorrowerCase,IndividualBorrower,Contact Party

BorrowerCase has a one to many relationship with individualborrower and individualborrower has one to many relationship with contactparty.

Each of these tables has a auto-number ID. So how do i do it? I Read that microsoft access 2000 cant have subform within subform if you have a continous form but i really need to implement my form this way as i think its the most logical solution so any ideas??

 

[Galaxiom]

Put all the subforms on the main form and link them via a textbox on the main form.

The ControlSource of the textbox is set to a field in one subform and the LinkMasterFields of the other subformcontrol set to the textbox.

 

[xwnoob]

Sorry but i do not fully understand what you are trying to explain. Can you use my tables as an example? I know i must establish relationships but im not sure whats linkmasterfields.

I want the borrower subform to refer to the main form and i want the sub-subform to refer to the borrower subform and it would help if you could explain in detail how to link the sub-subform to the subform.

I have read about subforms so i think i am able to do it for only a a form that has only 1 subform, but not for this case.

 

[Galaxiom]

The table Relationships don't matter. This is all done on the form.

LinkMasterFields and LinkChildFields are properties of the subformcontrol. This particular setup can't be done by the Wizard so you have to enter them manually. If you are using the Wizard then bypass the step where it asks you about the relationship between the records in the form and subform.

Place a subform on the main form for each of the two levels you are trying to link. Then place a textbox on the main form and set the Control Source to:

=subformcontrolname.Form.keyfieldname

The textbox should then track the current record in the subform.

Then in the properties of the second subformcontrol enter the textbox name in the LinkMasterFields property and the keyfieldname of its subform in the LinkChildFields. This subform then tracks the textbox which is tracking the other subform.

 

[xwnoob]

"Then place a textbox on the main form and set the Control Source to:

=subformcontrolname.Form.keyfieldname

The textbox should then track the current record in the subform."


Does it matter if this textbox tracks my auto-number primary key ID?? because im not sure if this is the same as relationships whereby two fields should have same data type. This textbox also should not have any value other than the control source of the subform right?

ill try this method but i hope that you can answer 1 more question that i have:

Lets say for example i enter all required information into the main form.. then i go to the borrower's subform and enter 3 borrowers. Then, in the sub-subform, i want to enter contact party individually for each of the 3 borrowers by selecting their name in the subform. Is there any code that is needed ? If so, can you give a brief explanation of how i can do this .

 

[Galaxiom]

The subform will work just as a normal subform. New Records will have the value from the LinkMasterField automatically entered in the LinkChildField.

So the new records in the linked subform will be automatically connected to the current record in the first subform via its primary key.

Remember, both subforms are on the main form. The second one is not inside the first subform.

 

[xwnoob]

ok but will it matter if my primary key is an auto number linking to the textbox???

 

[Galaxiom]

PK can be autonumber in the first subform. The textbox just reflects that value in the current record of the first subform.

This value becomes a foreign key in the second subform. It can have an autonumber PK too if required but that doesn't matter to the LinkFields.

 

[gemma-the-husky]

what G is saying is that although you can't have a continuous form, with a continuous sub-form, you can simulate this, by using the current item in the main form, to set some OTHER field, that then can be used with the second continuous form.

I am not sure about the exact method G suggests, as I do this in a slightly different way. I put both forms into a separate container form, and then use a tiny bit of code to manage the link.

 

[smig]

you can't put a sub_Form into a continous form (You can put it in the Top/Foot headers only)

So if you want your forms structure to look like this you can't:

XXXXXXX0
YYYYYYYYY0
YYYYYYYYY1
YYYYYYYYY2
ZZZZZZZZZZZ0
ZZZZZZZZZZZ1
ZZZZZZZZZZZ2
YYYYYYYYY3
ZZZZZZZZZZZ0
XXXXXXX1
YYYYYYYYY0
ZZZZZZZZZZZ0
YYYYYYYYY1
ZZZZZZZZZZZ0
ZZZZZZZZZZZ1

 

[Galaxiom]

I am not sure about the exact method G suggests, as I do this in a slightly different way. I put both forms into a separate container form, and then use a tiny bit of code to manage the link.

This is almost the same as what I do but using the LinkFields avoids the need for code in the OnCurrent Event to apply a filter or similar to the second subform.

The textbox on the container form can be made not Visible of you don't want to see it but I generally leave it showing above the second subform as kind of a title for the current record.

I also use the click event of the textbox to refocus on the first subform so that it is easy to get back to the current record even if it has been scrolled out of view. Adding a ConditionalFormat to highlight the focussed control makes the current record obvious.

BTW It is actually possible for the LinkMasterFields to refer directly to a field or control in the first subform (subformcontrol1.Form.fieldname, subformcontrol1.Form.Controlname or just subformcontrol1!fieldname) but this is not reliable. It will work if the first subform loads first but this can change when the form is edited so it is more relaible to put a textbox on the main form (or a container form).

 

[ctags009]

While we are on the subject of using multiple subforms on forms, is there a "best practice" as far as how many to use and how they are used as far as overall functionality is concerned? I mean, it seems like for any basic "Employee Info" database, one would have many subforms all related to an EmployeeID - I read somewhere that it is best to make separate forms where possible, but never heard anything concrete about what to do and what not to do? What are your thoughts on using a frame to hide/unhide overlapping subforms??

 

[Galaxiom]

Generally if there are too many subforms to fit on one screen they would be placed on separate tabs of the main form.