locked
Form Creation with Subform of Subform? RRS feed

  • Question

  • Hello,

    I have not created a form with this scenario so I need a little assistance...

    tblProj has a field "CNo", tblWO has two fields "WOCNo" and "WONo" and tblAData has a field "WOOrder".  CNo is the master with WOCNo being a child record.  WOOrder is then linked to WOCNo.

    My primary form (frmProjFrmQry) is based on a query (qryProjFrm) for tblProj.  tblWO (sfrmWO) and tblAData (sfrmALbr and sfrmAMES) are on the primary form.  How do I relate a subform to two subforms?

       


    Thanks PU_RJF

    Wednesday, April 29, 2020 5:42 PM

Answers

  • It is possible to use a fully qualified reference to a control in a subform as the LinkMasterFields property of another subform control to correlate the subforms.  Another approach is to reference a hidden unbound control in the parent form as the LinkMasterFields property.  However, in both cases performance is poor, and a far more efficient method is to base the correlated subform on a query which references a control in the subform to which it is correlated as a parameter.  The syntax for the parameter is like this example:

        [Forms]![frmCompanies]![frmsubOrders]![OrderID]

    This example is taken from my CorrelatedSubs demo which you'll find in my public databases folder at:

        https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    This little demo file illustrates both correlated and nested subforms, using Northwind data as its example.  In the Current event procedure of the frmsubOrders subform the correlated frmsubOrderDetails subform is requeried with the following line of code:

        Me.Parent!frmsubOrderDetails.Requery

    Note that frmsubOrders and frmsubOrderDetails are the names of the subform controls in the parent form.  These might or might not be the same as the names of their source form objects.


    Ken Sheridan, Stafford, England

    • Marked as answer by PU_RJF Thursday, May 7, 2020 11:54 AM
    Wednesday, April 29, 2020 11:07 PM
  • I understand the first item listed above other than the reference to a "foreign key".  Can you define the "foreign key"?
    I perhaps confused you by an error in my original post.  I said:

    '………….and the LinkMasterFields property to the name of the corresponding foreign key in the subform's recordset.'

    I should have said:

    '………….and the LinkChildFields property to the name of the corresponding foreign key in the subform's recordset.'

    A subform is usually a representation of a referencing table in a one-to-many relationship type with the referenced table on which the parent form is based.  A one-to-many relationship type is achieved by having a column in the referencing table which references the primary key column of the referenced table.  A common example would be a CustomerID column in an Orders table which references the CustomerID primary key column of a Customers table.  The CustomerID column in the Orders table is a foreign key.

    Where two subforms are correlated in a single parent form, the second subform is again a representation of a referencing table in a one-to-many relationship type with a referenced table, but in this case the first subform represents the referenced table, i.e. the relationship from the parent form to the first subform, and then to the second form is a tree-structured hierarchy.

    If you are unfamiliar with how relationship types between tables are built, and how they are represented in the user interface, you might like to take a look at DatabaseBasics.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    This little demo file illustrates, amongst other things, the different relationship types used in relational databases, along with examples of how these are represented by forms/subforms (including correlated subforms) in the user interface.


    Ken Sheridan, Stafford, England



    • Edited by Ken Sheridan Friday, May 1, 2020 11:18 PM Hyperlink inserted
    • Marked as answer by PU_RJF Thursday, May 7, 2020 11:54 AM
    Friday, May 1, 2020 11:15 PM

    1. tblProj -> frmProjbyQry has field “ID” as the Key Index.
    2. tblWO -> sfrmWO has fields “WOID” indexed (there can/will be duplicates) and “WONo” with no duplicates.  There can/will be multiple WONo’s per ID (Key Index) in No 1 above.  When records are initially entered, there may be no WONo information.
    3. tblAData -> sfrmALbr (and others) has field WOOrder indexed (there can/will be duplicates)

    So, in tblWO is WOID a foreign key referencing the primary key of tblProj?  In tblAData is WOOrder a foreign key referencing the primary key of tblWO?

    If so, sfrmWO can be linked to the parent form in the conventional way by setting the LinkMasterFields and LinkChildFields properties of the subform control to ID and WOID respectively.

    To correlate sfrmALbr  with sfrmWO  the former's RecordSoiurce property should e a query which references the primary key of the latter's recordset as a parameter:

        Forms!frmProjbyQry!sfrmWO!WONo

    This assumes that sfrmWO is the name of the subform control as well as of its source form object, in which case your query looks OK, in this regard at least.

    In the Current event procedure of sfrmWO requery sfrmALbr with:

        Forms!frmProjbyQry!sfrmALbr.Requery

    This assumes that sfrmALbr is the name of the subform control as well as of its source form object.


    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Monday, May 4, 2020 10:45 PM Typo corrected.
    • Marked as answer by PU_RJF Thursday, May 7, 2020 11:54 AM
    Monday, May 4, 2020 10:39 PM
  • Private Sub Form_Current()

       
        Forms!frmProjQry!sfrmALbr.Requery
        Forms!frmProjQry!sfrmAMES.Requery
       
    End Sub

    In each case the RecordSource property of the subreport will need to be a query which incudes references to a control as a parameter, which restricts the result set of the query to the relevant rows.  Is this the case?

    Ken Sheridan, Stafford, England

    • Marked as answer by PU_RJF Thursday, May 7, 2020 11:54 AM
    Tuesday, May 5, 2020 10:06 PM
  • Both queries reference a control in sfrWONo as a parameter, so both subforms should be requeried in sfrWONo's Current event procedure.

    Ken Sheridan, Stafford, England

    • Marked as answer by PU_RJF Thursday, May 7, 2020 11:53 AM
    Wednesday, May 6, 2020 4:44 PM

All replies

  • It is possible to use a fully qualified reference to a control in a subform as the LinkMasterFields property of another subform control to correlate the subforms.  Another approach is to reference a hidden unbound control in the parent form as the LinkMasterFields property.  However, in both cases performance is poor, and a far more efficient method is to base the correlated subform on a query which references a control in the subform to which it is correlated as a parameter.  The syntax for the parameter is like this example:

        [Forms]![frmCompanies]![frmsubOrders]![OrderID]

    This example is taken from my CorrelatedSubs demo which you'll find in my public databases folder at:

        https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    This little demo file illustrates both correlated and nested subforms, using Northwind data as its example.  In the Current event procedure of the frmsubOrders subform the correlated frmsubOrderDetails subform is requeried with the following line of code:

        Me.Parent!frmsubOrderDetails.Requery

    Note that frmsubOrders and frmsubOrderDetails are the names of the subform controls in the parent form.  These might or might not be the same as the names of their source form objects.


    Ken Sheridan, Stafford, England

    • Marked as answer by PU_RJF Thursday, May 7, 2020 11:54 AM
    Wednesday, April 29, 2020 11:07 PM
  • Ken,

    Thanks for the reply and information.  I did look at the information and am continuing to have an issue.  As before, my main form is "frmProjFrmQry".  My first subform "sfrmWO" is collecting the correct data; however, the second subform "sfrmALbr" is not.

    frmProjFrmQry has an autonumber index named "ID" (no duplicates)

    sfrmWO has a field "WOID" which allows duplicates and is a child field related to “ID”

    sfmWO has another field "WONo"

    sfrmALbr has a field "WOOrder"

    This seems very similar to your example yet I am still unable to collect the sfrmALbr information.  Any further suggestions?


    Thanks PU_RJF

    Friday, May 1, 2020 6:05 PM
  • The basis for correlating two subforms in a parent form is essentially quite simple:

    1.  The first subform's recordset is restricted to those rows which reference the current row in the parent form.  This is done by the conventional method of setting the LinkMasterFields property of the first subform to the name of the primary key of the parent form's recordset, and the LinkMasterFields property to the name of the corresponding foreign key in the subform's recordset.

    2.  The second subform's recordset is restricted to those rows which reference the current row in the first subform.   This is done by means of a parameter in the second subform's RecordSource query which references a control in the first subform bound to its recordset's primary key.  The control can be hidden if necessary.  The parameter is on the foreign key column of the second subform's RecordSource query which references the primary key of the first subform's recordset.

    3.  In the Current event procedure of the first subform the second subform's Requery method is called.  This reloads the second subform's recordset, restricting it by the parameter to the relevant rows.

    If you apply those simple requirements to your subforms they should return the current data as the user navigates (a) to a row in the parent form, and (b) to a row in the first subform,.

    Ken Sheridan, Stafford, England

    Friday, May 1, 2020 8:05 PM
  • Ken,

    Thanks for the response and sorry for my confusion.  I understand the first item listed above other than the reference to a "foreign key".  Can you define the "foreign key"?


    Thanks PU_RJF

    Friday, May 1, 2020 8:19 PM
  • I understand the first item listed above other than the reference to a "foreign key".  Can you define the "foreign key"?
    I perhaps confused you by an error in my original post.  I said:

    '………….and the LinkMasterFields property to the name of the corresponding foreign key in the subform's recordset.'

    I should have said:

    '………….and the LinkChildFields property to the name of the corresponding foreign key in the subform's recordset.'

    A subform is usually a representation of a referencing table in a one-to-many relationship type with the referenced table on which the parent form is based.  A one-to-many relationship type is achieved by having a column in the referencing table which references the primary key column of the referenced table.  A common example would be a CustomerID column in an Orders table which references the CustomerID primary key column of a Customers table.  The CustomerID column in the Orders table is a foreign key.

    Where two subforms are correlated in a single parent form, the second subform is again a representation of a referencing table in a one-to-many relationship type with a referenced table, but in this case the first subform represents the referenced table, i.e. the relationship from the parent form to the first subform, and then to the second form is a tree-structured hierarchy.

    If you are unfamiliar with how relationship types between tables are built, and how they are represented in the user interface, you might like to take a look at DatabaseBasics.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    This little demo file illustrates, amongst other things, the different relationship types used in relational databases, along with examples of how these are represented by forms/subforms (including correlated subforms) in the user interface.


    Ken Sheridan, Stafford, England



    • Edited by Ken Sheridan Friday, May 1, 2020 11:18 PM Hyperlink inserted
    • Marked as answer by PU_RJF Thursday, May 7, 2020 11:54 AM
    Friday, May 1, 2020 11:15 PM
  • Hello Ken,

    Thanks again for the responses.  I’m still struggling with this; however, I do understand your follow-up response regarding the Master and Child fields.  Just needed clarification on my part.

    I’m not sure if this will help.  I believe I am missing something simple…

    1. tblProj -> frmProjbyQry has field “ID” as the Key Index.
    2. tblWO -> sfrmWO has fields “WOID” indexed (there can/will be duplicates) and “WONo” with no duplicates.  There can/will be multiple WONo’s per ID (Key Index) in No 1 above.  When records are initially entered, there may be no WONo information.
    3. tblAData -> sfrmALbr (and others) has field WOOrder indexed (there can/will be duplicates)

    The main form “frmProjbyQry” and 1<sup>st</sup> subform “sfrmWO” continue to work fine.  The 2<sup>nd</sup> subform “sfrmALbr” only identifies records when sfrmWO is empty.  It then appears to list ALL records from the previous Key Index.  Following are screen shots from what I have...


    Thanks PU_RJF

    Monday, May 4, 2020 2:21 PM

    1. tblProj -> frmProjbyQry has field “ID” as the Key Index.
    2. tblWO -> sfrmWO has fields “WOID” indexed (there can/will be duplicates) and “WONo” with no duplicates.  There can/will be multiple WONo’s per ID (Key Index) in No 1 above.  When records are initially entered, there may be no WONo information.
    3. tblAData -> sfrmALbr (and others) has field WOOrder indexed (there can/will be duplicates)

    So, in tblWO is WOID a foreign key referencing the primary key of tblProj?  In tblAData is WOOrder a foreign key referencing the primary key of tblWO?

    If so, sfrmWO can be linked to the parent form in the conventional way by setting the LinkMasterFields and LinkChildFields properties of the subform control to ID and WOID respectively.

    To correlate sfrmALbr  with sfrmWO  the former's RecordSoiurce property should e a query which references the primary key of the latter's recordset as a parameter:

        Forms!frmProjbyQry!sfrmWO!WONo

    This assumes that sfrmWO is the name of the subform control as well as of its source form object, in which case your query looks OK, in this regard at least.

    In the Current event procedure of sfrmWO requery sfrmALbr with:

        Forms!frmProjbyQry!sfrmALbr.Requery

    This assumes that sfrmALbr is the name of the subform control as well as of its source form object.


    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Monday, May 4, 2020 10:45 PM Typo corrected.
    • Marked as answer by PU_RJF Thursday, May 7, 2020 11:54 AM
    Monday, May 4, 2020 10:39 PM
  • Ken,

    Got It!  Thanks again!

    My issue was with the Forms!frmProjbyQry!sfrmALbr.Requery.  I had identified the incorrect second subform.

    A final question, I assume I can use the same method for an additional correlated subform?  sfrmALbr is Actual Labor for a sfrmWO.  I also have sfrmAMES which is Actual Materials for the same sfrmWO.  Can I add an additional Requery (Form!frmProjbyQry!sfrmAMES.Requery)?


    Thanks PU_RJF

    Tuesday, May 5, 2020 12:42 PM
  • There is no reason why you cannot requery multiple subforms.  When you call the Requery method the control being referenced must have the correct value of course, so if the control in question is in another subform which is being requeried, that must be requeried first so that the second subform's query is referencing the correct value.

    Ken Sheridan, Stafford, England

    Tuesday, May 5, 2020 5:45 PM
  • I may need a different method for this...

    From our previous thread, I am using Forms!frmProjbyQry!sfrmALbr.Requery for Labor based on the smfrmWO.  Labor and Material information is maintained the same table but different subforms.  Using Forms!frmProjbyQry!sfrmAMES.Requery as below did not work.

    Private Sub Form_Current()
       
        Forms!frmProjQry!sfrmALbr.Requery
        Forms!frmProjQry!sfrmAMES.Requery
       
    End Sub


    Thanks PU_RJF

    Tuesday, May 5, 2020 7:36 PM
  • Private Sub Form_Current()

       
        Forms!frmProjQry!sfrmALbr.Requery
        Forms!frmProjQry!sfrmAMES.Requery
       
    End Sub

    In each case the RecordSource property of the subreport will need to be a query which incudes references to a control as a parameter, which restricts the result set of the query to the relevant rows.  Is this the case?

    Ken Sheridan, Stafford, England

    • Marked as answer by PU_RJF Thursday, May 7, 2020 11:54 AM
    Tuesday, May 5, 2020 10:06 PM
  • Sorry...previous response had non-relevant formatting "stuff"

    Following us current relationships and RecordSource information:

    Subform sfrmWO Key Field is WONo

    For subform sfrmALbr foreign field WOOrder.  RecordSource is as follows...

    SELECT tblAData.WOOrder, tblAData.[Cost Element], tblAData.[Cost Element Name], tblAData.[Posting Date], tblAData.TotalQty, tblAData.[Valin RepCur] FROM tblAData WHERE (((tblAData.WOOrder)=Forms!frmProjQry!sfrmWO!WONo) And ((tblAData.[Cost Element])>800000 And (tblAData.[Cost Element])<890000)) ORDER BY tblAData.[Posting Date];

    For subform sfrmAMES foreign field WOOrder.  RecordSource is as follows...

    SELECT tblAData.WOOrder, tblAData.[Cost Element], tblAData.[Cost Element Name], tblAData.[Posting Date], tblAData.TotalQty, tblAData.[Posted unit of meas], tblAData.[Valin RepCur], tblAData.[Purchase Order Text] FROM tblAData WHERE (((tblAData.WOOrder)=Forms!frmProjQry!sfrmWO!WONo) And ((tblAData.[Cost Element])<800000));


    Thanks PU_RJF

    Wednesday, May 6, 2020 11:53 AM
  • Both queries reference a control in sfrWONo as a parameter, so both subforms should be requeried in sfrWONo's Current event procedure.

    Ken Sheridan, Stafford, England

    • Marked as answer by PU_RJF Thursday, May 7, 2020 11:53 AM
    Wednesday, May 6, 2020 4:44 PM
  • Seems to be working. Thanks for all your help!

    Thanks PU_RJF

    Thursday, May 7, 2020 11:53 AM